asked on

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

ASKER CERTIFIED SOLUTION

Brendt Hess

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

irmsystems

ASKER

Thanks,

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

Brendt Hess

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],
....

irmsystems

ASKER

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

Brendt Hess

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

irmsystems

ASKER

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.