Group by aged days and sum the invoice accordingly

Question

question

excelsql987 asked Jan 12, '20

Group by aged days and sum the invoice accordingly

USE [CCS]
SELECT ISNULL(be.[FY],'Total') as FY,
sum(be.[Invoice Amount]) as 'Total Amount',
DATEDIFF (DAY,be.[Due Date] ,ft.[EndDate])as Aged,
CASE
WHEN Aged <=365 then sum(be.[Invoice Amount]),0) as 'UnderOneYear'---this is where i get the error ','
WHEN Aged >365 and Aged <=730 then sum(be.[Invoice Amount]),0) as '1_2 years'
WHEN Aged >730 and Aged <=1095 then sum(be.[Invoice Amount]),0) as '2_3 years'
WHEN Aged >1095 then sum(be.[Invoice Amount]),0) as 'Over_3_years'
END as Aged
FROM ([dbo].[FiscalDateTable] as ft
INNER JOIN ([dbo].[TB_DATA_All] as be
LEFT JOIN ([dbo].[Master Reason Codes List] AS mrc
ON be.[Reason Code] = mrc.[Reason Code])
ON ft.[Fiscal YYYY-MM] = be.FY)
INNER JOIN ([dbo].[Master Profit Center List_All Divisions_New] as mpc
ON be.[Profit Center] = mpc.[Profit Centers])
AND be.CoCd = mpc.[Company FY15])
WHERE (be.[Reconciliation Account]) LIKE'%24%' AND mpc.New_Div ='DI' AND be.FY 
BETWEEN '2018-12' AND '2019-12' 
AND be.[Invoice Amount]<0
GROUP BY be.[FY], be.[Aged]

sql server 2012

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-01-14T18:13:56Z

Jon Crawford answered Jan 14, '20

You can't SUM() and alias each WHEN...THEN statement in your CASE, that's all only one column being returned. Also, you can't use the "Aged" as it's being calculated on the fly. You want to do a crosstab something like this:

SELECT ISNULL(be.[FY],'Total')as FY, sum(be.[Invoice Amount]) as 'Total Amount',

--DATEDIFF (DAY,be.[Due Date],ft.[EndDate]) as Aged,

SUM(CASE WHEN DATEDIFF (DAY,be.[Due Date],ft.[EndDate])<=365 then be.[Invoice Amount] ELSE 0 END) as 'UnderOneYear',

SUM(CASE WHEN DATEDIFF (DAY,be.[Due Date],ft.[EndDate])>365 and DATEDIFF (DAY,be.[Due Date],ft.[EndDate])<=730 then be.[Invoice Amount] ELSE 0) as '1_2 years',

SUM(CASE WHEN DATEDIFF (DAY,be.[Due Date],ft.[EndDate])>730 and DATEDIFF (DAY,be.[Due Date],ft.[EndDate])<=1095 then be.[Invoice Amount] ELSE 0) as '2_3 years',

SUM(CASE WHEN DATEDIFF (DAY,be.[Due Date],ft.[EndDate])>1095 then be.[Invoice Amount] ELSE 0) as 'Over_3_years'

FROM <whatever>

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question