Skip to content
Advertisement

Count and group data by Year/month and by user

I have a dataset that looks like this:

I am using the following query to count the number of tickets closed per month:

I want to show the amount of tickets closed per month BY each user but I cannot get the INSERT INTO-Select statement include a set of rows for each users that outline the count by month. I am guessing i would need some sort of while loop that iterates over a temporary table that includes a list of distinct users to accomplish this. I know loops in MSSQL are not the best to implement. What is the best way to accomplish this?

Advertisement

Answer

You don’t need a loop, derive year and month and then group by them

Also, I don’t think you need them for this, but in case I misunderstood your question and you want start and end dates in this you can back-derive them from year and month as follows

, DATEFROMPARTS (ClosedYear, ClosedMonth, 1) as firstDayOfMonth , DATEADD(month, 1, DATEFROMPARTS (ClosedYear, ClosedMonth, 1)) as firstDayNextMonth

And one more thought, you don’t need to put the results into a temporary table, but if you wanted to the syntax would be as follows

EDIT: In the comment you said you wanted zeros added back in so you can plot this, you can still do that with this structure by adding in a temporary table of USERS x DATES and COALESCE in the zeros over NULLS

EDIT: Here it is with a pivot table as requested in comments

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement