A/R Aging Report

[Former Member]

Dear Experts,

Good day!

I have seen a report here in SCN where it allows me to generated Aging Report using a query.

select T1.cardcode 'Bp Code',T1.cardname 'Name',sysdeb 'Debit Amount',syscred 'Credit Amount',

(T0.BALDUEDEB - T0.BALDUECRED) as 'Balance Due',

case T0.transtype

when '13' then 'INV'

when '14' then 'AR CN'

when '24' then 'INCOMING'

else 'Other'

end 'Type',

Ref1,

fccurrency 'BP Currency',

CONVERT(VARCHAR(10), refdate, 103) 'Posting Date',

CONVERT(VARCHAR(10), duedate, 103) 'Due Date',

CONVERT(VARCHAR(10), taxdate, 103) 'Doc Date' ,

CASE when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31 then

case

when BALDUECRED <> 0 then -BALDUECRED

else BALDUEDEB

end

end "0-30 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 30

and (datediff(dd,refdate,current_timestamp))+1< 61)

then

case

when BALDUECRED <> 0 then -BALDUECRED

else BALDUEDEB

end

end "31 to 60 days",

case when ((datediff(dd,refdate,current_timestamp))+1 > 60

and (datediff(dd,refdate,current_timestamp))+1< 91)

then case when BALDUECRED <> 0 then -BALDUECRED else BALDUEDEB  end end "61 to 90 days",

CASE

when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90

then

case

when BALDUECRED= 0 then BALDUEDEB

when BALDUEDEB= 0 then -BALDUECRED

end

end "90 + days"

from dbo.JDT1 T0

INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'

where T0.intrnmatch = '0' and T0.BALDUEDEB != T0.BALDUECRED and t1.CardName='[%0]'

ORDER BY T1.CARDCODE, T0.taxdate

The problem is the correct age per transaction was not displayed because the report is considering only the current system date. What if I want to see the report for a specific period of time and not only the current date?

Thank you!

Ashley

[BattleshipCobra]

Hello!  If you add a variable for the date you can simply replace the timestamp with whatever you want.

Try this:

DECLARE @AgingDate DATETIME = /* SELECT FROM dbo.OINV T0 WHERE T0.DocDate >= */ '[%0]' 
DECLARE @CardName NVARCHAR(150) = /* SELECT FROM dbo.OCRD T0 WHERE T0.CardName <= */ '[%1]' 


SELECT
  T1.cardcode 'Bp Code'
  ,T1.cardname 'Name'
  ,sysdeb 'Debit Amount'
  ,syscred 'Credit Amount'
  ,(T0.BALDUEDEB - T0.BALDUECRED) as 'Balance Due'
  ,CASE T0.transtype
    WHEN '13' THEN 'INV'
    WHEN '14' THEN 'AR CN'
    WHEN '24' THEN 'INCOMING'
  ELSE 'Other' END 'Type'
  ,Ref1
  ,fccurrency 'BP Currency'
  ,CONVERT(VARCHAR(10), refdate, 103) 'Posting Date'
  ,CONVERT(VARCHAR(10), duedate, 103) 'Due Date'
  ,CONVERT(VARCHAR(10), taxdate, 103) 'Doc Date'
  ,CASE
    WHEN (DATEDIFF(dd,refdate,@AgingDate))+1 < 31 THEN
      CASE
        WHEN BALDUECRED <> 0 THEN -BALDUECRED
        ELSE BALDUEDEB
      END
    END '0-30 days',
  CASE
    WHEN ((datediff(dd,refdate,@AgingDate))+1 > 30 AND (datediff(dd,refdate,@AgingDate))+1< 61)
    THEN
      CASE
        WHEN BALDUECRED <> 0 THEN -BALDUECRED
        ELSE BALDUEDEB
      END
    END '31 to 60 days',
  CASE
    WHEN ((datediff(dd,refdate,@AgingDate))+1 > 60 AND (datediff(dd,refdate,@AgingDate))+1< 91) THEN
      CASE
        WHEN BALDUECRED <> 0 THEN -BALDUECRED ELSE BALDUEDEB
        END
      END '61 to 90 days'
  ,CASE
    WHEN (DATEDIFF(dd,refdate,@AgingDate))+1 > 90 THEN
      CASE
        WHEN BALDUECRED= 0 THEN BALDUEDEB
        WHEN BALDUEDEB= 0 THEN -BALDUECRED
      END
    END '90 + days'

FROM
  JDT1 T0
  INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode AND T1.CardType = 'c'

WHERE
  T0.intrnmatch = '0'
  AND
  T0.BALDUEDEB != T0.BALDUECRED
  AND
  T1.CardName = @CardName

ORDER BY T1.CARDCODE, T0.taxdate

[Former Member]

Hi Mike Taylor,

Thank you for your feedback.

Where did you get the aging date? Is it the variable that you're pertaining to?

Thanks!

Ashley