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);
1条答案
按热度按时间63lcw9qa1#
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.
This returns a row per day for the preceding week of each summ, with the daily average for each weekday:
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.