Active Patients with No Current Insurance
Back to Query Directory
I am lookng for a query that would be me a list of active patients, along with their birth dates and primary providers, for whom there is no current insurance coverage on file.
There are quite a few ways to do this, but here is one I especially like. There are several operators in SQL that allow you to create a single result set from the results of two or more queries. Each of the queries must deliver the same type of data in the same column, so if the desired final composite result set should be a character string in the first column, a date in the second, and an integer in the third, then each of the queries used to generate that final set must also produce those three columns in the same order. You don't have to use the same data elements and tables in each query, just so long as the TYPE of data in each column is consistent.
One of the operators is UNION, which, as you might guess, combines the results of each of the component queries into a single result set. Another operator is INTERSECT, which gives you only the rows that appear in each of the sub-result sets; if a row appears in one, but not the other, it is discarded. Finally, we have the EXCEPT operator, which gives us the rows that appear in ONLY the first of two queries, that is give me everything from query one EXCEPT what also appears in query two.
So, we can get our desired results by querying all the active patients (the first query) EXCEPT the active patients with current insurance (the second query). Note also that when you generate these compound result sets, you do your ORDER BY simply with column numbers rather than column names because each of your component queries could have different column names. Remember that the requirement is just that the type of data in each column match, not the table or column names.
One other thing: these compound queries eliminate duplicate rows by default, so it is not necessary to mess with DISTINCT.
/* First, let's get a list of all the active patients */
SELECT
a.lastname, a.firstname, a.id, a.dob, c.provcode
FROM
patients a JOIN
providers c ON a.providernum = c.providernum
WHERE
a.flag = 0 AND
a.dischargedate IS NULL
/* Now our EXCEPT operator */
EXCEPT
/* The list of patients who have current insurance. Because the policy start and end dates can be left blank in SOS, notice that we use COALESCE to replace missing dates with dates that would be well before or well after the current date. */
SELECT
lastname, firstname, id, a.dob, c.provcode
FROM
patients a JOIN
ptpolicies b ON a.ptnum = b.ptnum JOIN
providers c ON a.providernum = c.providernum
WHERE
CURRENT DATE BETWEEN COALESCE(b.active,'1980-01-01') AND COALESCE(b.inactive,'2200-12-31') AND
a.flag = 0 AND
a.dischargedate IS NULL
/* Finally, let's sort the results by name and account id*/
ORDER BY 1,2,3
SELECT
a.lastname, a.firstname, a.id, a.dob, c.provcode
FROM
patients a JOIN
providers c ON a.providernum = c.providernum
WHERE
a.flag = 0 AND
a.dischargedate IS NULL
/* Now our EXCEPT operator */
EXCEPT
/* The list of patients who have current insurance. Because the policy start and end dates can be left blank in SOS, notice that we use COALESCE to replace missing dates with dates that would be well before or well after the current date. */
SELECT
lastname, firstname, id, a.dob, c.provcode
FROM
patients a JOIN
ptpolicies b ON a.ptnum = b.ptnum JOIN
providers c ON a.providernum = c.providernum
WHERE
CURRENT DATE BETWEEN COALESCE(b.active,'1980-01-01') AND COALESCE(b.inactive,'2200-12-31') AND
a.flag = 0 AND
a.dischargedate IS NULL
/* Finally, let's sort the results by name and account id*/
ORDER BY 1,2,3
CategoryQueries
