Query for Customer Aeging Report with date

[thobenjo1]

Hi Guys in this Query Can I add to DueDate Range,An opion to select the date range or till this date.

Query

--

-- SAP B1 Customer Receivables Aeging - By Due Date

--

--

--

-- Version 10022008

--

select OCRD.cardcode 'Customer Code',OCRD.cardname 'Customer Name', OCRD.CreditLine,

(select PymntGroup FROM OCTG WHERE GroupNum=OCRD.GroupNum) as [Payment Group],

(select SlpName FROM OSLP WHERE SlpCode=OCRD.SlpCode) as [Sales Employee],

SUM(case when syscred <> 0 then syscred * - 1

else sysdeb

end) "Balance Due",

ISNULL(SUM(CASE when DATEDIFF(dd,JDT1.DueDate,current_timestamp) < 0

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "Non Due",

ISNULL(SUM(CASE when (DATEDIFF(dd,JDT1.DueDate,current_timestamp) >= 0

and datediff(dd,JDT1.DueDate,current_timestamp)< 30)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "1-30 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 30

and datediff(dd,JDT1.DueDate,current_timestamp)< 60)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "31 to 60 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 60

and datediff(dd,JDT1.DueDate,current_timestamp)< 90)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "61 to 90 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 90

and datediff(dd,JDT1.DueDate,current_timestamp) < 120)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "91 to 120 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 120

and datediff(dd,JDT1.DueDate,current_timestamp) < 150)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "121 to 150 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 150

and datediff(dd,JDT1.DueDate,current_timestamp) < 180)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "151 to 180 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 180

and datediff(dd,JDT1.DueDate,current_timestamp) < 210)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "181 to 210 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 210

and datediff(dd,JDT1.DueDate,current_timestamp) < 240)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "211 to 240 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 240

and datediff(dd,JDT1.DueDate,current_timestamp) < 270)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "241 to 270 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 270

and datediff(dd,JDT1.DueDate,current_timestamp) < 300)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "271 to 300 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 300

and datediff(dd,JDT1.DueDate,current_timestamp) < 330)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "301 to 330 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 330

and datediff(dd,JDT1.DueDate,current_timestamp) < 365)

then

case

when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1

else JDT1.BalDueDeb

end

end),0.00) "331 to 365 days",

ISNULL(SUM(CASE when DATEDIFF(dd,JDT1.DueDate,current_timestamp) >= 365

then

case

when BalDueCred <> 0 then BalDueCred * - 1

else BalDueDeb

end

end),0.00) "365+ days"

from JDT1,OCRD

WHERE JDT1.shortname = OCRD.cardcode and OCRD.cardtype = 'C'

GROUP BY OCRD.cardcode, OCRD.cardname,OCRD.CreditLine,OCRD.GroupNum,OCRD.SlpCode

--Filters Out Zero Balances

HAVING SUM(case when syscred <> 0 then syscred * - 1

else sysdeb

end) > 0 OR SUM(case when syscred <> 0 then syscred * - 1

else sysdeb

end) < 0

ORDER BY OCRD.CARDCODE