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) AS ,
COALESCE(,0) AS ,
COALESCE(,0) AS ,
COALESCE(,0) AS ,
COALESCE(,0) AS ,
COALESCE(,0) AS ,
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 (, , , , , , [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) AS ,
COALESCE(,0) AS ,
COALESCE(,0) AS ,
COALESCE(,0) AS ,
COALESCE(,0) AS ,
COALESCE(,0) AS ,
--                 COALESCE([TotalAll],0) AS [TotalAll]
COALESCE(,0) + COALESCE(,0) + COALESCE(,0) + COALESCE(,0) + COALESCE(,0) + COALESCE(,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 (, , , , , , [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) AS ,
COALESCE(,0) AS ,
....

Commented:
I added this to the last line of the query for the group by to work:
GROUP BY AgeGroup, , , , , , , [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) , COALESCE(,0), COALESCE(,0), COALESCE(,0), COALESCE(,0), COALESCE(,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.