I need the ability to return a total for 2005 and 2006 by fiscal week.
So for november i need 4 weeks (44, 45, 46, 47) of totals for both years.
I can run a query and get either or but i would like to get both totals in the same rows for display purposes.
Here is the query im using for 2005
SUM(a.IDI) as Total2005
FROM web05.web04_intranet.dbo.puffsnapshot a
Left Join WH_Date_Dim b ON a.snapdate = b.datekey
Where a.ReportType = 'Bookings'
and DatePart(year, a.snapdate) = 2005
and b.FiscalPeriod = '11'
and DatePart(month, a.snapdate) = '11'
Group By b.WeekNumber, b.MonthName
I need to see if there is a way to just add another column that will pull the same info for 2006.
Modifing the above query and chaning 2005 to 2006 will pull the correct data for 2006.
This way when i display the data i can have
Month Name | FiscalWeek | 2005 Total | 2006 Total