Completed Patients With Address For Mail Merge
Back to Query Directory
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. Note that export to EXCEL is supported only
in the DBISQLG utility, not in DBISQLC, so use the G version to run this one. 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 journal a JOIN jcharges b ON a.jnum = b.jnum
WHERE a.ptnum = pt.ptnum
AND servicenum IN
(SELECT servicenum
FROM services
WHERE srvcode IN ( '90801','90806','90847', '90853' ) ) )
/*replace 'A','B','C' above with the codes you want to count */
AS "SrvDateCount",
dob,
AGEINYEARS(dob,TODAY()) AS "Age"
FROM
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.xls FORMAT EXCEL
lastname, firstname, id,
lfeedate AS "Last Service",
priprvcode AS "primary Provider",
email,
/*count(distict trandate) as SrvDateCount */
(SELECT COUNT(DISTINCT a.trandate)
FROM journal a JOIN jcharges b ON a.jnum = b.jnum
WHERE a.ptnum = pt.ptnum
AND servicenum IN
(SELECT servicenum
FROM services
WHERE srvcode IN ( '90801','90806','90847', '90853' ) ) )
/*replace 'A','B','C' above with the codes you want to count */
AS "SrvDateCount",
dob,
AGEINYEARS(dob,TODAY()) AS "Age"
FROM
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.xls FORMAT EXCEL
CategoryQueries
