Account Aging Balance Detail on Specified Date

I want to see a detailed view of the balance for a particular account on a previous date. The aging report gives me the balance on that date, but I want to see the detail for each item that contributes to the aggregate balance figure.

I think this may give you what you need. You will notice on line 15 (just before the WHERE):

 –,*

 If you remove the dashes at the beginning of that line, it will add many more columns in the output. You can pick and choose from those to add above that line, then put the dashes back in to hide the extra columns. In the WHERE, just change the ID to the account you want to check out.

This query will list only those chargesplits that were still carrying a balance on June 30, 2012. For any other date, change the dates in both line 13, 14, and line 29.

select 
  a.jnum,
  a.id,
  a.trandate as "service date",
  a.srvcode,
  a.amount as "fee",
  d.payorname as "responsible payor",
  a.chgspldate as "chg split date",
  a.chgsplamt as "chg split amt",
  (select 
      coalesce(sum(crsplamt),0) from sos.rv_creditsplits 
   where 
      cre_date < '2012-07-01'  
      and dateapplied < '2012-07-01' 
      and chgsplnum = a.chgsplnum) as "credits applied",
  a.chgsplamt - "credits applied"  as "chg split balance",
  b.crsplamt as "credit split amt",
  b.credtype as "credit type", 
  b.dateapplied as "date applied"
 --,*
from 
  sos.rv_charges a 
  left outer join sos.rv_creditsplits b on a.chgsplnum = b.chgsplnum
  join sos.ptpayors c on a.ptpayornum = c.ptpayornum
  join sos.payors d on c.payornum = d.payornum
where 
  a.id = '123456789'
  and "chg split balance" <> 0
  and a.chgspldate < '2012-07-01'
order by 
  "service date","chg split date"

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.