Category: G4 Queries

ICD-9 Diagosis Codes In Use and ICD-10 Suggestions from CMS GEMs

Posted on | by SOS Blog | Leave a Comment on ICD-9 Diagosis Codes In Use and ICD-10 Suggestions from CMS GEMs

This query returns a list of diagnoses that are currently in use in Claim Setups in OM and in DSM Profiles in CM. These are the ones that you will have to replace by the new ICD-10 diagnosis codes on or before October of 2014.

The query itself is a main query that lists diagnoses that appear in any of a bunch of fields in OM and CM and a subquery used in the WHERE clause. The subquery contains a simple query for the dxnum in each possible location, and all the simple queries are UNIONed together to get a single list.

The second query below is a revision of the first, adding a column for the ICD-10 suggestion, according to the CMS GEMs crosswalk.

SELECT
  dxcode, dxtype, dxdesc
FROM
  sos.dx
WHERE
  dxnum IN
  (
  SELECT dx1 FROM sos.ptcsu
  UNION
  SELECT dx2 FROM sos.ptcsu
  UNION
  SELECT dx3 FROM sos.ptcsu
  UNION
  SELECT dx4 FROM sos.ptcsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT fl67i FROM sos.ptubsu
  UNION
  SELECT fl67i FROM sos.ptubsu
  UNION
  SELECT fl67i FROM sos.ptubsu
  UNION
  SELECT fl67i FROM sos.ptubsu
  UNION
  SELECT fl67i FROM sos.ptubsu
  UNION
  SELECT fl67i FROM sos.ptubsu
  UNION
  SELECT fl67i FROM sos.ptubsu
  UNION
  SELECT fl67i FROM sos.ptubsu
  UNION
  SELECT fl67i FROM sos.ptubsu  
  UNION
  SELECT dxnum FROM  sos.v_ptdsmlist WHERE dxnum <> 0
  )
ORDER BY dxcode

The following variation adds a JOIN to the CMS GEMS table to show the suggested ICD-10 code for each of the current ICD-9 codes in use in your data.

SELECT
  a.dxcode, a.dxtype, a.dxdesc, b.icd10 AS "GEMS suggested ICD10", c.longdesc AS "ICD10 Desc" 
FROM
  sos.dx a
  LEFT OUTER JOIN sos.v_ICD9to10 b ON a.dxcode = b.icd9
  LEFT OUTER JOIN sos.dx c ON b.icd10 = c.dxcode
WHERE
  a.dxnum IN
  (
  SELECT dx1 FROM sos.ptcsu
  UNION
  SELECT dx2 FROM sos.ptcsu
  UNION
  SELECT dx3 FROM sos.ptcsu
  UNION
  SELECT dx4 FROM sos.ptcsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu   
  UNION   
  SELECT box67 FROM sos.ptubsu   
  UNION   
  SELECT box67 FROM sos.ptubsu
  UNION
  SELECT box67 FROM sos.ptubsu 
  UNION 
  SELECT box67 FROM sos.ptubsu 
  UNION 
  SELECT fl67i FROM sos.ptubsu 
  UNION 
  SELECT fl67i FROM sos.ptubsu 
  UNION 
  SELECT fl67i FROM sos.ptubsu 
  UNION 
  SELECT fl67i FROM sos.ptubsu 
  UNION 
  SELECT fl67i FROM sos.ptubsu 
  UNION 
  SELECT fl67i FROM sos.ptubsu 
  UNION 
  SELECT fl67i FROM sos.ptubsu 
  UNION 
  SELECT fl67i FROM sos.ptubsu 
  UNION 
  SELECT fl67i FROM sos.ptubsu 
  UNION 
  SELECT dxnum FROM  sos.v_ptdsmlist 
  WHERE dxnum <> 0   ) ORDER BY a.dxcode

Discharged Patients With Rendering Provider

Posted on | by SOS Blog | Leave a Comment on Discharged Patients With Rendering Provider

This query delivers a list of discharged patients with rendering provider and discharge date. If the patient was seen by more than one provider, he or she will appear multiple times in the list:

SELECT
  DISTINCT pt.lastname, pt.firstname, pt.id, pr.provcode, pt.dischargedate
FROM
  sos.providers pr
  JOIN sos.jcharges jc ON jc.providernum = pr.providernum
  JOIN sos.patients pt ON jc.ptnum = pt.ptnum
