Category: Payment Analysis

Balance Breakdown By Patient On Specified Date

Posted on | by SOS Blog | Leave a Comment on 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

 

Aged Unapplied Credits

Posted on | by SOS Blog | Leave a Comment on Aged Unapplied Credits

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"

Referral Source, Patient Count and Charges By Month

Posted on | by SOS Blog | Leave a Comment on Referral Source, Patient Count and Charges By Month

The unique thing about this query is that it creates a grid with the months across the top. For each
Referral Source/Year there is a single row.

SELECT 
   b.refname as "Ref’d By", 
   YEAR(c.trandate) as "Year",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 1 THEN c.ptnum ENDIF)) as "Jan Count",
   SUM(IF MONTH(c.trandate) = 1 THEN c.amount ELSE 0 ENDIF) as "Jan Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 2 THEN c.ptnum ENDIF)) as "Feb Count",
   SUM(IF MONTH(c.trandate) = 2 THEN c.amount ELSE 0 ENDIF) as "Feb Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 3 THEN c.ptnum ENDIF)) as "Mar Count",
   SUM(IF MONTH(c.trandate) = 3 THEN c.amount ELSE 0 ENDIF) as "Mar Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 4 THEN c.ptnum ENDIF)) as "Apr Count",
   SUM(IF MONTH(c.trandate) = 4 THEN c.amount ELSE 0 ENDIF) as "Apr Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 5 THEN c.ptnum ENDIF)) as "May Count",
   SUM(IF MONTH(c.trandate) = 5 THEN c.amount ELSE 0 ENDIF) as "May Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 6 THEN c.ptnum ENDIF)) as "Jun Count",
   SUM(IF MONTH(c.trandate) = 6 THEN c.amount ELSE 0 ENDIF) as "Jun Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 7 THEN c.ptnum ENDIF)) as "Jul Count",
   SUM(IF MONTH(c.trandate) = 7 THEN c.amount ELSE 0 ENDIF) as "Jul Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 8 THEN c.ptnum ENDIF)) as "Aug Count",
   SUM(IF MONTH(c.trandate) = 8 THEN c.amount ELSE 0 ENDIF) as "Aug Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 9 THEN c.ptnum ENDIF)) as "Sep Count",
   SUM(IF MONTH(c.trandate) = 9 THEN c.amount ELSE 0 ENDIF) as "Sep Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 10 THEN c.ptnum ENDIF)) as "Oct Count",
   SUM(IF MONTH(c.trandate) = 10 THEN c.amount ELSE 0 ENDIF) as "Oct Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 11 THEN c.ptnum ENDIF)) as "Nov Count",
   SUM(IF MONTH(c.trandate) = 11 THEN c.amount ELSE 0 ENDIF) as "Nov Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 12 THEN c.ptnum ENDIF)) as "Dec Count",
   SUM(IF MONTH(c.trandate) = 12 THEN c.amount ELSE 0 ENDIF) as "Dec Chgs",
   COUNT(DISTINCT a.ptnum) as "# Count", SUM(c.amount) as "TotChgs"
FROM 
   sos.patients a
   JOIN sos.refsrcs b on a.refsrcnum = b.refsrcnum
   JOIN sos.journal c on a.ptnum = c.ptnum
WHERE 
   c.trantype = 'S' 
   AND c.trandate > '2000-12-31'
GROUP BY 
   b.refname, 
   YEAR(c.trandate)
ORDER BY 
   b.refname, 
   YEAR(c.trandate)

Payment Latency for Specified Payor Number

Posted on | by SOS Blog | Leave a Comment on Payment Latency for Specified Payor Number 
In order to determine how many days it takes a payor to pay your claims, you
can examine firstbilled and paiddate values in JChgSplits. The following
query will tell you these dates and compute the number of days between them.
Just change the payor number (get the payor number from the second column in
Lookups > Ins Carriers/Plans) and the desired service date range in the
WHERE clause.
SELECT 
   a.jnum,c.amount AS "Fee",
   a.chgsplamt AS "SplitToPayor",
   a.firstbilled,
   a.paiddate,
   datediff(day,a.firstbilled,a.paiddate) AS "PaymentDays"
FROM 
   sos.jchgsplits a
   JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
   JOIN sos.journal c ON a.jnum = c.jnum
WHERE 
   b.payornum = 101
   AND c.trandate BETWEEN '2004-1-1' AND '2004-12-31'

Collections by Service for Period

Posted on | by SOS Blog | Leave a Comment on Collections by Service for Period 
“I have a need for a report that would breakdown monies collected for each service code over a specified time period.”


This one selects based on the service date.


SELECT 
   srvcode, 
   sum(crsplamt) AS Payments
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
   AND Srv_Date BETWEEN '2001-01-01' and '2001-12-31'
GROUP BY 
   srvcode
ORDER BY 
   srvcode



This one also selects based on the service date, but adds a count of the number of services and the average payment per service.


SELECT 
   srvcode, 
   SUM(crsplamt) AS "Total Payments",
   COUNT(DISTINCT srv_jnum) AS "Count",
   CAST(ROUND("Total Payments"/"Count",2) AS DECIMAL(12,2)) AS "Avg Payment per Svc"
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
   AND Srv_Date BETWEEN '2001-01-01' AND '2001-12-31'
GROUP BY 
   srvcode
ORDER BY 
   srvcode


This one selects based on the date payment was applied.


SELECT 
   srvcode, 
   sum(crsplamt) AS Payments
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
   AND DateApplied BETWEEN '2001-01-01' and '2001-12-31'
GROUP BY 
   srvcode
ORDER BY 
   srvcode


This one selects based on the date payment was received.


SELECT 
   srvcode, 
   sum(crsplamt) AS Payments
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
AND 
   Cre_Date BETWEEN '2001-01-01' and '2001-12-31'
GROUP BY
   srvcode
ORDER BY 
   srvcode