Getting monthly totals when there is no activity 1

[nugget5]

I have a view (vMonthlyBalSheet) that totals user activity by month

SELECT UserID, DATEPART(yy, ActivityDate) AS ActivityDateYear, DATENAME(m, ActivityDate) AS ActivityDateMonth,
COUNT(*) AS DaysWorked, SUM(Offage) AS NetCashVar, SUM(UnitsSold) AS TotalSold
FROM vBalanceSheet
GROUP BY UserID, DATEPART(yy, ActivityDate), DATENAME(m, ActivityDate)

However, this view will not return rows for months where a user has no activity. I need a way to return rows for months where there was no activity.
I have tried taking this view and joining it with a table that contains records for all months and years.

What I need is 12 rows for each user in the database (the last 12 months) whether they had activity or not.

 

[monksnake]

A simple way to go about this is to create a table that just lists all the days in a year and join it with your query above and group by the month and year of the calendar table.

<.

 

[nugget5]

Can you elaborate on this?
I need rows that will have the userid not null that shows the zero activity. Right now, I get rows with null userid.

 

[monksnake]

Phew, I didn't realize you needed userid not to be null either, ok, this one was a doozie for me, I'll try to translate what I did into your code, and if anyone has any better ways, please feel free to add.

First, I have a table called CalendarTable that contains one column, dte, I populate this column with every day from the beginning of the year to the end. (365 rows this year)

SELECT BB.UserID, BB.ActivityDateYear, BB.ActivityDateMonth, 
COUNT(AA.Offage) AS DaysWorked, SUM(AA.Offage) AS NetCashVar, SUM(AA.UnitsSold) AS TotalSold 
FROM vBalanceSheet AA RIGHT JOIN 
   (SELECT DATEPART(YY, A.ActivityDate) AS ActivityDateYear, 
    DATEPART(MM, A.ActivityDate) AS ActivityDateMonth, B.UserID 
       FROM vBalanceSheet B INNER JOIN 
          CalendarTable A ON YEAR(A.ActivityDate) = YEAR(B.ActivityDate) 
             GROUP BY DATEPART(YY, A.ActivityDate), DATEPART(MM, A.ActivityDate), B.UserID) BB 
   ON AA.UserID = BB.UserID AND 
   BB.ActivityDateMonth = DATEPART(MM, AA.ActivityDate) AND
   BB.ActivityDateYear = DATEPART(YY, AA.ActivityDate) GROUP BY BB.ActivityDateYear, BB.ActivityDateMonth, BB.UserID ORDER BY BB.UserID, BB.ActivityDateYear, BB.ActivityDateMonth

I know, this is some sloppy stuff, but it worked for me.

<.

 

[AlexCuse]

Couldn't you just populate a table variable with 1-12 and join from that to datepart(month, ActivityDate) ?

I haven't played with this idea yet, but I will have a closer look in the morning. I think it would be easier.

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

Couldn't you just populate a table variable with 1-12 and join from that to datepart(month, ActivityDate) ?

That will work if the table vBalanceSheet only contains data for one year, otherwise you have to include year with the month numbers, so in that case it just seems easier to use dates.

"There's an old saying in Tennessee — I know it's in Texas, probably in Tennessee — that says, fool me once, shame on — shame on you. Fool me — you can't get fooled again." - George W. Bush

<.

 

[AlexCuse]

Yeah I saw that when I took a look this morning. I suppose a table variable with year and month might be slightly more efficient, although also slightly more effort to set up. Whether it is worth it or not probably depends on how popular a query this is going to be.

Ignorance of certain subjects is a great part of wisdom