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"