Category: Queries – Patients

E-Mail Addresses For Active Patients

Posted on | by SOS Blog | Leave a Comment on E-Mail Addresses For Active Patients

I need a list of active patients with their email addresses. Only list those with emails, and sort in order by name.

SELECT DISTINCT
a.lastname, a.firstname, b.email
FROM
sos.patients a JOIN payors b ON a.payornum = b.payornum
WHERE
flag = 0 AND dischargedate IS NULL AND email > ''
ORDER BY
a.lastname, a.firstname, b.email

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

	


	





	

			

	

		
Count Of Patients For An Age Range
		

			Posted on  |  by SOS Blog |  Leave a Comment on Count Of Patients For An Age Range		

			

	

		
The following will give you a count of patients with activity in calendary year 2006 who were between 13 and 19 on the last day of 2006.


SELECT
  COUNT(DISTINCT a.ptnum) AS "numpts"
FROM
  sos.journal a
  JOIN sos.patients b ON a.ptnum = b.ptnum
WHERE
  trantype = 's'
  AND amount > 0
  AND trandate BETWEEN '2006-01-01' AND '2006-12-31'
  AND sos.AgeInYears(dob,'2006-12-31') BETWEEN 13 AND 19

	


	





	

			

	

		
Count of Patients by Provider with and without Medicaid
		

			Posted on  |  by SOS Blog |  Leave a Comment on Count of Patients by Provider with and without Medicaid		

			

	

		
I need to know by provider how many active patients they have and how many have Medicaid coverage.




This query shows the combined use a an IF expression that evaluates based on a subquery that returns a count. In this case, if there is any Medicaid coverage (carriers.coverage = ‘D’) then the expression returns “Yes”, otherwise (no ‘caid coverage) it returns “No”.


SELECT
(a.provfname + ' '+a.provlname) AS "Provider",
(IF (SELECT COUNT(*)
    FROM sos.ptpayors s1 JOIN sos.payors s2 ON s1.payornum = s2.payornum JOIN sos.carriers s3 ON s2.payornum = s3.payornum
    WHERE s1.ptnum = a.ptnum AND s3.coverage = 'D') > 0
THEN 'YES'
ELSE 'NO'
END IF)
AS "Medicaid",
COUNT(DISTINCT a.ptnum) AS "Pt Count"
FROM
sos.rv_charges a
WHERE
a.trandate BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
"Provider", "Medicaid"
ORDER BY
"Provider", "Medicaid"

	


	





	

			

	

		
Count Of Patients By Insurance Plan
		

			Posted on  |  by SOS Blog |  Leave a Comment on Count Of Patients By Insurance Plan		

			

	

		
The SOS report “Patients by Insurance Carrier” lists every active patient. Can that report be trimmed to just giving the carrier and the number of active patients within each carrier, perhaps also with a grand total of active patients? Knowing the exact amount of patients we serve is helpful when negotiating with carriers about rates, etc.



The following query gives a count by Plan and (primary) Provider. For the count across providers, remove line 3, line 9, and the comma and d.provcode from the GROUP BY and ORDER BY clauses. Better yet, look at the second version of the query below, featuring a ROLLUP that gives you all the totals and subtotals without the need to edit.


SELECT
  c.payorname AS "Ins Plan",
  d.provcode,
  count(a.ptnum) AS "N"
FROM
  sos.patients a
  JOIN sos.ptpayors b ON a.ptnum = b.ptnum
  JOIN sos.payors c ON b.payornum = c.payornum
  JOIN sos.providers d ON a.providernum = d.providernum
WHERE
  a.flag = 0
  AND a.dischargedate IS NULL
  AND c.payortype = 'I'
GROUP BY
  c.payorname, d.provcode
ORDER BY
  c.payorname, d.provcode


The version below adds the ROLLUP operator to the GROUP BY. That creates a result set that includes NULL in various cells. Read NULL as “ALL.” Therefore if you see NULL in the ProvCode column, it means that this count is for all providers. There is also a row with NULL in both the Plan and Provider columns, meaning all plans and all providers, which in this case is the number of patients in the result set.


SELECT
  c.payorname AS "Ins Plan",
  d.provcode,
  count(a.ptnum) AS "N"
FROM
  sos.patients a
  JOIN sos.ptpayors b ON a.ptnum = b.ptnum
  JOIN sos.payors c ON b.payornum = c.payornum
  JOIN sos.providers d ON a.providernum = d.providernum
WHERE
  a.flag = 0
  AND a.dischargedate IS NULL
  AND c.payortype = 'I'
GROUP BY
  ROLLUP(c.payorname, d.provcode)
ORDER BY
  c.payorname, d.provcode