Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved 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:

GID

1

2

3

4

Turnover

1

2000

0

800

900

1233

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: 1184 | Views in the last 30 days: 41
 
Related Articles
SCRIPT

Last Sunday

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

FORUM

Monday of the current week

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

FORUM

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-...

FORUM

Total Number of Sundays,Mondays.. in a month

Hi, After searching a lot in google i think i have to create my login in sql forum to get SQL Serve...

BLOG

Motivational Monday - 8/30/10

Goor Morning folks !  It's time for another motivational Monday. Today I want to remind you to be c...

Tags
cte    
pivot    
recursion    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones