We help IT Professionals succeed at work.

# Aged Receivables

on
Hi,

I am trying to use pivot tables to create a table of aged receivables. Here is the select statement I have currently.

SELECT AgeGroup, COALESCE([0],0) AS [0],
COALESCE([30],0) AS [30],
COALESCE([60],0) AS [60],
COALESCE([90],0) AS [90],
COALESCE([120],0) AS [120],
COALESCE([121],0) AS [121],
COALESCE([TotalAll],0) AS [TotalAll]
FROM
(SELECT CASE
WHEN (trl_modfk IS NULL) THEN '3'
WHEN (trl_modfk = 1) THEN '1'
WHEN (trl_modfk = 2) THEN '2'
END AS AgeGroup,
CASE
WHEN (DATEDIFF(day, tra_due, GETDATE()) <= 0) THEN '0'
WHEN (DATEDIFF(day, tra_due, GETDATE()) <= 30) THEN '30'
WHEN (DATEDIFF(day, tra_due, GETDATE()) <= 60) THEN '60'
WHEN (DATEDIFF(day, tra_due, GETDATE()) <= 90) THEN '90'
WHEN (DATEDIFF(day, tra_due, GETDATE()) <= 120) THEN '120'
WHEN (DATEDIFF(day, tra_due, GETDATE()) >= 121) THEN '121'
WHEN (tra_due IS NULL) THEN 'TotalAll'
END AS Age, tra_balance
FROM #TempTra) AS SourceTable
PIVOT
(
SUM(tra_balance)
FOR Age IN ([0], [30], [60], [90], [120], [121], [TotalAll])
) AS PivotTable

The results I get are:

AgeGroup 0                     30                    60                    90                    120                   121                   TotalAll
-------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
1        0.00                  0.00                  0.00                  0.00                  0.00                  243.53                0.00
2        0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00

Is there a way to total the values from the 0,30,60,90,120 columns into the TotalAll column using the select statement I have above? I don't know if the select above will make sense but I'm trying to get a result set of aged receivables and the total for an age group.

Thanks,
Tyler
Comment
Watch Question

## View Solution Only

Senior DBA
CERTIFIED EXPERT
Commented:
Try this:

SELECT AgeGroup, COALESCE([0],0) AS [0],
COALESCE([30],0) AS [30],
COALESCE([60],0) AS [60],
COALESCE([90],0) AS [90],
COALESCE([120],0) AS [120],
COALESCE([121],0) AS [121],
--                 COALESCE([TotalAll],0) AS [TotalAll]
COALESCE([0],0) + COALESCE([30],0) + COALESCE([60],0) + COALESCE([90],0) + COALESCE([120],0) + COALESCE([121],0) as [TotalAll]
FROM
(SELECT CASE
WHEN (trl_modfk IS NULL) THEN '3'
WHEN (trl_modfk = 1) THEN '1'
WHEN (trl_modfk = 2) THEN '2'
END AS AgeGroup,
CASE
WHEN (DATEDIFF(day, tra_due, GETDATE()) <= 0) THEN '0'
WHEN (DATEDIFF(day, tra_due, GETDATE()) <= 30) THEN '30'
WHEN (DATEDIFF(day, tra_due, GETDATE()) <= 60) THEN '60'
WHEN (DATEDIFF(day, tra_due, GETDATE()) <= 90) THEN '90'
WHEN (DATEDIFF(day, tra_due, GETDATE()) <= 120) THEN '120'
WHEN (DATEDIFF(day, tra_due, GETDATE()) >= 121) THEN '121'
--            WHEN (tra_due IS NULL) THEN 'TotalAll'
END AS Age, tra_balance
FROM #TempTra) AS SourceTable
PIVOT
(
SUM(tra_balance)
FOR Age IN ([0], [30], [60], [90], [120], [121], [TotalAll])
) AS PivotTable

Commented:
Thanks,

That does what I need it to do. Is there a way to get a total row for each column as well?
Senior DBA
CERTIFIED EXPERT

Commented:
Try adding this as the last line of the query:
GROUP BY AgeGroup WITH ROLLUP

You can also add this change:

SELECT CASE WHEN AgeGroup IS NULL THEN 'Total' ELSE Cast(AgeGroup as varchar(5)) END As AgeGroup,
COALESCE([0],0) AS [0],
COALESCE([30],0) AS [30],
....

Commented:
I added this to the last line of the query for the group by to work:
GROUP BY AgeGroup, [0], [30], [60], [90], [120], [121], [TotalAll] WITH ROLLUP

I then got the results:
AgeGroup 0                     30                    60                    90                    120                   121                   TotalAll
-------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
1        0.00                  0.00                  0.00                  0.00                  0.00                  408.10                408.10
1        0.00                  0.00                  0.00                  0.00                  0.00                  408.10                408.10
1        0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00
1        0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00
1        0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00
1        0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00
1        0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00
1        0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00
Total    0.00                  0.00                  0.00                  0.00                  0.00                  0.00                  0.00
Senior DBA
CERTIFIED EXPERT

Commented:
At the very least, try adding the COALESCE to the GROUP statement, e.g.

GROUP BY AgeGroup, COALESCE([0],0) , COALESCE([30],0), COALESCE([60],0), COALESCE([90],0), COALESCE([120],0), COALESCE([121],0), [TotalAll] WITH ROLLUP

Commented:
Hey,

I can't get a totals row to work with the select I have. I have tried the GROUP BY that you suggested but it doesn't give me the results I was looking for. I will do the totals row in code.