Author: SOS Blog

Patient Count for Period by SortCode

Posted on | by SOS Blog | Leave a Comment on Patient Count for Period by SortCode

This one is a variation on other, earlier queries. The difference here is the introduction of SortCode. SortCode is a value specified when entering charges and credits. In order to associate patients with particular SortCode values, we must inspect the transactions linked to the patient and to the SortCode.

If you were to inspect the values in the sortcode field of the journal table, you would see only numbers that do not reflect your selections in the SortCode field in the transaction entry windows. The number is a link to a row in the lookups table that contains the SortCode shorthand code and description. You must, therefore, include a JOIN from journal.sortcode to lookups.lunum, which is the analogous value in the lookups table.

The aggregate function structure COUNT(DISTINCT PTNUM) forces the query to count only unique
patients, eliminating duplicates. If you were to omit the keyword DISTINCT, you would end up with a
count of the number of transactions instead of the number of unique patients. Note that the query returns only charge entries (a.trantype = ‘S’) because we want to count only patients who have been seen during the period. We don’t want to count payments or adjustments (which share a trantype of ‘P’).

This example also uses a standard function to represent the current date, TODAY(*). An alternate syntax would simply be the words CURRENT DATE. The result would be identical. You could, of course, also substitute a specific date.

One more thing: note that if there are transactions for the same patient but that have different
SortCodes, the patient will appear in the count for each of the SortCodes linked to his or her charge
entries.

Here is the query:

SELECT
   lucode AS "SortCode", 
   COUNT(DISTINCT PTNUM) AS "ClientCount"
FROM
   sos.journal a
   JOIN sos.lookups b ON a.sortcode = b.lunum
WHERE
   a.trantype = 'S'
   AND a.trandate BETWEEN '2005-10-01' AND TODAY(*)
GROUP BY
   "SortCode"
ORDER BY
   "SortCode"

Active Patient Count For Date Range

Posted on | by SOS Blog | Leave a Comment on Active Patient Count For Date Range

This simple query tells you how many patients have charge entries in their ledgers between two dates.
The keyword DISTINCT in the COUNT() function eliminates duplicates.

SELECT
   COUNT(DISTINCT ptnum)
FROM
   sos.journal
WHERE
   trantype = 'S'
   AND amount > 0
   AND trandate BETWEEN '2004-01-01' AND '2004-12-31'

Here’s a variation using the rv_charges view that breaks down the patient count by rendering provider. In
this case, we use rv_charges as an easy way to get to the provider code associated with the charges:

SELECT
   provcode AS "Provider", 
   COUNT(DISTINCT ptnum) AS "Clients Seen"
FROM
   sos.rv_charges
WHERE
   amount > 0
   AND trandate BETWEEN '2004-01-01' AND '2004-12-31'
GROUP BY
   provcode

Admissions by Zipcode

Posted on | by SOS Blog | Leave a Comment on Admissions by Zipcode

Start with the basic count of intakes between two dates, grouped by zip code:

SELECT
   zip,
   COUNT(*) AS "N"
FROM 
   sos.rv_patients
WHERE
   intakedate BETWEEN '2004-01-01' AND '2004-12-31'
GROUP BY 
   zip
ORDER BY 
   zip

Next, let’s make it a little more interesting by breaking the results down further by year and month:

SELECT
   zip,
   YEAR(intakedate) AS "Yr",
   MONTH(intakedate) AS "Mon",
   COUNT(*) AS "N"
FROM 
   sos.rv_patients
WHERE
   intakedate BETWEEN '1990-01-01' AND '2005-12-31'
GROUP BY 
   zip, 
   "Yr", 
   "Mon"
ORDER BY 
   zip, 
   "Yr", 
   "Mon"

You could, of course, change the order of the columns in the SELECT clause and in the ORDER BY line
to reorganize the output.

Length of Stay by Provider

Posted on | by SOS Blog | 2 Comments on Length of Stay by Provider

Let’s take a look at this request from a detailed and summary angle.

SELECT
   Lastname,
   firstname,
   id,
   COALESCE(provcode,'None') AS "Provider",
   Intakedate,dischargedate,
   DateDiff(day,intakedate,dischargedate) AS "Days"
