SQL Aggregate records by CASE WHEN

Question

I am trying to aggregate the records' sum depending on the value that match the CASE WHEN I am able to aggregate the sum but I don't know how I can group the records depending on the description value.

SQL Query:

SELECT
T1.Company, T1.DueDate, 
SUM(T1.Amount) AS TotalAmount,

DATEDIFF( day, CONVERT(DATETIME, CAST(T1.DueDate AS VARCHAR(8)), 112), DATEADD( day, - 1, CONVERT(DATETIME, CAST(T3.FromDate AS VARCHAR(8)), 112) ) ) AS Age,
CASE
      WHEN
         DATEDIFF( day, CONVERT(DATETIME, CAST(T1.DueDate AS VARCHAR(8)), 112), DATEADD( day, - 1, CONVERT(DATETIME, CAST(T3.FromDate AS VARCHAR(8)), 112) ) ) <= 0 
      THEN
         'Current'
      WHEN
         DATEDIFF( day, CONVERT(DATETIME, CAST(T1.DueDate AS VARCHAR(8)), 112), DATEADD( day, - 1, CONVERT(DATETIME, CAST(T3.FromDate AS VARCHAR(8)), 112) ) ) > 0 
         AND DATEDIFF( day, CONVERT(DATETIME, CAST(T1.DueDate AS VARCHAR(8)), 112), DATEADD( day, - 1, CONVERT(DATETIME, CAST(T3.FromDate AS VARCHAR(8)), 112) ) ) <= 30 
      THEN
        'Late'
END AS Description

FROM
    Table1 T1 
    LEFT JOIN
      Table2 T2 
      ON T1.Company = T2.Company 
      AND T1.YR = T2.YR 

    INNER JOIN
      Table3 T3 
      ON T1.Company = T3.Company 
      AND T3.YR = YEAR(CURRENT_DATE)

GROUP BY T1.Company,T1.DueDate, T3.FromDate

My query returns the following.

 Company    | DueDate        | TotalAmount | Age  | Description
------------+----------------+-------------+------+------------
 123        | 20200423       |    150      |   7  |   Late
 123        | 20200604       |    18000    |  -35 |   Current
 123        | 20200515       |    500      |  -15 |   Current

However, what I really want to accomplish is to aggregate records if they have same Description value. So, the correct result should be :

  Company   | TotalAmount  |Description |
------------+--------------+------------+
 123        |     150      |  Late
 123        |     18500    |  Current

The 2nd and 3rd row falls under "Current". Therefore, it should combine the 2 rows and add 18000 + 500 = 18500. DueDate and Age column is just for reference. The most important fields are TotalAmount and Description

I tried grouping by the alias Description which is used in CASE-WHEN but I didn't work.

I'd appreciate any help.

Thanks!

Answer 1

You copy-and-paste the CASE statement into the GROUP BY clause:

GROUP BY T1.Company, CASE WHEN ...

which is a lot of code to repeat.

Or you can use a common table expression (CTE):

;WITH
    cte AS 
    (
        SELECT
        T1.Company, T1.DueDate, T1.Amount

        DATEDIFF( day, CONVERT(DATETIME, CAST(T1.DueDate AS VARCHAR(8)), 112), DATEADD( day, - 1, CONVERT(DATETIME, CAST(T3.FromDate AS VARCHAR(8)), 112) ) ) AS Age,
        CASE
            WHEN
                DATEDIFF( day, CONVERT(DATETIME, CAST(T1.DueDate AS VARCHAR(8)), 112), DATEADD( day, - 1, CONVERT(DATETIME, CAST(T3.FromDate AS VARCHAR(8)), 112) ) ) <= 0 
            THEN
                'Current'
            WHEN
                DATEDIFF( day, CONVERT(DATETIME, CAST(T1.DueDate AS VARCHAR(8)), 112), DATEADD( day, - 1, CONVERT(DATETIME, CAST(T3.FromDate AS VARCHAR(8)), 112) ) ) > 0 
                AND DATEDIFF( day, CONVERT(DATETIME, CAST(T1.DueDate AS VARCHAR(8)), 112), DATEADD( day, - 1, CONVERT(DATETIME, CAST(T3.FromDate AS VARCHAR(8)), 112) ) ) <= 30 
            THEN
                'Late'
        END AS Description

        FROM
            Table1 T1 
            LEFT JOIN
            Table2 T2 
            ON T1.Company = T2.Company 
            AND T1.YR = T2.YR 

            INNER JOIN
            Table3 T3 
            ON T1.Company = T3.Company 
            AND T3.YR = YEAR(CURRENT_DATE)
    )

SELECT      Company, Description, SUM(Amount) AS TotalAmount
FROM        cte
GROUP BY    Company, Description