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