Query for Customer Aeging Report with date
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