Account Receivable Aging report with sql subquery
I am so late on this project for work.
I think I am the home stretch though. I have two tables pay
and chg
. trying to do the aging buckets first, then I want to subtract the correlating buckets(chg
(chg
0-30)-pmt
(pmt
0-30)) Am I on the right track.
SELECT fgc, sum(pay) from
(SELECT fgc, pay,
CASE
WHEN [date]<= 30 THEN 'pmt 0-30'
WHEN [date]> 30 AND [date] <= 60 THEN 'pmt 30-60'
WHEN [date]> 61 AND [date] <= 90 THEN 'pmt 61-90'
WHEN [date]> 91 AND [date] <= 120 THEN 'pmt 91-120'
WHEN [date]> 121 AND [date] <= 150 THEN 'pmt 121-150'
WHEN [date]> 151 AND [date] <= 180开发者_如何转开发 THEN 'pmt 151-180'
ELSE 'pmt 181+'
END)
FROM @pay(fgc,pay,[date])
GROUP by fgc
SELECT fgc, sum(chg) from
(SELECT fgc, chg,
CASE
WHEN [date]<= 30 THEN 'pmt 0-30'
WHEN [date]> 30 AND [date] <= 60 THEN 'charge 30-60'
WHEN [date]> 61 AND [date] <= 90 THEN 'charge 61-90'
WHEN [date]> 91 AND [date] <= 120 THEN 'charge 91-120'
WHEN [date]> 121 AND [date] <= 150 THEN 'charge 121-150'
WHEN [date]> 151 AND [date] <= 180 THEN 'charge 151-180'
ELSE 'pmt 181+'
END)
FROM @chg (fgc,chg,[date])
GROUP by fgc
This should get you started:
select fgc,sum(Totpay) as TotPay,Sum(TotChg) as TotChg
from
(
SELECT fgc, sum(pay) as TotPay,0 as TotChg
from
(SELECT fgc, pay,
CASE
WHEN [date]<= 30 THEN 'pmt 0-30'
WHEN [date]> 30 AND [date] <= 60 THEN 'pmt 30-60'
WHEN [date]> 61 AND [date] <= 90 THEN 'pmt 61-90'
WHEN [date]> 91 AND [date] <= 120 THEN 'pmt 91-120'
WHEN [date]> 121 AND [date] <= 150 THEN 'pmt 121-150'
WHEN [date]> 151 AND [date] <= 180 THEN 'pmt 151-180'
ELSE 'pmt 181+'
END)
FROM @pay
GROUP by fgc
UNION
SELECT fgc, 0 as TotPay,sum(chg) as TotChg
from
(SELECT fgc, chg,
CASE
WHEN [date]<= 30 THEN 'pmt 0-30'
WHEN [date]> 30 AND [date] <= 60 THEN 'charge 30-60'
WHEN [date]> 61 AND [date] <= 90 THEN 'charge 61-90'
WHEN [date]> 91 AND [date] <= 120 THEN 'charge 91-120'
WHEN [date]> 121 AND [date] <= 150 THEN 'charge 121-150'
WHEN [date]> 151 AND [date] <= 180 THEN 'charge 151-180'
ELSE 'pmt 181+'
END)
FROM @chg
GROUP by fgc
) xx
GROUP BY fgc
精彩评论