Query for Customer Receivables Ageing Report

Select distinct  Ab.CardCode,AB.CardName,AC.SlpName,

  sum(Ag.Balance_Due_Amt) as Balance_Due_Amt,

  SUM(Ag.Bellow30Days)as Bellow30Days,

  SUM(Ag.Bellow60Days)as Bellow60Days,

  SUM(Ag.Bellow75Days)as Bellow75Days,

  SUM(Ag.Bellow90Days)as Bellow90Days,

  SUM(Ag.Above90Days)as Above90Days

        From dbo.ocrd Ab inner join

                       (select

                         Ar.CardCode,

                         Ar.CardName,

                         Ar.SlpName,

                         Ar.sumtotal Balance_Due_Amt,

                         Sum(Ar.bellow_30)as Bellow30Days,

                         Sum(Ar.bellow_60)as Bellow60Days,

                         Sum(Ar.bellow_75)as Bellow75Days,

                         Sum(Ar.bellow_90)as Bellow90Days,

                         Sum(Ar.above_90)as  Above90Days

        from  (SELECT

  T2.CardCode,

  T2.CardName,

  t4.sumtotal,

  T5.SlpName,

  T4.bellow_30,

  T4.above_90,

  T4.bellow_60,

  T4.bellow_75,

  T4.bellow_90

   FROM dbo.ocrd T2 left  join(select AK.CardCode,(Ak.DocTotal-Ak.PaidToDate) as sumtotal,

  Ak.PaidToDate,

  Ak.DocTotal,

  Ak.bellow_30,

  Ak.bellow_60,

  Ak.bellow_75,

  Ak.bellow_90,

  Ak.above_90

                                    from  ( select T0.CardCode,T0.DocTotal as sumtotal,T0.DocTotal,T0.PaidToDate,

                                                   Case when DATEDIFF(DAY, T0.DocDate, GETDATE( )) >=’0′ and DATEDIFF(DAY, T0.DocDate, GETDATE( )) <’30’

                                                        then (T0.DocTotal-T0.PaidToDate)else null end as bellow_30 ,

                                                        case when DATEDIFF(DAY, T0.DocDate, GETDATE( )) >=’30’ and DATEDIFF(DAY, T0.DocDate, GETDATE( )) <’60’

                                                        then (T0.DocTotal-T0.PaidToDate)else null end as bellow_60,

                                                        case when DATEDIFF(DAY, T0.DocDate, GETDATE( )) >=’60’ and DATEDIFF(DAY, T0.DocDate, GETDATE( )) <’75’

                                                        then (T0.DocTotal-T0.PaidToDate)else null end as bellow_75 ,

                                                        case when DATEDIFF(DAY, T0.DocDate, GETDATE( )) >=’75’ and DATEDIFF(DAY, T0.DocDate, GETDATE( )) <’90’

                                                        then (T0.DocTotal-T0.PaidToDate)else null end as bellow_90,

                                                        case when DATEDIFF(DAY, T0.DocDate, GETDATE( )) >=’90’

                                                        then (T0.DocTotal-T0.PaidToDate)else null end as above_90  

          from dbo.OINV T0  

                                                       left join dbo.OSLP T1 on T0.SlpCode = T1.SlpCode

                      where  T0.DocStatus = ‘O’  

  group by T1.SlpName,T0.DocTotal,T0.DocDate,T0.PaidToDate,T0.CardCode,T0.CardName) Ak   

                                 Group by Ak.CardCode,Ak.DocTotal,Ak.PaidToDate,Ak.sumtotal,Ak.DocTotal,Ak.bellow_30,Ak.above_90,Ak.bellow_60,Ak.bellow_75,Ak.bellow_90)T4 on T2.CardCode = T4.CardCode

                      Left join OSLP T5 on T2.SlpCode = T5.SlpCode

      group by T2.CardCode,T2.CardName,T5.SlpName,T4.sumtotal,T5.SlpName,T4.bellow_30,T4.above_90,T4.bellow_60,T4.bellow_75,T4.bellow_90)Ar

  Group by Ar.CardCode,Ar.CardName,Ar.SlpName,Ar.sumtotal)Ag on Ab.CardCode = Ag.CardCode

  Left Join OSLP Ac on Ab.SlpCode = Ac.SlpCode

  WHERE Ag.Balance_Due_Amt IS NOT NULL AND Ac.SlpName = ‘[%0]’

  Group by Ab.CardCode,Ab.CardName,Ac.SlpName

  order by Ab.CardCode