Query for a sepcific GL account

Dayton,

You can start maybe from something like this:

select T0.Account 'Account', T0.sysdeb 'Debit GBP', T0.syscred 'Credit GBP',
case T0.transtype 
when '13' then 'AR Invoice'
when '14' then 'AR Credit Note'
when '24' then 'Incoming Payment'
else 'Other'
end 'Type',
T0.linememo 'Memo',
T0.Ref1,
CONVERT(VARCHAR(10), T0.refdate, 103)'Posting Date' ,
CONVERT(VARCHAR(10), T0.duedate, 103) 'Due Date',
CONVERT(VARCHAR(10), T0.taxdate, 103) 'Doc Date' ,

CASE when DATEDIFF(dd,T0.taxdate,current_timestamp) < 31 
then case
when T0.syscred <> 0 then T0.syscred * - 1
else T0.sysdeb
end
end "0-30 days",

CASE when (datediff(dd,T0.taxdate,current_timestamp) > 30 
and datediff(dd,T0.taxdate,current_timestamp)< 61) 
then case
when T0.syscred <> 0 then T0.syscred * - 1
else T0.sysdeb
end 
end "31 to 60 days",

CASE when (datediff(dd,T0.taxdate,current_timestamp) > 60 
and datediff(dd,T0.taxdate,current_timestamp)< 91) 
then case
when T0.syscred <> 0 then T0.syscred * - 1
else T0.SYSDeb
end 
end "61 to 90 days",

CASE when (datediff(dd,T0.TaxDate,current_timestamp) > 91 
and datediff(dd,T0.TaxDate,current_timestamp)< 120) 
then case
when T0.SYSCred <> 0 then T0.SYSCred * - 1
else T0.SYSDeb
end 
end "91 to 120 days",

CASE
when DATEDIFF(dd,T0.TaxDate,current_timestamp) > 121 
then case
when T0.SYSCred <> 0 then T0.SYSCred * - 1
else T0.SYSDeb
end
end "121+ days"

from JDT1 T0
 where T0.Account = [%1]

It was originally an ageing report for BP so you might want to amend it to show the fields you need.

Regards,

Nat