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
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 ptpayors s1 JOIN payors s2 ON s1.payornum = s2.payornum
JOIN 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
rv_charges a
WHERE
a.trandate BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
"Provider", "Medicaid"
ORDER BY
"Provider", "Medicaid"
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
Give me a count of unique patients and a count of services rendered, grouped by provider, patient category, and service code for a specified date range.
SELECT
(a.provfname + ' ' + a.provlname) AS "Provider",
c.categcode AS "Category",
a.srvcode AS "Service",
COUNT(DISTINCT a.ptnum) AS "Pt Count",
COUNT(DISTINCT a.jnum) AS "Srv Count"
FROM
sos.rv_charges a
JOIN sos.patients b ON a.ptnum = b.ptnum
LEFT OUTER JOIN sos.ptcategs c ON b.ptcategnum = c.ptcategnum
WHERE
a.trandate BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY
"Provider","Category","Service"
We are looking to send a brief survey to patients who have recently completed treatment. We hope to use their feedback to improve our services. Since insurance companies have been moving in the direction of wanting to see quality of service, this could come in handy.
We want a list of patients who have had a CPT code type of transaction within a prescribed time AND who have had no CPT code transaction in the last 90 days.
We would also like a column for Age and to export the results to Excel so that it will be easy to use the results as a mail merge file.
The query below includes both date of birth and age columns, and an OUTPUT line to do the export. Modify the file name in that line as you like, but no spaces unless you also surround the filename with quotation marks! The additions are in red. Excel format output is not available in all versions of the query utility, so we will output the results as an HTML file instead. You can open this file using Excel, and, if you like, re-save (File > Save as) in Excel format. Note also that this query demonstrates the use of NESTED SUBQUERIES:
SELECT
lastname, firstname, id,
lfeedate AS "Last Service",
priprvcode AS "primary Provider",
email,
/*count(distict trandate) as SrvDateCount */
(SELECT COUNT(DISTINCT a.trandate)
FROM sos.journal a JOIN sos.jcharges b ON a.jnum = b.jnum
WHERE a.ptnum = pt.ptnum
AND servicenum IN
(SELECT servicenum
FROM sos.services
WHERE srvcode IN ( '90801','90806','90847', '90853' ) ) )
/*replace 'A','B','C' above with the codes you want to count */
AS "SrvDateCount",
dob,
sos.AGEINYEARS(dob,TODAY()) AS "Age"
FROM
sos.rv_patients AS pt
WHERE
IntakeDate BETWEEN (TODAY( ) - 240) AND TODAY()
/*automatically does last 8 months. Note that TODAY( ) returns the same
value AS "CURRENT DATE"*/
AND lfeedate < (CURRENT DATE - 90) AND SrvDateCount <= 93 ;OUTPUT TO c:\sos\survey.html FORMAT HTML
Simple Software Designs, LLC, dba Synergistic Office Solutions