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"
