将周六改为周五，然后改为AVG

Question

我有一个表，它大约有1.65亿行数据——正在创建平均每日使用量，但仅针对工作周。我需要从周六开始进行库存交易，并在周五进行计数，然后将周日改为周一。

CREATE TABLE #temptable ( [ITEMID] nvarchar(20), [Daily Usage] decimal(38,10), [CalendarDate] date )
INSERT INTO #temptable
VALUES
( N'A24519-01', 0.0000000000, N'2019-02-18T00:00:00' ), 
( N'A24519-01', 7.0000000000, N'2019-02-19T00:00:00' ), 
( N'A24519-01', 10.0000000000, N'2019-02-20T00:00:00' ), 
( N'A24519-01', 4.0000000000, N'2019-02-21T00:00:00' ), 
( N'A24519-01', 11.0000000000, N'2019-02-22T00:00:00' ), 
( N'A24519-01', 0.0000000000, N'2019-02-23T00:00:00' ), 
( N'A24519-01', 0.0000000000, N'2019-02-24T00:00:00' ), 
( N'A24519-01', 9.0000000000, N'2019-02-25T00:00:00' ), 
( N'A24519-01', 5.0000000000, N'2019-02-26T00:00:00' ), 
( N'A24519-01', 8.0000000000, N'2019-02-27T00:00:00' ), 
( N'A24519-01', 17.0000000000, N'2019-02-28T00:00:00' ), 
( N'A24519-01', 0.0000000000, N'2019-03-01T00:00:00' ), 
( N'A24519-01', 1.0000000000, N'2019-03-02T00:00:00' ), 
( N'A24519-01', 0.0000000000, N'2019-03-03T00:00:00' ), 
( N'A24519-01', 1.0000000000, N'2019-03-04T00:00:00' ), 
( N'A24519-01', 12.0000000000, N'2019-03-05T00:00:00' ), 
( N'A24519-01', 4.0000000000, N'2019-03-06T00:00:00' ), 
( N'A24519-01', 14.0000000000, N'2019-03-07T00:00:00' ), 
( N'A24519-01', 0.0000000000, N'2019-03-08T00:00:00' ), 
( N'A24519-01', 0.0000000000, N'2019-03-09T00:00:00' ), 
( N'A24519-01', 0.0000000000, N'2019-03-10T00:00:00' ), 
( N'A24519-01', 4.0000000000, N'2019-03-11T00:00:00' ), 
( N'A24519-01', 9.0000000000, N'2019-03-12T00:00:00' ), 
( N'A24519-01', 6.0000000000, N'2019-03-13T00:00:00' ), 
( N'A24519-01', 0.0000000000, N'2019-03-14T00:00:00' ), 
( N'A24519-01', 14.0000000000, N'2019-03-15T00:00:00' ), 
( N'A24519-01', 1.0000000000, N'2019-03-16T00:00:00' ), 
( N'A24519-01', 0.0000000000, N'2019-03-17T00:00:00' )

所以如果我在上面运行下面的，我会得到4.89

SELECT AVG(1 * [Daily Usage]) 
FROM #temptable 

我试图得到6.85-我不知道如何将数字从周六/周日移动到周五/周一-他们从#临时中删除了周末

Answer 1

如果有人遇到同样的问题，来到这里-下面是我在解决这个问题时最终使用的内容。

--Move Saturday data into Friday and move Sunday data into Monday (Working days for buyers...)
UPDATE
EDU
SET
EDU.[Daily Usage] = EDU.[Daily Usage] + ND.[Daily Usage]
FROM #ExplodedDailyUsage                                    AS EDU
INNER JOIN (SELECT
t.ITEMID
, t.[Daily Usage]
, t.CalendarDate
, DATEPART(dw, t.CalendarDate)                                                                             DOW
, DATEADD(
DAY, CASE DATEPART(WEEKDAY, t.CalendarDate) WHEN 7 THEN -1 WHEN 1 THEN 1 ELSE 0 END, t.CalendarDate) AS NewDate
FROM #ExplodedDailyUsage AS t
WHERE DATEPART(dw, t.CalendarDate) IN ( 7, 1 )) ND ON ND.NewDate = EDU.CalendarDate
AND ND.ITEMID = EDU.ITEMID;
--Delete Saturdays and Sundays
DELETE FROM #ExplodedDailyUsage WHERE DATEPART(dw, CalendarDate) IN ( 7, 1 );