自定义销售金额计算

Question

我有下表

;WITH cte AS(
SELECT * FROM (VALUES
('23/06/2014', 3,  0,  11203659),
('30/06/2014', 7,  1,  11203659),
('07/07/2014', 6,  0,  11203659),
('14/07/2014', 2,  1,  11203659),
('21/07/2014', 5,  0,  11203659),
('28/07/2014', 21, 1,  11203659),
('04/08/2014', 3,  1,  11203659),
('11/08/2014', 9,  1,  11203659),
('18/08/2014', 7,  0,  11203659),
('25/08/2014', 4,  0,  11203659),
('01/09/2014', 2,  0,  11203659),
('08/09/2014', 4,  0,  11203659),
('15/09/2014', 1,  0,  11203659),
('22/09/2014', 3,  1,  11203659),
('29/09/2014', 6,  1,  11203659),
('06/10/2014', 3,  1,  11203659),
('13/10/2014', 4,  1,  11203659)
) as t([Date], SoldAmt, promo, code_article))

我尝试计算最小日期和最大日期之间的平均总和（SoldAmt）/天数，其中促销 = 1 按文章回滚前 28 天/次

select sum(SoldAmt)/convert(day, Date)
from MyTable 
group by [code article ],Date

Answer 1

从你的问题中获取cte并尝试这个：

SELECT sum(SoldAmt)*1.00/DATEDIFF(day, MIN(convert(date,[Date],104)),MAX(convert(date,[Date],104))) as 'average sum'
FROM cte 
WHERE promo !=1
GROUP BY code_article

WHERE promo !=1从结果开始精确回滚 28 天DATEDIFF。

结果：

average sum
---------------------------------------
0.3809523809523
(1 row(s) affected)