How to split sales from weekly data to daily without taking weekends into account? (SSMS)-CodePudding

I have this dataset:

create table ds
(
dt date,
summ int
);
insert into ds values ('26.02.2013', 312)
insert into ds values ('05.03.2013', 833)
insert into ds values ('12.03.2013', 225)
insert into ds values ('19.03.2013', 453)
insert into ds values ('26.03.2013', 774)
insert into ds values ('02.04.2013', 719)
insert into ds values ('09.04.2013', 136)
insert into ds values ('16.04.2013', 133)
insert into ds values ('23.04.2013', 157)
insert into ds values ('30.04.2013', 850)
insert into ds values ('07.05.2013', 940)
insert into ds values ('14.05.2013', 933)
insert into ds values ('21.05.2013', 422)
insert into ds values ('28.05.2013', 952)
insert into ds values ('04.06.2013', 136)
insert into ds values ('11.06.2013', 701)
;

As the title suggests I need to split sales from weekly data to daily. The thing is that there are no sales on weekends, so the number of daily sales on Saturday and Sunday is 0. In that case the simple (week number/7) doesn't actually work. It must be (week number/5), but I don't know how to exclude weeknds from my code. For example, 05.03.2013 - 2 days of this particular week refers to February and 3 of them to March. Then I need to aggregate all sales from all days that refers to a specific month into one number.

The final table should include 2 columns - 1) the sum for each month and 2) month number (from 2 to 6 in this particular example).

Here is my little template where you can see a wanted result (obviously the calculations for each month are not right):

drop table if exists #subtable
declare @first_date date = (select top 1 dt from ds)                                                 
declare @mindate date = (select DATEADD(day, -6, @first_date))
declare @maxdate date = (select max(dt) from ds)

;with cte as 
    (
    select @mindate as firstdate
    from ds
    union all 
    select dateadd(day, 1, firstdate) from cte
    where firstdate < @maxdate and firstdate >= @mindate
    )
select distinct *
into #subtable
from cte 
option(maxrecursion 0)
select month(firstdate) as MonthNumber, round(sum(dailysale), 2) as overall_sales
from
    (
    select 
        d.firstdate, dailysale = ds.summ / 7
    from ds
    full join 
    #subtable d on d.firstdate <= ds.dt and d.firstdate >= DATEADD(day, -6, ds.dt)
    ) as newttt
group by month(firstdate);

CodePudding user response：

I am posting this attempt in t-sql to clarify your question. Your sample data is very small, so it's difficult to ascertain how the final aggregation should be handled. Please run this and help us edit for you.

This query assumes there is one day per week in your source table (are they always Tuesdays?) and you want to average that summ over the 5 weekdays.

--SET DATEFORMAT dmy;

declare @ds table
(
dt date,
summ int
);
insert into @ds values ('26.02.2013', 312)
insert into @ds values ('05.03.2013', 833)
insert into @ds values ('12.03.2013', 225)
insert into @ds values ('19.03.2013', 453)
insert into @ds values ('26.03.2013', 774)
insert into @ds values ('02.04.2013', 719)
insert into @ds values ('09.04.2013', 136)
insert into @ds values ('16.04.2013', 133)
insert into @ds values ('23.04.2013', 157)
insert into @ds values ('30.04.2013', 850)
insert into @ds values ('07.05.2013', 940)
insert into @ds values ('14.05.2013', 933)
insert into @ds values ('21.05.2013', 422)
insert into @ds values ('28.05.2013', 952)
insert into @ds values ('04.06.2013', 136)
insert into @ds values ('11.06.2013', 701);

    
select  *, 
        datename(dw, s.d) as [Weekday], 
        case when datepart(dw, s.d) not in (1,7) then summ/5. else 0 end as [DailySumm] 
from @ds 
cross
apply (select dateadd(day, -n.n, dt)
        from (values(0),(1),(2),(3),(4),(5),(6))n(n)
)s(d);

This returns a row per day for the preceding week of each summ, with the daily average for each weekday:

...
2013-02-26 312         2013-02-20 Wednesday                      62.400000
2013-03-05 833         2013-03-05 Tuesday                        166.600000
2013-03-05 833         2013-03-04 Monday                         166.600000
2013-03-05 833         2013-03-03 Sunday                         0.000000
2013-03-05 833         2013-03-02 Saturday                       0.000000
2013-03-05 833         2013-03-01 Friday                         166.600000
2013-03-05 833         2013-02-28 Thursday                       166.600000
2013-03-05 833         2013-02-27 Wednesday                      166.600000
2013-03-12 225         2013-03-12 Tuesday                        45.000000
...

Hopefully this helps you describe exactly how you want the weekly/monthly aggregation to be handled. With this dataset, grouping by week or month should be straightforward.