Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Sergiu Ungureanu,
A friend of mine asked me to help him on a project he had at work. Basically he was asked to create a simple report such as: GID, Week4,Week3,Week2,Week1,Turnover based on a table that stores the sales data something like this: GID, SaleDate, ItemQty, ItemPrice. Going back to the report:
- GID- should be unique- consider it a product id if you want;
- Week1- sum of the sales that have been made 3 weeks ago (meaning starting from Monday until Sunday)
- Week2- same as Week4 but for the week following Week4-> 2 weeks ago basically
- Week3-same but 1 week ago Monday to Sunday
- Week4- sum of all the sales made by this Monday until right now (or Sunday considering the possibility of having sales recorded in the future)
- Turnover- it's the avg of the week columns, but only for those whose value <>0. eg:
Turnover=(2000+800+900)/3 (not 4!) = 1233
The application my friend tries to configure can be used to design reports, but these reports can use as data sources ONLY views or tables. Thus the challenge! :D ...Challenge Accepted!
1. Each week should be filtered from Monday to Sunday as per the current date the report is ran at. Eg: If the report is ran today (20130906) this is how the sales amount should be filtered based on the sale date:
Week1 2013-08-12 2013-08-18
Week2 2013-08-19 2013-08-25
Week3 2013-08-26 2013-09-01
Week4 2013-09-02 2013-09-08
2. Once I'll have the data, I will have it as per the source table layout -> vertically. I will need it horizontally (also known as pivoted).
3. Doing all this in a single view that should run on top of millions of records.
After one hour of struggle here is the view.
(see the sql script)
Hope it can be of any use to anyone else. It can be easily modified for more or less weeks.
Thanks and hope this will not be my first and last script I share with you guys!
Needed to return the week ending date as Sunday for daily transactions multiple times, for several a...
Need to default reporting services parameter to Monday of the current week (ongoing)
I have the following:
datename(ww,fld_posting_date) , fld_posting_date
I am creating a cycle sql that give me the range of second dates to Sunday of each month, for that I...
After searching a lot in google i think i have to create my login in sql forum to get SQL Serve...