Sales Report on a View
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! 😀 ...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!
if object_id (N'salesinputtable') is not null
drop table salesinputtable
go
create table salesinputtable
(
Ident int identity(1,1) not null primary key,
GID int not null,
SaleDate datetime not null,
ItemQty int not null,
ItemPrice money not null
)
go
--Here we create a test source table
insert into salesinputtable
values
(1,'20130819',2,250),
(1,'20130819',3,500),
(1,'20130830',1,100),
(1,'20130830',10,1000),
(1,'20130907',1,250),
(1,'20130907',5,2500),
(2,'20130907',2,5),
(4,'20130819',2,250),
(3,'20130819',3,500),
(5,'20130830',1,100),
(13,'20130830',10,1000),
(12,'20130907',1,250),
(22,'20130907',5,2500),
(1244,'20130907',2,5),
(1244,'20130819',2,250),
(2,'20130819',3,500),
(4,'20130830',1,100),
(3,'20130830',10,1000),
(5,'20130907',1,250),
(14,'20130907',5,2500),
(13,'20130907',2,5)
go
---Defining the view
if OBJECT_ID('dbo.VW_CTE_SalesComputeReport') is not null
drop view VW_CTE_SalesComputeReport
go
--Here comes the fun :)
create view VW_CTE_SalesComputeReport
as
with
--1. We get the date of this week's Monday
----- it could have been done based on @@datefirst and datepart(dw,x) but it depends a lot on the server's set language
MondayThisWeek(DateOfMonday) as
(select CONVERT(date,
case DATENAME(dw,getdate())
when 'Monday' then GETDATE()
when 'Tuesday' then GETDATE()-1
when 'Wednesday' then GETDATE()-2
when 'Thursday' then GETDATE()-3
when 'Friday' then GETDATE()-4
when 'Saturday' then GETDATE()-5
when 'Sunday' then GETDATE()-6
end)
),
--2. We find the dates of all Mondays and Sundays starting 3 weeks ago up to this week
TimeTable(Monday,Sunday) as
(
select DateOfMonday,DATEADD(dd,6,DateOfMonday) from MondayThisWeek
union all
select dateadd(ww,-1,Monday),DATEADD(dd,6,dateadd(ww,-1,Monday)) from TimeTable
where Monday>=convert(date,dateadd(wk,-3,getdate()))
),
--3. Let's add in the number of the week
TimeTableWithWeek(Monday,Sunday,WeekNbr) as
(
select Monday,Sunday,'Week'+convert(varchar(1),4-DATEPART(wk,getdate())+DATEPART(wk,Monday)) from TimeTable
),
--4. After getting the data we need we also pivot it per weeks numbers
FinalTablePivoting(GID,Week1,Week2,Week3,Week4,Turnover) as
(
select GID,isnull([Week1],0),isnull([Week2],0),isnull([Week3],0),isnull([Week4],0),0 as Turnover
from
(
select sls.gid,tt.WeekNbr as WeekNbr,sum(sls.ItemQty*sls.ItemPrice) as SalesAmount
from salesinputtable sls
cross join TimeTableWithWeek tt
where convert(date,sls.SaleDate) between tt.Monday and tt.Sunday
group by sls.gid,tt.WeekNbr
) t1
PIVOT
(max(SalesAmount) for WeekNbr in ([Week1],[Week2],[Week3],[Week4])) as t2
)
--5. Final select in which we also compute the turnover column
select
GID,
Week1,
Week2,
Week3,
Week4,
case when Week1=0 and week2=0 and week3=0 and week4=0
then 0
else
(Week1+Week2+Week3+Week4)
/
(
(case when Week1=0 then 0 else 1 end)+
(case when Week2=0 then 0 else 1 end)+
(case when Week3=0 then 0 else 1 end)+
(case when Week4=0 then 0 else 1 end)
)
end as Turnover
from FinalTablePivoting
go
--6.Display initial data
select * from salesinputtable;
--7.Display processed data
select * from VW_CTE_SalesComputeReport ; 
