Query for Customer Receivables Ageing Report

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