Category: Management

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

	


	





	

			

	

		
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)

	


	





	

			

	

		
Count of Referrals by Primary Provider and Referral Source, for Intake Date Range
		

			Posted on  |  by SOS Blog |  2 Comments on Count of Referrals by Primary Provider and Referral Source, for Intake Date Range		

			

	

		
Show number of referrals by referral source, sorted by provider, for a specified intake date range.



This query illustrates the use of an IF…ELSE…ENDIF expression in the SELECT list.


 


SELECT
  COALESCE(c.provcode,'No Pri Prov') AS "Primary Provider",
  IF a.refsrcnum IS NULL
    THEN 'No Ref'
    ELSE TRIM(b.Refname + ' '+b.firstname)
  ENDIF AS "Source Name",
  COALESCE(b.reftype,'') AS "Ref Type",
  COUNT(*) AS "Referrals"
FROM
  sos.patients a
  LEFT OUTER JOIN sos.refsrcs b ON a.refsrcnum = b.refsrcnum
  LEFT OUTER JOIN sos.providers c ON a.providernum = c.providernum
WHERE
  a.intakedate BETWEEN '2000-01-01' AND '2008-08-20'
  AND a.licnum = 101
GROUP BY "Primary Provider","Source Name", "Ref Type"
ORDER BY "Primary Provider","Source Name"


It is also possible to report the data by Rendering Provider, but because a single patient may be seen by two or more (rendering) providers, the grand total will likely be greater than the number of patients in the intake date range. Here is that variation of the query:


SELECT
  DISTINCT COALESCE(c.provcode,'No Pri Prov') AS "Rendering Provider",
  IF a.refsrcnum IS NULL
    THEN 'No Ref'
    ELSE TRIM(b.Refname + ' '+b.firstname)
  ENDIF AS "Source Name",
  COALESCE(b.reftype,'') AS "Ref Type",
  COUNT(*) AS "Referrals"
FROM
  sos.patients a
  LEFT OUTER JOIN sos.refsrcs b ON a.refsrcnum = b.refsrcnum
  LEFT OUTER JOIN sos.jcharges d ON a.ptnum = d.ptnum 
  LEFT OUTER JOIN sos.providers c ON d.providernum = c.providernum
WHERE
  a.intakedate BETWEEN '2000-01-01' AND '2008-08-20'
  AND a.licnum = 101
GROUP BY "Rendering Provider","Source Name", "Ref Type"
ORDER BY "Rendering Provider","Source Name"

	


	





	

			

	

		
Credits by Sort Code
		

			Posted on  |  by SOS Blog |  Leave a Comment on Credits by Sort Code		

			

	

		
In SOS SortCode values can be assigned to charge entries, credit entries, or both. As a result, there are a couple of ways to group and sort credits by SortCode. The following queries provide totals by giving priority to either the SortCode in the credit entry, or to the SortCode entered in the charge to which the credit split is applied. There is also the possiblity that a payment credit may have been entered, but not assigned as yet to a charge, as in the case of a pre-payment.


In the following query, the sortcode entered directly on the credit is represented by lu1.lucode. The SortCode on the charge to which it is applied (if it has been applied) is lu2.lucode. If neither value is available for a particular credit split, then it will be assigned the value ‘Unspecified’. The credit/charge priority is easily reversed by simply reversing the order of the lu1.sortcode and lu2.sortcode fields in the COALESCE function in line 2. Remember that the COALESCE function simply returns the value of the first parameter that is not NULL. In other words, if the first sortcode specified was not entered by the user, then it will check to see if other one was. If neither one is present, then it returns the third parameter, which in this case is the string “Unspecified”.


SELECT
COALESCE(lu1.lucode,lu2.lucode,'Unspecified') AS "SortCode",
SUM (c.crsplamt) AS "Total Credit"
FROM
sos.journal a
JOIN sos.jcredits b ON a.jnum = b.jnum
JOIN sos.jcrsplits c ON b.jnum = c.jnum
LEFT OUTER JOIN sos.lookups lu1 ON a.sortcode = lu1.lunum
LEFT OUTER JOIN sos.jchgsplits d ON c.chgsplnum = d.chgsplnum
LEFT OUTER JOIN sos.journal e ON d.jnum = e.jnum
LEFT OUTER JOIN sos.lookups lu2 ON e.sortcode = lu2.lunum
WHERE a.trandate BETWEEN '2000-01-01' AND '2011-12-31'
GROUP BY "SortCode"


 


 

	


	





	

			

	

		
CPT Code Count for Period and Payor
		

			Posted on  |  by SOS Blog |  Leave a Comment on CPT Code Count for Period and Payor		

			

	

		
Does anybody have a query that will allow me to get a count of CPT code units by insurance plan (BCBS) over a specified time frame?


I specifically need to know which and how many CPT code units were billed to BCBS over the last 6 months.




This query produces a count of services for each CPT code for a specified period. The results are limited to service entries where there is at least one split to the designated payor. In this example, we are selecting any payor that has the word “BLUE” somewhere in the payor name: f.payorname LIKE ‘%BLUE%’ but you could use a specific name: f.payorname = ‘BCBS’ or payor number: f.payornum = 1234 or a group of payor numbers: f.payornum IN (1234, 5678, 123456) by changing the appropriate line in the WHERE clause below.


SELECT
  d.cptcode AS "CPT",
  COUNT(DISTINCT a.jnum) AS "SvcCount"
FROM
  sos.journal a
  JOIN sos.jcharges b ON a.jnum = b.jnum
  JOIN sos.jchgsplits c ON b.jnum = c.jnum
  JOIN sos.services d ON b.servicenum = d.servicenum
  JOIN sos.ptpayors e ON c.ptpayornum = e.ptpayornum
  JOIN sos.payors f ON e.payornum = f.payornum
WHERE
  trandate BETWEEN '2002-01-01' AND '2002-03-31'
  AND f.payorname LIKE '%BLUE%'
GROUP BY
  d.cptcode
ORDER BY
  d.cptcode