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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.