计算SQL SERVER两个日期之间的总和

Question

我有两个表

日历

FirstDate   |  LastDate
------------+-----------    
2020-05-01     2020-05-03
2020-05-02     2020-05-04

ProfileRate

ID |    Date    | Rate | Product
---+------------+------+----------
1    2020-05-01   100    Prod1
2    2020-05-02   200    Prod1
3    2020-05-03   300    Prod1
4    2020-05-04   400    Prod1
5    2020-05-01   150    Prod2
6    2020-05-02   250    Prod2
7    2020-05-03   350    Prod2
8    2020-05-04   450    Prod2

我想计算SUM。像这样的

FirstDate   | LastDate    | Prod1  | Prod2 
------------+-------------+--------+-------
2020-05-01    2020-05-03    600      750
2020-05-02    2020-05-04    900      1050

我尝试过的：

SELECT [Date], SUM([Rate])
FROM [ProfileRate]
WHERE [Date] BETWEEN '2020-05-01' AND '2020-05-04'
GROUP BY [Date]

请帮帮我。谢谢

Answer 1

您必须执行几个步骤来解决问题：

1. 将日历记录与配置文件相结合
2. 确定哪些配置文件符合日历范围
3. 合计每个配置文件和日期范围的值
4. 最终建立日历的详细信息

此查询满足条件

with DataForCalendar as
(
SELECT IdCalendar,FirstDate,LastDate,Id,DateRate,Rate,Product
FROM
Calendar CROSS JOIN ProfileRate 
where DateRate  between '2020-05-01' and '2020-05-05'
),
DetailForCalendar as 
(
select IdCalendar,Rate,Product 
from DataForCalendar
where   DateRate between FirstDate and LastDate
),
TotalForCalendar as 
(
select IdCalendar,Product,sum(Rate)As Total from DetailForCalendar
GROUP BY IdCalendar,Product
)
select   Calendar.IdCalendar,Calendar.FirstDate,Calendar.LastDate,
TotalForCalendar.Product,TotalForCalendar.Total 
from TotalForCalendar inner join 
Calendar on TotalForCalendar.IdCalendar=Calendar.IdCalendar;

在这个例子中，你可以一步一步地找到它是如何工作的

在这个例子中，还有一个产品和一个日历。

Answer 2

我认为这是一个JOIN和条件聚合：

SELECT c.FirstDate, c.LastDate,
SUM(CASE WHEN pr.prod = 'Prod1' THEN pr.Rate END) as prod1,
SUM(CASE WHEN pr.prod = 'Prod2' THEN pr.Rate END) as prod2
FROM Calender c LEFT JOIN
ProfileRate pr
ON pr.Date >= c.FirstDate AND pr.Date <= c.LastDate
GROUP BY c.FirstDate, c.LastDate;

Answer 3

执行联接&条件聚合：

select c.fisrtdate, c.lastdate,
sum(case when pr.product = 'prod1' then rate else 0 end) as prod1,
sum(case when pr.product = 'prod2' then rate else 0 end) as prod2
from calendar c join
ProfileRate pr
on pr.date >= c.fisrtdate and pr.date <= c.lastdate
group by c.fisrtdate, c.lastdate;

如果需要所有日历日期，请使用left联接。