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 All Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SELECT lastname,firstname,"id", SUM(crsplamt) AS "Total Unapplied", (SELECT SUM(crsplamt) FROM jcrsplits crs JOIN 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 jcrsplits crs JOIN 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 jcrsplits crs JOIN 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 jcrsplits crs JOIN 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 jcrsplits crs JOIN 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 jcrsplits a JOIN 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" |