Category: G4 Queries

Active Patients with Last Date of Service and Next Appointment

Posted on | by SOS Blog | Leave a Comment on Active Patients with Last Date of Service and Next Appointment

Here is a very simple query that lists all active patients (flag = 0 and no discharge date) for the main data set (licnum = 101), using the SOS function LASTCHARGEDATE to return the most recent date of service with a non-zero fee amount.

SELECT
  a.lastname AS "Last Name",
  a.firstname AS "First Name",
  a.id AS "Account ID",
  a.dob AS "DOB",
  sos.LASTCHARGEDATE(a.ptnum) AS "Last Service"
FROM
  sos.patients a
WHERE
  a.licnum = 101
  AND a.flag = 0 
  AND a.dischargedate IS NULL
ORDER BY 
  a.lastname,a.firstname,a.id

Here is a variation in which we add another column containing a subquery that returns the patient’s next appointment as well…

SELECT
  a.lastname AS "Last Name",
  a.firstname AS "First Name",
  a.id AS "Account ID",
  a.dob AS "DOB",
  sos.LASTCHARGEDATE(a.ptnum) AS "Last Service",
  (SELECT MIN(apptdate) FROM sos.rv_appts WHERE ptnum = a.ptnum AND apptdate > today()) AS "Next Appt"
FROM
  sos.patients a
WHERE
  a.licnum = 101
  AND a.flag = 0 
  AND a.dischargedate IS NULL
ORDER BY 
  a.lastname,a.firstname,a.id

And finally, adding a filter to show only active patients who have not been seen in at least 180 days…

SELECT
  a.lastname AS "Last Name",
  a.firstname AS "First Name",
  a.id AS "Account ID",
  a.dob AS "DOB",
  sos.LASTCHARGEDATE(a.ptnum) AS "Last Service",
  (SELECT MIN(apptdate) FROM sos.rv_appts WHERE ptnum = a.ptnum AND apptdate > today()) AS "Next Appt"
FROM
  sos.patients a
WHERE
  a.licnum = 101
  AND a.flag = 0 
  AND a.dischargedate IS NULL
  AND "Last Service" <= (TODAY() - 180)
ORDER BY 
  a.lastname,a.firstname,a.id

Back-dated Charge Splits and Credit Splits

Posted on | by SOS Blog | Leave a Comment on Back-dated Charge Splits and Credit Splits

From time to time we at SOS hear from customers who are concerned that aging reports with the same aging base date, but printed on different dates, do not match. The differences in these reports is often because users enter additional charges and/or apply credits, but back date the charge-splits or credit applied dates. The two simple queries below provide you with a way of identifying these entries. In both of these examples, the base date is Dec 31, 2013. Replace that date with your own aging date.

Charge split dates…

SELECT
  *
FROM
  sos.rv_charges a
WHERE
  a.chgspldate <= '2013-12-31'
  AND a.postdate > '2013-12-31'

Credit split applied dates…

SELECT
  *
FROM
  sos.rv_creditsplits a
WHERE
  a.dateapplied <= '2013-12-31'
  AND a.postdate > '2013-12-31'

List Secondary Insurance Policies by Patient

Posted on | by SOS Blog | Leave a Comment on List Secondary Insurance Policies by Patient

I need a list of patients who have secondary insurance, including the secondary policy information (payer name, subscriber number, and group number).

The following solution takes into consideration that we could have a mix of active and inactive policies, with inactive policies above active ones in the list. We therefore have to filter out the policies that are not active on the date of the query, and re-assign corrected position numbers to the remaining policies. We can filter out the inactive ones using a simple WHERE condition, but renumber the rest requires use of an advanced RANK expression, including an optional PARTITION parameter. All that is included as a subquery in an outer container query that functions as an ad-hoc view by assigning the subquery an Alias (in this case X). That ad-hoc view is linked to the other normal view (rv_policies) and the patient table in the FROM clause. A condition in the WHERE of the outer query filters out the primary policy, leaving only lower order coverage.

SELECT 
 b.ptnum, b.lastname,b.firstname,b.id, a.payorname,a.insgroup,x.CurrentInsPos
FROM 
  sos.rv_policies a 
  JOIN sos.patients b ON a.ptnum = b.ptnum 
  JOIN (SELECT a.ptnum, a.ptpolnum, RANK() OVER (PARTITION BY a.ptnum ORDER BY a.ptnum,a.inspos) AS "CurrentInsPos"
        FROM sos.ptpolicies a 
        WHERE today() BETWEEN COALESCE(a.active,'1900-01-01') AND COALESCE(a.inactive,'2100-12-31')) X ON a.ptpolnum = x.ptpolnum
