Aged Receivables

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
irmsystemsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Brendt HessConnect With a Mentor Senior DBACommented:
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

0
 
irmsystemsAuthor Commented:
Thanks,

That does what I need it to do. Is there a way to get a total row for each column as well?
0
 
Brendt HessSenior DBACommented:
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],
....
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
irmsystemsAuthor 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
0
 
Brendt HessSenior DBACommented:
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
0
 
irmsystemsAuthor 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.
0
All Courses

From novice to tech pro — start learning today.