将每周总计转换为每日平均值

Question

我有一个这样的预测表：

Item      ForecastDate     ForecastQty
A123      7/30/17          140
A123      8/6/17           70
A123      8/13/17          70
A123      8/20/17          70
A123      8/27/17          70
A123      9/3/17           45

我需要提出所有天(包括周末(的每日平均值，所以我的输出看起来像这样：

Item      ForecastDate     DailyFcstQty
A123      7/30/17          20
A123      7/31/17          20
A123      8/1/17           20
A123      8/2/17           20
A123      8/3/17           20
A123      8/4/17           20
A123      8/5/17           20
A123      8/6/17           20
A123      8/7/17           10
A123      8/8/17           10

等等。

如何将每周数据(如果重要的话，始终是星期日(转换为跨天的每日平均值？

Answer 1

您需要一个日期表，这可以使用 CTE 来完成。然后，完全加入以枚举您的日期......并做除法。由于您声明数据中的日期始终是星期日，因此 7 的除数是静态的，dateadd(day,6,t.ForecastDate)也是如此。

declare @table table (Item varchar(4), ForecastDate date, ForecastQty int)
insert into @table
values
('A123','7/30/17',140),
('A123','8/6/17',70),
('A123','8/13/17',70),
('A123','8/20/17',70),
('A123','8/27/17',70),
('A123','9/3/17',45)
declare @minDate date = (select min(ForecastDate) from @table)
declare @maxDate date = (select max(ForecastDate) from @table)
;with GetDates As  
(  
select @minDate as TheDate              --startdate
from @table
UNION ALL  
select DATEADD(day,1, TheDate) from GetDates  
where TheDate <= @maxDate   --maxdate
)
select distinct * 
into #tempDates 
from GetDates 
option(maxrecursion 0)
select * from #tempDates order by TheDate
select 
t.Item
,d.TheDate
,DailyFcstQty = t.ForecastQty / 7
from @table t
full outer join
#tempDates d on 
d.TheDate >= t.ForecastDate
and d.TheDate <= dateadd(day,6,t.ForecastDate)
order by
t.ForecastDate

drop table #tempDates 

返回

+------+------------+--------------+
| Item |  TheDate   | DailyFcstQty |
+------+------------+--------------+
| A123 | 2017-07-30 |           20 |
| A123 | 2017-07-31 |           20 |
| A123 | 2017-08-01 |           20 |
| A123 | 2017-08-02 |           20 |
| A123 | 2017-08-03 |           20 |
| A123 | 2017-08-04 |           20 |
| A123 | 2017-08-05 |           20 |
| A123 | 2017-08-06 |           10 |
| A123 | 2017-08-07 |           10 |
| A123 | 2017-08-08 |           10 |
| A123 | 2017-08-09 |           10 |
| A123 | 2017-08-10 |           10 |
| A123 | 2017-08-11 |           10 |
| A123 | 2017-08-12 |           10 |
| A123 | 2017-08-13 |           10 |
| A123 | 2017-08-14 |           10 |
| A123 | 2017-08-15 |           10 |
| A123 | 2017-08-16 |           10 |
| A123 | 2017-08-17 |           10 |
| A123 | 2017-08-18 |           10 |
| A123 | 2017-08-19 |           10 |
| A123 | 2017-08-20 |           10 |
| A123 | 2017-08-21 |           10 |
| A123 | 2017-08-22 |           10 |
| A123 | 2017-08-23 |           10 |
| A123 | 2017-08-24 |           10 |
| A123 | 2017-08-25 |           10 |
| A123 | 2017-08-26 |           10 |
| A123 | 2017-08-27 |           10 |
| A123 | 2017-08-28 |           10 |
| A123 | 2017-08-29 |           10 |
| A123 | 2017-08-30 |           10 |
| A123 | 2017-08-31 |           10 |
| A123 | 2017-09-01 |           10 |
| A123 | 2017-09-02 |           10 |
| A123 | 2017-09-03 |            6 |
| A123 | 2017-09-04 |            6 |
+------+------------+--------------+