This has stumped me. Not sure how to get a distinct sum inside a case statement.
Here's a sample of my data in the two fields (I'm actually doing this for a year but just 2 months here for easier reading):
I need a count of each month in a column but each emp needs to count as one, like this:
but instead I get 4 and 4. Here is my code
I've tried putting the word distinct in between Select and sum but that made it a 1 and 1.
Any help would be much appreciated.
Try adding a group by. Without a dataset in front of me I would try a group by month(hist_date)
answered Nov 20 '12 at 10:13 PM
This is a two-step problem that you can solve with ROW_NUMBER.
Here's a sample solution:
The first part of the query is a common table expression which gets the row number for each employee ID based on the month and year in question. This is what you can use to filter out "duplicate" rows in the bottom expression: only get the ones where rownum = 1. The end results are what you are expecting, and it results in one scan of the table.
One thing you might think about doing is flipping the query around, though, especially if you're presenting it in a presentation layer like an SSRS matrix or Excel table. The query as you have it now works, but you would need to update each month (or write a somewhat-nasty dynamic SQL statement to display each month). Instead, you could use the natural solution:
Then, put it in an SSRS matrix and you'll automatically get the month and year as columns. Or if it's going out to Excel, you can do the same in a pivot table.
EDIT - Fixed a bug in the partitioning. Now a person can show up in multiple months.
If you don't need the separate dates in columns, you could write the query as: