October 26, 2005 at 3:07 pm
I would like a query to return a list of the totals for each category by month. I have a table with the list of categories and I have a view with the month/year, categoryid and total. If there were no items in a particular category and month there is currently no record in the view. I want to create a similar view but include a row for every category and month combination between a certain date range, even if there are no records for a particular month/category combination (fill those in with zeroes). Any help would be appreciated.
October 26, 2005 at 4:06 pm
ex:
declare @minDate datetime, @maxDate datetime
select @mindate = '20030101', @maxDate ='20051020'
select Y, M, CategoryName, Coalesce(V.Cnt, 0) TheCount
from
(select Month(dateadd(m, number, @mindate)) as M
, Year(dateadd(m, number, @mindate)) as Y
from master..spt_values
where type = 'P'
and number <= datediff(m, @mindate, @maxDate)
) Dates D
cross join
Categories C
left join The_counts_View v
on D.M = V.M and D.Y =V.Y and C.CategoryName = V.CategoryName
* Noel
October 26, 2005 at 6:14 pm
Thanks Noel!!
Once again you have the right answer to a problem I hadn't encountered before.
Andrew
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply