如何在SQL中实现bucket值

Question

我有这样的时间表(表名=testSch(

ID  Amount  scheduleDate     
1   7230.00  2018-07-13  
1   7272.00  2018-07-27  
1   7314.00  2018-08-10  
1   7356.00  2018-08-24  
1   7398.00  2018-09-07  
1   7441.00  2018-09-21  
1   7439.00  2018-10-08  
1   7526.00  2018-10-22  
1   7570.00  2018-11-05  
1   7613.00  2018-11-19  
1   5756.00  2018-12-03  

我需要根据特定的桶值对金额字段求和，如下所示

• 校长_7To30_Days

• 本金_1To3_Months

• 校长3至6个月

• 校长_6To12Months

• 校长1至3年

通过给出输入日期

我的输入日期是2018-07-09，下面是我的查询；

;with cteSchedule as (
select *,DATEDIFF(DAY,'20180709',scheduleDate) as datedifference, 
DATEDIFF(MONTH,'20180709',scheduleDate) as monthdifference from testSch)
select  ISNULL((SELECT SUM(cteSchedule.Amount) 
FROM cteSchedule 
WHERE cteSchedule.datedifference <7),0)  AS Principal_0To7_Days, 
ISNULL((SELECT SUM(cteSchedule.Amount) 
FROM cteSchedule 
WHERE cteSchedule.datedifference>=7 and cteSchedule.datedifference<30),0)  
AS Principal_7To30_Days,
ISNULL((SELECT SUM(cteSchedule.Amount) 
FROM cteSchedule 
WHERE cteSchedule.datedifference>=30 and cteSchedule.datedifference<90),0)  AS Principal_1To3_Months,
ISNULL((SELECT SUM(cteSchedule.Amount) 
FROM cteSchedule 
WHERE cteSchedule.datedifference>=90 and cteSchedule.datedifference<180),0)  AS Principal_3To6_Months,
ISNULL((SELECT SUM(cteSchedule.Amount) 
FROM cteSchedule 
WHERE cteSchedule.datedifference>=180 and cteSchedule.datedifference<365),0)  AS Principal_6To12_Months

下面是我的输出

Principal_0To7_Days Principal_7To30_Days    Principal_1To3_Months Principal_3To6_Months Principal_6To12_Months  
7230.00             7272.00                 29509.00              35904.00       0.00   

但正确的输出应该是

Principal_0To7_Days Principal_7To30_Days    Principal_1To3_Months Principal_3To6_Months Principal_6To12_Months  
7230.00             7272.00                     36948.00              28465.00       0.00

因此，问题是我得到了Principal_1To_Months和Principal_3To_Months的错误值。当我问我的客户在他们的遗留系统中如何计算时，他们回答说，他们通过添加月数而不是天数来计算+-月。因此，如果今天是2018-07-09+3个月，我们将获得2018-10-09。

所以我在cte查询中使用了月差，如下

DATEDIFF(MONTH,'20180709',scheduleDate) as monthdifference

在我的整体查询中使用这个，如下

ISNULL((SELECT SUM(cteSchedule.Amount) 
FROM cteSchedule 
WHERE cteSchedule.monthdifference>=1 and cteSchedule.monthdifference<=3),0)  AS Principal_1To3_Months

但这一次，我也得到了与第一次输出中提到的相同的值。有人能指出我的错误在哪里，以及如何实现正确输出中提到的值吗

Answer 1

我不会用DATEDIFF来计算day或month的差异天数，因为有些月份有31天，有些月份有30天。

因此，计算的差异天数并不准确。

我会用DATEADD而不是DATEDIFF来做这个条件。

;with cteSchedule as (
select *,'20180709' compareDay
from testSch
)
SELECT Sum(CASE 
WHEN t.scheduleDate < DATEADD(day, 7, compareDay) 
THEN t.amount
ELSE 0
END) AS  Principal_0To7_Days,
Sum(CASE 
WHEN t.scheduleDate >=DATEADD(day, 7, compareDay) AND t.scheduleDate < DATEADD(day, 30, compareDay) 
THEN t.amount
ELSE 0
END) AS Principal_7To30_Days,
Sum(CASE 
WHEN t.scheduleDate >=DATEADD(month,1,compareDay) AND t.scheduleDate < DATEADD(month,3,compareDay)
THEN t.amount
ELSE 0
END) AS Principal_1To3_Months,
Sum(CASE 
WHEN  t.scheduleDate >=DATEADD(month,3,compareDay) AND t.scheduleDate < DATEADD(month,6,compareDay)
THEN t.amount
ELSE 0
END) AS Principal_3To6_Months,
Sum(CASE 
WHEN t.scheduleDate >=DATEADD(month,6,compareDay) AND t.scheduleDate < DATEADD(month,12,compareDay)
THEN t.amount
ELSE 0
END) AS Principal_6To12_Months
from cteSchedule t

SQLFiddle

[结果]:

| Principal_0To7_Days | Principal_7To30_Days | Principal_1To3_Months | Principal_3To6_Months | Principal_6To12_Months |
|---------------------|----------------------|-----------------------|-----------------------|------------------------|
|                7230 |                 7272 |                 36948 |                 28465 |                      0 |

注意

您可以将CASE WHEN与SUM一起使用聚合函数代替select子查询，性能会更好。