How to calculate bucket values in SQL server using CASE statement

Question

I have a 'Schedule' table like this with a schedule for account = 'Acctxyz':

Account   DueDate        Amount

Acctxyz   2018-03-09    3049.00 
Acctxyz   2018-03-23    17857.00    
Acctxyz   2018-04-06    17949.00    
Acctxyz   2018-04-20    18042.00    
Acctxyz   2018-05-04    18135.00    
Acctxyz   2018-05-18    18229.00    
Acctxyz   2018-06-01    18324.00    
Acctxyz   2018-06-15    18419.00    
Acctxyz   2018-06-29    18514.00    

My input date should be 2017-07-09 and the output should be with Account column and other four fields with the Amounts should be summed up in four buckets based on date difference with my inputdate and Duedate, like below;

 Account   Late   Arrears   SeriousArrears  NonPerforming 
 Acctxyz    ####   ####       ####           #### 

• Late should be sum of Amounts with datedifference 2 and 30
• Arrears should be sum of Amounts with datedifference 31 and 60
• SeriousArrears should be sum of Amounts with datedifference 60 and 90
• NonPerforming should be sum of Amounts with datedifference >= 91

And below is what I did;

declare @Rundate date = '20180709'
select Account,
CASE WHEN DATEDIFF(D,@Rundate,DueDate) BETWEEN -2 AND -30 THEN SUM(Amount) ELSE 0 END as Late,  
CASE WHEN DATEDIFF(D,@Rundate,DueDate) BETWEEN -31 AND -60 THEN SUM(Amount) ELSE 0 END as Arrears,
CASE WHEN DATEDIFF(D,@Rundate,DueDate) BETWEEN -61 AND -90 THEN SUM(Amount) ELSE 0 END as SeriousArrears,
CASE WHEN DATEDIFF(D,@Rundate,DueDate) >= -91 THEN SUM(Amount) ELSE 0 END as NonPerforming
from Schedule
group by Acct,DueDateKey

I'm getting a output with nine rows and all the fields with Zero's.

Much appreciate how to achieve the excepted output and please pointout where am i making mistake.

Answer 1

BETWEEN -2 AND -30 means a number greater than -2 and less than -30. There is none. You have to be careful using between with negative values. Change your DATEDIFF to get positive day difference.

Try this:

SELECT   Account ,
         CASE WHEN DATEDIFF(D, DueDate, @Rundate)
                   BETWEEN 2 AND 30 THEN SUM(Amount)
              ELSE 0
         END AS Late ,
         CASE WHEN DATEDIFF(D, DueDate, @Rundate)
                   BETWEEN 31 AND 60 THEN SUM(Amount)
              ELSE 0
         END AS Arrears ,
         CASE WHEN DATEDIFF(D, DueDate, @Rundate)
                   BETWEEN 61 AND 90 THEN SUM(Amount)
              ELSE 0
         END AS SeriousArrears ,
         CASE WHEN DATEDIFF(D, DueDate, @Rundate) >= 91 THEN SUM(Amount)
              ELSE 0
         END AS NonPerforming
FROM Schedule
GROUP BY Account, DueDate

Update If you need a single row as result set:

CREATE TABLE #Schedule
    (
        Account VARCHAR(10) ,
        DueDate DATE ,
        Amount DECIMAL(10, 2)
    );

INSERT INTO #Schedule ( Account ,
                        DueDate ,
                        Amount )
VALUES ( 'Acctxyz', '2018-03-09', 3049.00 ) ,
       ( 'Acctxyz', '2018-03-23', 17857.00 ) ,
       ( 'Acctxyz', '2018-04-06', 17949.00 ) ,
       ( 'Acctxyz', '2018-04-20', 18042.00 ) ,
       ( 'Acctxyz', '2018-05-04', 18135.00 ) ,
       ( 'Acctxyz', '2018-05-18', 18229.00 ) ,
       ( 'Acctxyz', '2018-06-01', 18324.00 ) ,
       ( 'Acctxyz', '2018-06-15', 18419.00 ) ,
       ( 'Acctxyz', '2018-06-29', 18514.00  );

DECLARE @Rundate DATE = '20180709';
SELECT   t.Account ,
         SUM(Late) AS Late ,
         SUM(Arrears) AS Late ,
         SUM(SeriousArrears) AS SeriousArrears ,
         SUM(NonPerforming) AS NonPerforming
FROM     (   SELECT   Account ,
                      CASE WHEN DATEDIFF(D, DueDate, @Rundate)
                                BETWEEN 2 AND 30 THEN SUM(Amount)
                           ELSE 0
                      END AS Late ,
                      CASE WHEN DATEDIFF(D, DueDate, @Rundate)
                                BETWEEN 31 AND 60 THEN SUM(Amount)
                           ELSE 0
                      END AS Arrears ,
                      CASE WHEN DATEDIFF(D, DueDate, @Rundate)
                                BETWEEN 61 AND 90 THEN SUM(Amount)
                           ELSE 0
                      END AS SeriousArrears ,
                      CASE WHEN DATEDIFF(D, DueDate, @Rundate) >= 91 THEN
                               SUM(Amount)
                           ELSE 0
                      END AS NonPerforming
             FROM     #Schedule
             GROUP BY Account ,
                      DueDate ) t
GROUP BY t.Account;

DROP TABLE #Schedule;

Result:

+---------+----------+----------+----------------+---------------+
| Account |   Late   |   Late   | SeriousArrears | NonPerforming |
+---------+----------+----------+----------------+---------------+
| Acctxyz | 36933.00 | 36553.00 | 36177.00       | 38855.00      |
+---------+----------+----------+----------------+---------------+