FROM 
   sos.patients a
   JOIN sos.providers b ON a.providernum=b.providernum
WHERE
   intakedate IS NOT NULL
   and dischargedate IS NOT NULL
....will give you a list of patients, their intake and discharge dates, and the number of days between those
dates.

Extending that to give us a summary, with provider, average length of stay, and the number of patients
from which the statistics are derived:
SELECT
   COALESCE(provcode,'None') AS "Provider",
   COUNT(*) AS "N",
   AVG(DateDiff(day,intakedate,dischargedate)) AS "Days"
FROM 
   sos.patients a
   JOIN sos.providers b ON a.providernum=b.providernum
WHERE
   intakedate IS NOT NULL
   and dischargedate IS NOT NULL
GROUP BY 
   "Provider"
ORDER BY 
   "Provider"

Referral Source, Patient Count and Charges By Month

Posted on | by SOS Blog | Leave a Comment on Referral Source, Patient Count and Charges By Month

The unique thing about this query is that it creates a grid with the months across the top. For each
Referral Source/Year there is a single row.

SELECT 
   b.refname as "Ref’d By", 
   YEAR(c.trandate) as "Year",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 1 THEN c.ptnum ENDIF)) as "Jan Count",
   SUM(IF MONTH(c.trandate) = 1 THEN c.amount ELSE 0 ENDIF) as "Jan Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 2 THEN c.ptnum ENDIF)) as "Feb Count",
   SUM(IF MONTH(c.trandate) = 2 THEN c.amount ELSE 0 ENDIF) as "Feb Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 3 THEN c.ptnum ENDIF)) as "Mar Count",
   SUM(IF MONTH(c.trandate) = 3 THEN c.amount ELSE 0 ENDIF) as "Mar Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 4 THEN c.ptnum ENDIF)) as "Apr Count",
   SUM(IF MONTH(c.trandate) = 4 THEN c.amount ELSE 0 ENDIF) as "Apr Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 5 THEN c.ptnum ENDIF)) as "May Count",
   SUM(IF MONTH(c.trandate) = 5 THEN c.amount ELSE 0 ENDIF) as "May Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 6 THEN c.ptnum ENDIF)) as "Jun Count",
   SUM(IF MONTH(c.trandate) = 6 THEN c.amount ELSE 0 ENDIF) as "Jun Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 7 THEN c.ptnum ENDIF)) as "Jul Count",
   SUM(IF MONTH(c.trandate) = 7 THEN c.amount ELSE 0 ENDIF) as "Jul Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 8 THEN c.ptnum ENDIF)) as "Aug Count",
   SUM(IF MONTH(c.trandate) = 8 THEN c.amount ELSE 0 ENDIF) as "Aug Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 9 THEN c.ptnum ENDIF)) as "Sep Count",
   SUM(IF MONTH(c.trandate) = 9 THEN c.amount ELSE 0 ENDIF) as "Sep Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 10 THEN c.ptnum ENDIF)) as "Oct Count",
   SUM(IF MONTH(c.trandate) = 10 THEN c.amount ELSE 0 ENDIF) as "Oct Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 11 THEN c.ptnum ENDIF)) as "Nov Count",
   SUM(IF MONTH(c.trandate) = 11 THEN c.amount ELSE 0 ENDIF) as "Nov Chgs",
   COUNT (DISTINCT (IF MONTH(c.trandate) = 12 THEN c.ptnum ENDIF)) as "Dec Count",
   SUM(IF MONTH(c.trandate) = 12 THEN c.amount ELSE 0 ENDIF) as "Dec Chgs",
   COUNT(DISTINCT a.ptnum) as "# Count", SUM(c.amount) as "TotChgs"
FROM 
   sos.patients a
   JOIN sos.refsrcs b on a.refsrcnum = b.refsrcnum
   JOIN sos.journal c on a.ptnum = c.ptnum
WHERE 
   c.trantype = 'S' 
   AND c.trandate > '2000-12-31'
GROUP BY 
   b.refname, 
   YEAR(c.trandate)
ORDER BY 
   b.refname, 
   YEAR(c.trandate)