2 Stores, each with its sales data per day. Both get equipped with promotion material but not at the same day. After the pr_day the promotion material will stay there. Meaning, there should be a sales boost from the day of the installation of the promotion material.
Installation Date: Store A - 05/15/2019 Store B - 05/17/2019
To see if the promotion was a success we measure the sales before the pr-date and after by returning number of sales (not revenue but pieces sold) next to the int, indicating how far away it was from the pr-day: (sum of sales from both stores)
pr_date| sales -28 | 35 -27 | 40 -26 | 21 -25 | 36 -24 | 29 -23 | 36 -22 | 43 -21 | 31 -20 | 32 -19 | 21 -18 | 17 -17 | 34 -16 | 34 -15 | 37 -14 | 32 -13 | 29 -12 | 25 -11 | 45 -10 | 43 -9 | 26 -8 | 27 -7 | 33 -6 | 36 -5 | 17 -4 | 34 -3 | 33 -2 | 21 -1 | 28 1 | 16 2 | 6 3 | 16 4 | 29 5 | 32 6 | 30 7 | 30 8 | 30 9 | 17 10 | 12 11 | 35 12 | 30 13 | 15 14 | 28 15 | 14 16 | 16 17 | 13 18 | 27 19 | 22 20 | 34 21 | 33 22 | 22 23 | 13 24 | 35 25 | 28 26 | 19 27 | 17 28 | 29
you may noticed, that i already removed the day from the installation of the promotion material.
The issue starts with the different installation date of the pr-material. If I group by weekday it will combine the sales from different days away from the installation. It will just start at whatever weekday i define:
Select DATEDIFF(wk, change_date, sales_date), sum(sales)
from tbl_sales
group by DATEDIFF(wk, change_date, sales_date)
result:
week | sales
-4 | 75
-3 | 228
-2 | 204
-1 | 235
0 | 149
1 | 173
2 | 151
3 | 167
4 | 141
the numbers are not from the right days and there is one week to many. Guess this is comming from sql grouping the sales starting from Sunday and because the pr_dates are different it generates more than just the 8 weeks (4 before, 4 after)
trying to find a sustainable solution i couldn’t find the right fit and decided to post it here. Very thankfull for every thoughts of the community about this topics. Quite sure there is a smart solution for this problem cause it doesn’t look like a rare request to me
I tried it with over as well but i don’t see how to sum the 7 days together as they are not date days anymore but delta to the pr-date
Desired Result: week | sales -4 | 240 -3 | 206 -2 | 227 -1 | 202 1 | 159 2 | 167 3 | 159 4 | 163
Attachment from my analysis by hand what the Results should be:
Why do i need the weekly summary -> the Stores are performing differently depending on the weekday. With summing 7 days together I make sure we don’t compare mondays to sundays and so on. Furthermore, the result will be represented in a Line- or Barchart where you could see the weekday variation in a ugly way. Meaning it will be hard for your eyes to see the trend/devolopment of the salesnumbers. Whereas the weekly comparison will absorb this variations.
If anything is unclear please feel free to let me know so i could provide you with futher details
Thank you very much
Additional the different Installation date overview:
Shop A: store A delta date sales -28 17.04.2019 20 -27 18.04.2019 20 -26 19.04.2019 13 -25 20.04.2019 25 -24 21.04.2019 16 -23 22.04.2019 20 -22 23.04.2019 26 -21 24.04.2019 15 -20 25.04.2019 20 -19 26.04.2019 13 -18 27.04.2019 13 -17 28.04.2019 20 -16 29.04.2019 21 -15 30.04.2019 20 -14 01.05.2019 17 -13 02.05.2019 13 -12 03.05.2019 9 -11 04.05.2019 34 -10 05.05.2019 28 -9 06.05.2019 19 -8 07.05.2019 14 -7 08.05.2019 23 -6 09.05.2019 18 -5 10.05.2019 9 -4 11.05.2019 22 -3 12.05.2019 17 -2 13.05.2019 14 -1 14.05.2019 19 0 15.05.2019 11 1 16.05.2019 0 2 17.05.2019 0 3 18.05.2019 1 4 19.05.2019 19 5 20.05.2019 18 6 21.05.2019 14 7 22.05.2019 11 8 23.05.2019 12 9 24.05.2019 8 10 25.05.2019 7 11 26.05.2019 19 12 27.05.2019 15 13 28.05.2019 15 14 29.05.2019 11 15 30.05.2019 5 16 31.05.2019 8 17 01.06.2019 10 18 02.06.2019 19 19 03.06.2019 14 20 04.06.2019 21 21 05.06.2019 22 22 06.06.2019 7 23 07.06.2019 6 24 08.06.2019 23 25 09.06.2019 17 26 10.06.2019 9 27 11.06.2019 8 28 12.06.2019 23 Shop B: store B delta date sales -28 19.04.2019 15 -27 20.04.2019 20 -26 21.04.2019 8 -25 22.04.2019 11 -24 23.04.2019 13 -23 24.04.2019 16 -22 25.04.2019 17 -21 26.04.2019 16 -20 27.04.2019 12 -19 28.04.2019 8 -18 29.04.2019 4 -17 30.04.2019 14 -16 01.05.2019 13 -15 02.05.2019 17 -14 03.05.2019 15 -13 04.05.2019 16 -12 05.05.2019 16 -11 06.05.2019 11 -10 07.05.2019 15 -9 08.05.2019 7 -8 09.05.2019 13 -7 10.05.2019 10 -6 11.05.2019 18 -5 12.05.2019 8 -4 13.05.2019 12 -3 14.05.2019 16 -2 15.05.2019 7 -1 16.05.2019 9 0 17.05.2019 9 1 18.05.2019 16 2 19.05.2019 6 3 20.05.2019 15 4 21.05.2019 10 5 22.05.2019 14 6 23.05.2019 16 7 24.05.2019 19 8 25.05.2019 18 9 26.05.2019 9 10 27.05.2019 5 11 28.05.2019 16 12 29.05.2019 15 13 30.05.2019 17 14 31.05.2019 9 15 01.06.2019 8 16 02.06.2019 3 17 03.06.2019 8 18 04.06.2019 8 19 05.06.2019 13 20 06.06.2019 11 21 07.06.2019 15 22 08.06.2019 7 23 09.06.2019 12 24 10.06.2019 11 25 11.06.2019 10 26 12.06.2019 9 27 13.06.2019 6 28 14.06.2019 9
Advertisement
Answer
Try
select wk, sum(sales)
from (
select
isnull(sa.sales,0) + isnull(sb.sales,0) sales
, isnull(sa.delta , sb.delta) delta
, case when isnull(sa.delta , sb.delta) = 0 then 0
else case when isnull(sa.delta , sb.delta) > 0 then (isnull(sa.delta , sb.delta) -1) /7 +1
else (isnull(sa.delta , sb.delta) +1) /7 -1
end
end wk
from shopA sa
full join shopB sb on sa.delta=sb.delta
) t
group by wk;
A more readable version, it doesn’t run faster, just using CROSS APLLY this way allows to indroduce sort of intermediate variables for cleaner code.
select wk, sum(sales)
from (
select
isnull(sa.sales,0) + isnull(sb.sales,0) sales
, dlt delta
, case when dlt = 0 then 0
else case when dlt > 0 then (dlt - 1) / 7 + 1
else (dlt + 1) / 7 - 1
end
end wk
from shopA sa
full join shopB sb on sa.delta=sb.delta
cross apply (
select dlt = isnull(sa.delta, sb.delta)
) tmp
) t
group by wk;
Finally, if you already have a query which produces a dataset with the (pr_date, sales) columns
select wk, sum(sales)
from (
select sales
, case when pr_date = 0 then 0
else case when pr_date > 0 then (pr_date - 1) / 7 + 1
else (pr_date + 1) / 7 - 1
end
end wk
from (
-- ... you query here ...
)pr_date_sales
) t
group by wk;