I need some expert advice please.
I have a table with Employees and eCodes in SQL server. I want to get the count of eCodes for each employee but in some cases an employee doesn't have a certain eCode.
Where the eCode is missing for an employee I want to return zero.
SELECT Employee, eCode, Count(*) cnt FROM Test
Group By Employee, eCode
Order by Employee, eCode
The above SQL gives me following output
David is missing eCode X and Y, which Gary and Tony has. How can I return zero for its count.
Similarly Gary and Tony is missing F and G and I want to get zero for those two.
I can use some help here.
Best Answer
This should accomplish what you're looking for but this assumes all of your
eCodes
exist at least for one record in yourTest
table. Otherwise you can replace theTest
table instance on the right side of theCROSS JOIN
in theCTE
below with your source table ofeCodes
: