Balance Breakdown By Patient On Specified Date

 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

 

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.