To assure the accuracy of the aging figures in your aging reports, statements, and other reports containing aging information, a good practice is to force a recalculation prior to running the reports:
We would like to calculate the total fees during a period and the amount paid toward those fees, grouped by Facility and by Provider.
The following query uses the “ROLLUP” option to include subtotals for all providers and all facilities, as well as a grand total. In the result set interpret NULL as “ALL”. For example, if you see NULL in the Provider column, read that as “All providers”.
SELECT
COALESCE(g.shorthand,'None') AS "Facility",
f.provcode AS "Primary Provider",
SUM(b.amount) AS "Service Amount",
SUM(coalesce(i.crsplamt,0)) AS "Total Credit"
FROM
sos.patients a
JOIN sos.journal b ON a.ptnum=b.ptnum
JOIN sos.jcharges c ON b.jnum=c.jnum
JOIN sos.jchgsplits h ON h.jnum=c.jnum
LEFT OUTER JOIN sos.jcrsplits i ON h.chgsplnum=i.chgsplnum
LEFT OUTER JOIN sos.jcredits j ON i.jnum=j.jnum
JOIN sos.ptcsu d ON c.ptcsunum=d.ptcsunum
LEFT OUTER JOIN sos.providers f ON a.providernum=f.providernum
LEFT OUTER JOIN sos.facilities g ON d.facilitynum=g.facilitynum
WHERE
b.trandate BETWEEN '2008-01-01' AND '2015-02-20'
AND a.licnum=101
AND (credtype <> 'adjustment' OR credtype is NULL)
GROUP BY
ROLLUP("Facility", f.provcode)
ORDER BY
"Facility", f.provcode
Our auditors have requested a report of outstanding balances per client, as of Dec 31, 2011, separated by liability (Medicare, Medicaid, private insurance and self-pay). Insurance type is specified by “Coverage Type” on the Additional tab of the Insurance Carrie/Plan. We want to report patients in either the Active or Inactive lists, as long as they have a balance of some kind at the end of 2011.
Because we are looking for balances on a previous date, we have to tally up all the charges and payments on or before that date, then subtract the latter from the former to find the balance. To do that will require a bunch of embedded subqueries as you can see in the query below. If you want to run the query for a different date, be sure to replace ALL the instances of ‘2011-12-31’!
The output specified in this example will go to an HTML format file called AUDIT2011.HTML located in the C:\SOS folder. You can output the results to screen for inspection before outputting to file by inserting two dashes or slashes at the beginning of the last line. Just remove those characters when you are ready to generate the file. The benefit of outputting in HTML format is that you can view the results in a web browser, or open the file with Excel for additional massaging.
SELECT
a.lastname, a.firstname, a.id AS "Account ID",
-- Medicare
COALESCE((SELECT SUM(chgsplamt)
FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.carriers car ON ptp.payornum = car.payornum
WHERE chs.ptnum = a.ptnum AND car.coverage = 'C' AND chs.chgspldate <= '2011-12-31'),0) AS "Medicare Charges",
COALESCE((SELECT SUM(crsplamt)
FROM sos.jcrsplits crs
JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum
JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
JOIN sos.carriers car ON ptp.payornum = car.payornum
JOIN sos.jcredits cre ON crs.jnum = cre.jnum
JOIN sos.journal jou ON cre.jnum = jou.jnum
WHERE chs.ptnum = a.ptnum
AND car.coverage = 'C'
AND chs.chgspldate <= '2011-12-31'
AND jou.trandate <= '2011-12-31') ,0) AS "Medicare Credits",
("Medicare Charges" - "Medicare Credits") AS "Medicare Balance",
-- Medicaid
COALESCE((SELECT SUM(chgsplamt)
FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.carriers car ON ptp.payornum = car.payornum
WHERE chs.ptnum = a.ptnum AND car.coverage = 'D' AND chs.chgspldate <= '2011-12-31'),0) AS "Medicaid Charges",
COALESCE((SELECT SUM(crsplamt)
FROM sos.jcrsplits crs
JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum
JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
JOIN sos.carriers car ON ptp.payornum = car.payornum
JOIN sos.jcredits cre ON crs.jnum = cre.jnum
JOIN sos.journal jou ON cre.jnum = jou.jnum
WHERE chs.ptnum = a.ptnum
AND car.coverage = 'D'
AND chs.chgspldate <= '2011-12-31'
AND jou.trandate <= '2011-12-31' ) ,0) AS "Medicaid Credits",
("Medicaid Charges" - "Medicaid Credits") AS "Medicaid Balance",
-- Private Ins
COALESCE((SELECT SUM(chgsplamt)
FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.carriers car ON ptp.payornum = car.payornum
WHERE chs.ptnum = a.ptnum AND car.coverage NOT IN ('C','D') AND chs.chgspldate <= '2011-12-31'),0) AS "Private Ins Charges",
COALESCE((SELECT SUM(crsplamt)
FROM sos.jcrsplits crs
JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum
JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
JOIN sos.carriers car ON ptp.payornum = car.payornum
JOIN sos.jcredits cre ON crs.jnum = cre.jnum
JOIN sos.journal jou ON cre.jnum = jou.jnum
WHERE chs.ptnum = a.ptnum
AND car.coverage NOT IN ('C','D')
AND chs.chgspldate <= '2011-12-31'
AND jou.trandate <= '2011-12-31' ) ,0) AS "Private Ins Credits",
("Private Ins Charges" - "Private Ins Credits") AS "Private Ins Balance",
-- Self-Pay
COALESCE((SELECT SUM(chgsplamt)
FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.payors pay ON ptp.payornum = pay.payornum
WHERE chs.ptnum = a.ptnum AND pay.payortype IN ('P','O') AND chs.chgspldate <= '2011-12-31'),0) AS "Self-Pay Charges",
COALESCE((SELECT SUM(crsplamt)
FROM sos.jcrsplits crs
JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum
JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
JOIN sos.payors pay ON ptp.payornum = pay.payornum
JOIN sos.jcredits cre ON crs.jnum = cre.jnum
JOIN sos.journal jou ON cre.jnum = jou.jnum
WHERE chs.ptnum = a.ptnum
AND pay.payortype IN ('P','O')
AND chs.chgspldate <= '2011-12-31'
AND jou.trandate <= '2011-12-31' ) ,0) AS "Self-Pay Credits",
("Self-Pay Charges" - "Self-Pay Credits") AS "Self-Pay Balance"
FROM
sos.patients a
WHERE
"Medicare Balance" <> 0
OR "Medicaid Balance" <> 0
OR "Private Ins Balance" <> 0
OR "Self-Pay Balance" <> 0
ORDER BY
a.lastname, a.firstname, a.id
We looking for a report that will list all sessions that have been billed for each insurance company during a month. For example, the report would list out many sessions have been billed for MVP insurance for the month of October. Please let me know if this is possible.
In the following query, the output is directed to an HTML file that can be viewed in a web browser, or loaded in Excel for additional manipulation.
SELECT DISTINCT
pay.payorname,
pt.lastname, pt.firstname, pt.id,
jou.trandate, srv.srvcode, prv.provcode, jou.amount
FROM
sos.journal jou
JOIN sos.jcharges chg ON jou.jnum = chg.jnum
JOIN sos.jchgsplits chs ON chg.jnum = chs.jnum
JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
JOIN sos.payors pay ON ptp.payornum = pay.payornum
JOIN sos.patients pt ON jou.ptnum = pt.ptnum
JOIN sos.services srv ON chg.servicenum = srv.servicenum
JOIN sos.providers prv ON chg.providernum = prv.providernum
WHERE
pay.payortype = 'I'
AND trandate BETWEEN '2011-11-01' AND '2011-11-30'
ORDER BY
pay.payorname, pt.lastname, pt.firstname, pt.id, jou.trandate ;
OUTPUT TO c:\sos\insforperiod.html FORMAT HTML
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"
Simple Software Designs, LLC, dba Synergistic Office Solutions