Tuesday, 22 October 2013

Displaying comparative sales information

Requirement : Display Sales numbers day wise. Next to the day's sale, display the amount sold on the same day of last week. Also, display the average of last 4 same day of week sales.

For instance, if we are seeing the sales numbers for Sep 27th (which is a friday), the report should display the number for Sep 20th. Also, the average of the numbers for Sep 20, Sep 13, Sep 6 and Aug 30.


this is one way:


select t1.CAFE_ID,t1.SALES_DATE,

sum(t1.GROSS_SALE) GROSS_SALE,
lag(sum(t1.GROSS_SALE),7,0) over (order by t1.CAFE_ID,t1.SALES_DATE) as GROSS_SALE_LAST_WEEK,
floor(
((lag(sum(t1.GROSS_SALE),7,0) over (order by t1.CAFE_ID,t1.SALES_DATE) +
lag(sum(t1.GROSS_SALE),14,0) over (order by t1.CAFE_ID,t1.SALES_DATE) +
lag(sum(t1.GROSS_SALE),21,0) over (order by t1.CAFE_ID,t1.SALES_DATE) +
lag(sum(t1.GROSS_SALE),28,0) over (order by t1.CAFE_ID,t1.SALES_DATE))/4)) GROSS_SALE_LAST_4_WEEK_AVG
from DS_DAILY_SALES t1
group by t1.CAFE_ID,t1.SALES_DATE;

This uses the LAG function to lookback a week ago.


However, the days where a store was closed, there is no record for the Cafe for that day. So the logic of looking back 7 records in the window, doesn't always work. Even a single day off will make the report wrong.


Alternate:


select v1.CAFE_ID,v1.SALES_DATE,v1.GROSS_SALE, 

coalesce(v2.GROSS_SALE ,0) GROSS_SALE_LAST_WEEK,
floor((coalesce(v2.GROSS_SALE ,0)+coalesce(v3.GROSS_SALE ,0)+coalesce(v4.GROSS_SALE ,0)+coalesce(v5.GROSS_SALE ,0))/4) GROSS_SALE_LAST_4_WEEK_AVG
from
(select CAFE_ID,SALES_DATE,sum(GROSS_SALE) GROSS_SALE
from DS_DAILY_SALES
group by CAFE_ID,SALES_DATE) v1 left outer join (
select CAFE_ID,SALES_DATE,sum(GROSS_SALE) GROSS_SALE
from DS_DAILY_SALES
group by CAFE_ID,SALES_DATE) v2 on (v1.CAFE_ID = v2.CAFE_ID and v1.SALES_DATE = DATEADD(DAY,7,v2.SALES_DATE))
left outer join (
select CAFE_ID,SALES_DATE,sum(GROSS_SALE) GROSS_SALE
from DS_DAILY_SALES
group by CAFE_ID,SALES_DATE) v3 on (v1.CAFE_ID = v3.CAFE_ID and v1.SALES_DATE = DATEADD(DAY,14,v3.SALES_DATE))
left outer join (
select CAFE_ID,SALES_DATE,sum(GROSS_SALE) GROSS_SALE
from DS_DAILY_SALES
group by CAFE_ID,SALES_DATE) v4 on (v1.CAFE_ID = v4.CAFE_ID and v1.SALES_DATE = DATEADD(DAY,21,v4.SALES_DATE))
left outer join (
select CAFE_ID,SALES_DATE,sum(GROSS_SALE) GROSS_SALE
from DS_DAILY_SALES
group by CAFE_ID,SALES_DATE) v5 on (v1.CAFE_ID = v5.CAFE_ID and v1.SALES_DATE = DATEADD(DAY,28,v5.SALES_DATE))

This does a left outer join. As this is an outer join, missing records don't make the report wrong.

No comments:

Post a Comment