WHERE
  pt.dischargedate IS NOT NULL
ORDER BY
  pt.lastname, pt.firstname, pt.id, pr.provcode


To sort by provider, rather than patient name, just change the ORDER BY clause:


SELECT
  DISTINCT pt.lastname, pt.firstname, pt.id, pr.provcode, pt.dischargedate
FROM
  sos.providers pr
  JOIN sos.jcharges jc ON jc.providernum = pr.providernum
  JOIN sos.patients pt ON jc.ptnum = pt.ptnum
WHERE
  pt.dischargedate IS NOT NULL
ORDER BY
  pr.provcode, pt.lastname, pt.firstname, pt.id

	


	





	

			

	

		
Detailed List of Charge Balances by Payor On Specified Date
		

			Posted on  |  by SOS Blog |  Leave a Comment on Detailed List of Charge Balances by Payor On Specified Date		

			

	

		
We have an audit coming up and would like to have a detailed listing, down to the charge split level, of unpaid charges on the last day of our fiscal year. It would be most helpful to have the list sorted by payor and date.



In the following query, you must replace the date in two places with the last date of your fiscal period. Remember that this is a listing of charge splits, so there can be several lines for the same service item, even for a single payor. The sort order keeps splits for the same payor and service together.


SELECT
  //patient
  a.licnum AS "DataSet",
  a.id AS "AccountID",
  a.lastname AS "LastName",
  a.firstname AS "FirstName",
  //service
  a.jnum AS "TransactionNum",
  a.trandate AS "SrvDate",
  a.provcode AS "ProvCode",
  a.srvcode AS "SrvCode",
  a.amount AS "TotalFee",
  a.balance AS "TotalBalance",
  //payor
  TRIM(c.payorname + ' ' +c.firstname) AS "Payor",
  a.chgsplamt AS "AmtChargedPayor",
  (SELECT COALESCE(SUM(crsplamt),0)
       FROM sos.jcrsplits
       WHERE chgsplnum = a.chgsplnum AND dateapplied <= '2008-09-30') AS "PayorCreditsApplied",
  (a.chgsplamt - "PayorCreditsApplied") AS "PayorBalance",
  //billing dates
  COALESCE(STRING(a.firstbilled),'') AS "DateFirstBilled",
  COALESCE(STRING(a.lastbilled),'') AS "DateMostRecentlyBilled"
FROM
  sos.rv_charges a
  JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
  JOIN sos.payors c ON b.payornum = c.payornum
WHERE
   "AmtChargedPayor" <> 0
  AND "PayorBalance" <> 0
  AND  (a.paiddate IS NULL OR a.paiddate > '2008-09-30')
ORDER BY
  "Payor","SrvDate","TransactionNum"


The following version combines splits if a single payor has more than one split on the same service charge entry.


SELECT
  //patient
  a.licnum AS "DataSet",
  a.id AS "AccountID",
  a.lastname AS "LastName",
  a.firstname AS "FirstName",
  //service
  a.jnum AS "TransactionNum",
  a.trandate AS "SrvDate",
  a.provcode AS "ProvCode",
  a.srvcode AS "SrvCode",
  a.amount AS "TotalFee",
  a.balance AS "TotalBalance",
  //payor
  TRIM(c.payorname + ' ' +c.firstname) AS "Payor",
  SUM(a.chgsplamt) AS "AmtChargedPayor",
  SUM((SELECT COALESCE(SUM(crsplamt),0)
          FROM sos.jcrsplits
          WHERE chgsplnum = a.chgsplnum AND dateapplied <= '2008-09-30')) AS "PayorCreditsApplied",
  ("AmtChargedPayor" - "PayorCreditsApplied") AS "PayorBalance",
  //billing dates
  MIN(COALESCE(STRING(a.firstbilled),'')) AS "DateFirstBilled",
  MAX(COALESCE(STRING(a.lastbilled),'')) AS "DateMostRecentlyBilled"
FROM
  sos.rv_charges a
  JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
  JOIN sos.payors c ON b.payornum = c.payornum
WHERE
  (a.paiddate IS NULL OR a.paiddate > '2008-09-30')
GROUP BY
  "DataSet","Payor","Lastname","Firstname","ID","SrvDate","ProvCode",
  "SrvCode","TotalFee","TotalBalance","TransactionNum"
HAVING
  "AmtChargedPayor" <> 0
  AND "PayorBalance" <> 0
