September 25, 2015 at 8:56 pm
hi
I create a report base on categories and sales of goods.
Now I have Daily Info abaout all Products.
But I Need to present this report base on weekly periods. in pivotal format.
I family with pivotal format but change between daily report and weekly report is abigious for me.
Thank you
September 25, 2015 at 10:07 pm
MotivateMan1394 (9/25/2015)
hiI create a report base on categories and sales of goods.
Now I have Daily Info abaout all Products.
But I Need to present this report base on weekly periods. in pivotal format.
I family with pivotal format but change between daily report and weekly report is abigious for me.
Thank you
Quick suggestion, add a YearWeek column (YYYYWW) and aggregate on that one instead of the date.
😎
SELECT (YEAR(GETDATE()) * 100) + DATEPART(WEEK,GETDATE()) AS YYYYWW
September 25, 2015 at 10:13 pm
Probably the easiest way to do this is to have an auxiliary Calendar table that you use to make this really easy. There are several articles here with examples of how to create a calendar table, so I'll just link to one.
In your calendar table, you would have a date field (unique) representing all the dates in the date range you need. Then your WeekOfYear column would be populated with something like this:
DATEPART(week,GETDATE());
which returns the weeknumber of the given date.
Assuming you have this handy date table, your query becomes something like:
SELECT cal.TheYear
,cal.WeekNumber
,SUM(sales.[Quantity]) AS WeeklySales
FROM Calendar cal LEFT JOIN Sales ON cal.Datekey = Sales.SaleDate
GROUP BY cal.TheYear
,cal.TheWeek
Then if the boss changes his mind and suddenly wants a grouping by quarter or fiscal year or fiscal quarter etc, the job is really simple. Just change the GROUP BY and you're finished.
Oh, the Calendar table articles I promised... http://www.sqlservercentral.com/articles/T-SQL/70482/
If you find just about any Data Warehouse book, you'll see a calendar table / dimension with all kinds of different columns for grouping. Pick one that works for you and away you go.
Eirikur -- I guess you're right... you could do it that way, but I like the Calendar table for one simple reason - crazy easy to use. Do the join, group by whatever you want, and you're off to the races. AND you can outer join to it...
September 26, 2015 at 12:06 am
Thank you
Thank you
Excellent !!!!
😛
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply