SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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)

Sales Report on a View

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!

Challenges detected:

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! 

Total article views: 1403 | Views in the last 30 days: 2
Related Articles

Last Sunday

Needed to return the week ending date as Sunday for daily transactions multiple times, for several a...


Monday of the current week

Need to default reporting services parameter to Monday of the current week (ongoing)


Monday Night Networking at Summit 2017

Next week is the PASS Summit in Seattle. Thousands of SQL Server professionals are going, many comin...


DateName Week Change Start from Sunday to Monday

I have the following: datename(ww,fld_posting_date) , fld_posting_date it produces: 10 2008-03-...


Mondays and Sundays of the month

I am creating a cycle sql that give me the range of second dates to Sunday of each month, for that I...