WHERE
  b.flag = 0
  AND b.licnum = 101
  AND x.currentinspos > 1
ORDER BY 
  b.lastname, b.firstname, b.id, x.currentinspos

The query above excludes the primary insurance. The version below includes all active policies in the result set, but only for patients who have two or more currently active policies.

SELECT 
  b.ptnum, b.lastname,b.firstname,b.id, 
  RANK() OVER (PARTITION BY a.ptnum ORDER BY a.ptnum,a.inspos) AS "CurrentInsPos",
  a.payorname,a.insgroup
FROM 
  sos.rv_policies a 
  JOIN sos.patients b ON a.ptnum = b.ptnum 
  JOIN (SELECT ptnum,count(*) as "Active Policies"
        FROM sos.ptpolicies a 
        WHERE today() BETWEEN COALESCE(a.active,'1900-01-01') AND COALESCE(a.inactive,'2100-12-31')
        GROUP BY ptnum
        -- HAVING "Active Policies" > 1 
       ) AS X ON a.ptnum = x.ptnum
WHERE
  b.flag = 0
  AND b.licnum = 101
  AND x."Active Policies" > 1
ORDER BY
  b.lastname,b.firstname,b.id,CurrentInsPos

Average Payment by Service and Payer for Period

Posted on | by SOS Blog | Leave a Comment on Average Payment by Service and Payer for Period

I would like to compare payment rates by service codes for our various payers. Specifically, I would like to see the number of services rendered, total payments, and average payments grouped and sorted by service code and payer.

This little query illustrates a bunch of techniques including:

  • IF expression used as a SELECT column
  • Aggregate functions COUNT and SUM
  • CAST a value to a different data type
  • ROUND function
  • GROUP BY ROLLUP( )
SELECT
  (IF pay.payortype = 'I' THEN pay.payorname ELSE 'Private Pay' END IF) AS "Payer",
  chg.srvcode AS "SrvCode",
  COUNT(DISTINCT chg.jnum) AS "SrvCount", 
  SUM(IF COALESCE(cre.credtype,'Adjustment')= 'Adjustment' THEN 0 ELSE cre.crsplamt END IF) AS "TotPaid",
  CAST(ROUND(("TotPaid"/"SrvCount"),2) AS DECIMAL(10,2)) AS "AvgPaid"
FROM
  sos.rv_charges chg
  JOIN sos.ptpayors ptp ON chg.ptpayornum = ptp.ptpayornum
  JOIN sos.payors pay ON ptp.payornum = pay.payornum
  LEFT OUTER JOIN sos.rv_creditsplits cre ON chg.chgsplnum = cre.chgsplnum
WHERE
  chg.trandate BETWEEN '2000-01-01' AND '2000-12-31'
  AND chg.amount > 0
GROUP BY
   ROLLUP("SrvCode","Payer")
ORDER BY
  "SrvCode","Payer"

Transactions with Missing Sort Codes for Period

Posted on | by SOS Blog | Leave a Comment on Transactions with Missing Sort Codes for Period

The following query produces a list of charge entries for which no sort code was selected during data entry. Be sure to modify the date range to the period that you want to examine.

SELECT 
  lastname, firstname, id, jnum, trandate, provcode, srvcode, amount, adduser 
FROM 
  sos.rv_charges
WHERE 
  sortcode IS NULL
  AND trandate BETWEEN '2013-01-01' AND '2013-03-31'
ORDER BY 
  lastname,firstname,id,trandate

Credits are a bit more complicated. If a creditsplit has been applied, and no sort code has been specified for the entire credit entry, some SOS reports will trace back to the charge being paid and report the amount of the creditsplit with the linked charge’s sort code. The following query ignores the linked charge and reports based on the absence of a specified sort code on the credit itself.

SELECT 
  pt.lastname, pt.firstname, pt.id, jou.jnum, jou.trandate, cre.credtype, jou.amount, jou.adduser 
FROM 
  sos.journal jou 
  JOIN sos.jcredits cre
  JOIN sos.patients pt ON jou.ptnum = pt.ptnum
WHERE 
  sortcode IS NULL
  AND trandate BETWEEN '2013-01-01' AND '2013-03-31'
ORDER BY 
  lastname,firstname,id,trandate