Project: ID Non DM Patients and their A1c Values

This morning I worked to finish up a little project Andy had assigned to me last week.  He asked me to pull all non diabetic patients that had been seen in 2016, and to pull in their most recent A1c values if they had one.  Andy suggested originally (last week) that I look at the Prototype_QryA query.  I think he initially thought that I would be able to change an = sign to a <> and that the query would pull in those that were not diabetic instead of patients that were diabetic.  The problem was that we were linking to the Historical Diagnosis table, and though a patient may show up as having a diabetic diagnosis (having a value in the DM_Dx field) that same patient may also  also have a diagnosis for something else - meaning they would still show up even though we were saying <> to a diabetic diagnosis.  Therefore, if you pull in patient numbers that don't have a DM diagnosis that doesn't mean they won't have other diagnosis.  So I had to go about this a different way.  I recognized this and started working on it late last week, but did not finish.

When I came in this morning Andy suggested using the vwDashboard_DMPop view rather than QryA.  He again suggested that I just use <> instead of =, so I tried that first.  First I ran the vwDashboard_DMPop qry and identified 10 patients that had diabetes.  Then I changed the Qry to <> and checked each of those ten patients and all of them still showed in the <>.  Conclusion:  simply subbing the <> for the = did not eliminate those with diabetes.

My new strategy involved identifying the DM patient numbers (saved in a new view called vwDMPatList RB).  Then I would run the <> qry (new saved as vwNEDMPopRB), but excluding the vwDMPatList RB numbers.  I couldn't quite figure out how to do the exclusion, but what I did see was that when I linked these two views together by patient number the patient number in the DMPatListRB showed a NULL if they were in the Non DM population but not in the DM population.  Therefore those that had a NULL were my Non-Diabetic Patients.  I then created another view (vwNonDMA1cRB) that linked the Non Dibetic patient list (NULLs) to the A1c Identifier qry (vwDashboard_DMA1c) that Andy had written for the dashboard.

I also filtered the original two queries for 2016.  I found that filtering between 1/1/2016 and 12/31/2016 on the Appointment Scheduled Date took a very long time.  The qry I borrowed those two from (vwDashboard_DMPop) had a Qtr and Year expression.  I just deleted the qtr expression for each and ran with the year as 2016.  Not ideal.  What if I need to run for next month for the previous 12 months?

Extension:  

I got this done, but there is a better way.  My skills are still weak, but I was pleased that I figured this out. I want to try to do all of this again, but in one large query or stored procedure, with temp tables.  

Also see if I can figure out the exclusion (of DM Patient Numbers) that I had trouble with.  

Also, see if I can improve the date range piece to a between t make it more flexible.