Is there a query out there that takes the unapplied (pre-pay) figure (obtained from an Aging Report) and breaks it down over a specified time period? I assume there is a date there is a date attached to the unapplied credit, probably the date that it was first entered in SOS?
Can a query give me the amount that remains unapplied and break down this total according to a range of times? E.g.,
Grand Total of unapplied 0-30 31-60 61-90 91-120 over 120
$63,852.72 51,800.00 8050.50 3000 1000. 802.22
SELECT
lastname,firstname,"id",
SUM(crsplamt) AS "Total Unapplied",
(SELECT SUM(crsplamt) FROM sos.jcrsplits crs join sos.journal jou on crs.jnum=jou.jnum WHERE jou.ptnum = a.ptnum AND crs.chgsplnum = 0 AND jou.trandate between (today() - 30) and today() ) as "CURRENT",
(SELECT SUM(crsplamt) FROM sos.jcrsplits crs join sos.journal jou on crs.jnum=jou.jnum WHERE jou.ptnum = a.ptnum AND crs.chgsplnum = 0 AND jou.trandate between (today() - 60) and(today()-31) ) as "31 - 60",
(SELECT SUM(crsplamt) FROM sos.jcrsplits crs join sos.journal jou on crs.jnum=jou.jnum WHERE jou.ptnum = a.ptnum AND crs.chgsplnum = 0 AND jou.trandate between (today() - 90) and (today()-61) ) as "61 - 90",
(SELECT SUM(crsplamt) FROM sos.jcrsplits crs join sos.journal jou on crs.jnum=jou.jnum WHERE jou.ptnum = a.ptnum AND crs.chgsplnum = 0 AND jou.trandate between (today() - 120) and (today()-91) ) as "91 - 120",
(SELECT SUM(crsplamt) FROM sos.jcrsplits crs join sos.journal jou on crs.jnum=jou.jnum WHERE jou.ptnum = a.ptnum AND crs.chgsplnum = 0 AND jou.trandate < (today() - 120) ) as "Over 120"
FROM
sos.jcrsplits a
JOIN sos.patients b ON a.ptnum = b.ptnum
WHERE
chgsplnum = 0
AND flag = 0
AND dischargedate IS NULL
GROUP BY
lastname,firstname,"id","current","31 - 60","61 - 90","91 - 120", "Over 120"
ORDER BY
lastname,firstname,"id"