ORDER BY
  "DataSet","Payor","SrvDate","TransactionNum"



	


	





	

			

	

		
Diagnosis Queries
		

			Posted on  |  by SOS Blog |  Leave a Comment on Diagnosis Queries		

			

	

		
I want to do some queries that include the patients’ diagnoses. Where do I find that information?



That seems like such a simple question, but the answer is not so simple. There are several ways to get to the patient diagnoses. The easiest is if you use the RV_PATIENTS view instead of the PATIENTS table in your query. In that case, you will find integer values in RV_PATIENTS columns named DX1, DX2, DX3, and DX4. Those numbers are NOT the diagnoses, but rather they are foreign keys that provide links back to the DX table. Therefore, if you wanted the codes for those diagnoses, here is how you would query them:


SELECT
  a.lastname, a.firstname, a.id,
  b.dxcode AS "DxCode1",
  c.dxcode AS "DxCode2",
  d.dxcode AS "DxCode3",
  e.dxcode AS "DxCode4"
FROM
  sos.rv_patients a
  LEFT OUTER JOIN sos.dx b ON a.dx1 = b.dxnum
  LEFT OUTER JOIN sos.dx c ON a.dx2 = c.dxnum
  LEFT OUTER JOIN sos.dx d ON a.dx3 = d.dxnum
  LEFT OUTER JOIN sos.dx e ON a.dx4 = e.dxnum


The technical details are that the dx links are actually in the PTCSU table. The patients table is related to ptcsu, which in turn is related (4 times) to the dx table. In OM Pro, there can be many CSU's for each patient, so there can also be many different sets of diagnoses for a single patient. The query above assumes that you want the default CSU's diagnoses. In reality, however, the relationship between patient and diagnosis is more complex, as shown in this query, which will generate more than one row for any patient with more than one Claim Setup. (Note that this issue does not pertain to the standard version of OM, which has only one claim setup per patient.):


SELECT
  a.*,
  c.dxcode AS "dxcode1",
  d.dxcode AS "dxcode2",
  e.dxcode AS "dxcode3",
  f.dxcode AS "dxcode4"
FROM
  sos.patients a
  LEFT OUTER JOIN sos.ptcsu b ON a.ptnum = b.ptnum
  LEFT OUTER JOIN sos.dx c ON b.dx1 = c.dxnum
  LEFT OUTER JOIN sos.dx d ON b.dx2 = d.dxnum
  LEFT OUTER JOIN sos.dx e ON b.dx3 = e.dxnum
  LEFT OUTER JOIN sos.dx f ON b.dx4 = e.dxnum


You can limit to the default Claim setup by adding a condition "typeflag = D", or you could use a subquery to get the most recently added claim setup, using MAX(ptcsunum) or MAX(adddate):


SELECT
  a.*,
  c.dxcode AS "dxcode1",
  d.dxcode AS "dxcode2",
  e.dxcode AS "dxcode3",
  f.dxcode AS "dxcode4"
FROM
  sos.patients a
  LEFT OUTER JOIN sos.ptcsu b ON a.ptnum = b.ptnum
  LEFT OUTER JOIN sos.dx c ON b.dx1 = c.dxnum
  LEFT OUTER JOIN sos.dx d ON b.dx2 = d.dxnum
  LEFT OUTER JOIN sos.dx e ON b.dx3 = e.dxnum
  LEFT OUTER JOIN sos.dx f ON b.dx4 = e.dxnum  
WHERE
  b.ptcsunum = (SELECT MAX(ptcsunum) FROM sos.ptcsu WHERE ptnum = a.ptnum)

	


	





	

			

	

		
Credits Without Sort Codes By Primary Provider
		

			Posted on  |  by SOS Blog |  Leave a Comment on Credits Without Sort Codes By Primary Provider		

			

	

		
We have discovered that some credit entries were entered without specifying a Sort Code, which we need for reporting purposes. I need a total amount of such credits, by provider.


SELECT
 r.provcode,
 r.provlname,
 SUM(amount) AS "TotalCredits"
FROM
 sos.journal j,
 sos.patients p,
 sos.providers r
WHERE
 j.ptnum=p.ptnum AND
 p.providernum=r.providernum AND
 j.trandate BETWEEN '2000-03-01' AND '2016-03-15' AND
 j.sortcode IS NULL
GROUP BY
 r.provcode, r.provlname