I need total # of patients seen, broken down by provider, patient category, and service code, for a specific time frame.
SELECT
(a.provfname + ' '+a.provlname) AS "Provider",
c.categcode AS "Category",
a.srvcode AS "Service",
COUNT(DISTINCT a.ptnum) AS "Pt 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 '1980-01-01' AND '2008-12-31'
GROUP BY
"Provider","Category","Service"
Would it be possible to write a query to do the following:
List the patient name, account number, outstanding balance and provider
For any account that has not had a date of service in 2009
And has had no payments within the past 30 days.
We want to use this to clear out all such outstanding accounts..
The views used in the query below are not super-efficient, so on a large database it will take a good while to run, but it will deliver the results you want.
SELECT
a.lastname + ', '+ a.firstname AS "Name",
a.id AS "Account",
c.provcode AS "Primary-Provider",
(SELECT sos.LASTCHARGEDATE(a.ptnum)) AS "LastService",
(SELECT sos.LASTCREDITDATE(a.ptnum)) AS "LastPayment",
d.ptbalance AS "Balance"
FROM sos.patients a
LEFT OUTER JOIN sos.providers c ON a.providernum = c.providernum
JOIN sos.patientbalance d ON a.ptnum = d.ptnum
WHERE
"LastService" < '2009-01-01'
AND ("LastPayment" < (TODAY()-30) OR "LastPayment" IS NULL )
ORDER BY
"Name", "Account"
;
OUTPUT TO c:\sos\cleanup.html FORMAT HTML
;
I am looking for a query that prints out total balance remaining on patients only (not insurance) by LOC code (e.g.,11 or 61) by date range by provider.
The following query gives the balance itemized by patient. To get just summary totals, remove “a.lastname,a.firstname,a.id” from the SELECT and GROUP BY clauses.
SELECT
a.provcode,a.lastname,a.firstname,a.id,SUM(a.chgsplbal) AS "Balance"
FROM
sos.rv_charges a
JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN sos.payors c ON b.payornum = c.payornum
JOIN sos.poscodes d ON a.poscodenum = d.poscodenum
JOIN sos.patients e ON a.ptnum = e.ptnum
WHERE
a.licnum = 101 AND /*look only at main data set*/
e.flag = 0 AND /* just active list patients*/
c.payortype <> 'I' /* ignore insurance splits*/
AND d.defcode IN ('11','61') /* place of service code is 11 or 61*/
AND a.trandate BETWEEN '2009-01-01' AND '2009-03-31' /* date range*/
GROUP BY
a.provcode,a.lastname,a.firstname,a.id
We like to have a follow-up appointment scheduled for each patient after a visit, or an alarm set to make one if follow-up isn’t needed for a while. This ensures patient is seen again before medication runs out. However, many patients are in a hurry to leave after their appointment and don’t stop by the front window to schedule their follow-up, or they want to go home and check their schedule first, etc.
We would like to have a report that will print in one column every patient that was seen during a given day, and then have a column where it prints the date of any follow-up appointment made (pulled from scheduler) and a second column where it will show the date of an alarm set to remind us to follow-up (in the event we weren’t able to schedule).
There might be two or more alarms in the future — even two or more on the very next future date, so in this query we retrieve the first of those entered (smallest ptnotenum) and also display the first part of the note text entered for the alarm. To accomplish all this, we use correlated subqueries in the SELECT list.
SELECT
a.ptfullname AS "Name",
a.id AS "ID",
(SELECT MIN(apptdate) FROM sos.rv_appts WHERE ptnum = a.ptnum AND apptdate > a.apptdate) AS "Next Appt",
(SELECT MIN(AlarmDate) FROM sos.ptnotes WHERE ptnum = a.ptnum AND alarmdate > a.apptdate) AS "Next Alarm",
(SELECT Note FROM sos.ptnotes WHERE ptnotenum =
(SELECT MIN(ptnotenum)FROM sos.ptnotes WHERE ptnum = a.ptnum AND AlarmDate > a.apptdate)
) AS "Alarm Note"
FROM
sos.rv_appts a
WHERE
a.apptdate = TODAY()
ORDER BY
"Name", "ID"
I want to print sets of patient mailing labels, but filtering for specified primary provider codes, patient category, and patient age. Include only patients who have been seen for a chargeable service within the last year.
One effective way to do this is to create a result set that matches what Microsoft Word expects in a mailing list and export it to Excel format. You can then use the Mail Merge wizard in Word to very easily create your labels. Note that we have used the TRIM function on some of the elements to be sure that any extra spaces are removed from the end of the data. We have also used AS to rename the data elements to match what Word is looking for. That saves the step of matching fields when setting up your Mail Merge. This query uses a custom function “AgeInYears” that SOS provides in your database so that you can get accurate age calculations by simply providing the date of birth and the target date. Here we are interested in the patient’s age right now, so instead of hard-coding a date, we use the SQL function TODAY(), which is replaced automatically by the current date when we run the query. The same TODAY() function is used in the condition that restricts the patients to those seen in the past year.
For a video that shows how to do MS Word mail-merge labels using the Excel file produced by this query, go to…
http://www.sosoft.com/files/tv/other/querylabelmerge.swf
SELECT
TRIM(a.firstname) AS "First Name",
TRIM(a.lastname) AS "Last Name",
a.addr1 AS "Address 1",
a.addr2 AS "Address 2",
TRIM(city) AS "City",
TRIM(state) AS "State",
zip
FROM
sos.rv_patients a
JOIN sos.ptvars b ON a.ptnum = b.ptnum
WHERE
a.licnum = 101
AND a.priprvcode IN ('AF','AFB')
AND a.categcode = 'C'
AND sos.AgeInYears(a.dob,TODAY() ) BETWEEN 0 AND 80
AND b.lfeedate > (TODAY() - 365)
ORDER BY
a.lastname, a.firstname
;
OUTPUT TO c:\sos\labels.html FORMAT HTML
Simple Software Designs, LLC, dba Synergistic Office Solutions