How to achieve the bucket values in SQL?

Question

I have schedule table like this (table name = 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  

I need to sum the Amount field based on specific bucket values as shown below

• Principal_7To30_Days

• Principal_1To3_Months

• Principal_3To6_Months

• Principal_6To12_Months

• Principal_1To3_Years

by giving an input date

And my input date is 2018-07-09 and below is my query;

;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

And below is my output

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   

But the correct output should be

    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

So the problem is i'm getting wrong values for Principal_1To3_Months and Principal_3To6_Months, When I asked my client how do they calculate this in their legacy system, they replied that they calculate using +-months by adding number of months and not days. So if today is 2018-07-09 + 3 months we will get 2018-10-09.

So I used the month difference in my cte query as below

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

And use this in my overall query as below

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

But this time also I get the same values as mentioned in my very first output. Can someone please point out where is my mistake and how to achieve this values as mentioned in correct output

Answer 1

I wouldn't use DATEDIFF to calculation day or month difference days, because there is some month have 31 days, others month have 30 days.

Therefore, the calculated difference days are not accurate.

I would use DATEADD instead of DATEDIFF to do the condition.

;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

[Results]:

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

Note

You can use CASE WHEN with SUM Aggregate function instead of select subquery, the performance will be better.