date functions 1

[georgia51390]

This is a tough one! I have a table of patient enrollment data. The table has a patient ID, an enrollment date, and a status value. Patients are in the table for every month that they are enrolled, and the date is always the last day of the month. I need to write a statement to look at each enrollment date, then look BACKWARDS 11 months from that date and verify that the patient had 11 status values. For example: For Patient A, if they have an enrollement date of 12/31/99, I need to look at 11/30/99, 10/31/99, 9/30/99, ..... all the way back to 1/31/99. I need to verify that for each of those dates starting with 12/31/99 and going backwards, that Patient A had a valid status value. If that patient had 11 valid status values from 1/31/99 - 12/31/99, then I want to pull that record with the 12/31/99 enrollment date.
Any suggestions?? This one has me stumped. Thanks so much!

 

[VogonPoet]

Can you/are you willing to use MS Access (as a front end to your SQL database) or VB to do this? If so, I can help.

Michael
mhodes@earthlink.net

 

[georgia51390]

I guess I am willing to try anything! Let me know what you think. . . thanks so much!

 

[tlbroadbent]

Let's approach this in a different way. What I propose will only work if your data is consistent and valid.

What you want to find is 12 consecutive months ending with the latest month on the table for each patient. Further, you need to determine if the patient had a valid enrollment status each month of the 12 month period. This means I need to find 12 months of valid status between MaxDate-11 and MaxDate.

By utilizing SQL aggregate functions, case statements and a simple INNER JOIN using a derived table or sub-query we should be able to identify the patients that meet the criteria.

1st step: get max enroll date per patient.
Select ID, Max(EnrollDate) As MaxEnroll
From PatientEnroll
Group By ID

2nd step: incorporate the above query in a JOIN query to identify patients that meet the criteria.

Select e.ID,
Sum(Case e.Status When 1 Then 1 Else 0 End) As ValidCnt,
Sum(Month(e.EnrollDate)) As MonthSum
From PatientEnroll e Inner Join
(Select ID, Max(EnrollDate) As MaxEnroll
From PatientEnroll
Group By ID) As q
On e.ID=q.ID
Where e.EnrollDate Between dateadd(month,-11,q.MaxEnroll) And q.MaxEnroll
Group By e.ID
Having Sum(Case Status When 1 Then 1 Else 0 End) = 12
And Sum(Month(e.EnrollDate)) = 78

NOTES:

The query uses Status=1 to identify valid enrollment status. You will need to replace this code with your criteria.

The query counts the number of valid months and sums the months to double check the validity of the data.

3rd step: select patient records that meet the criteria

Select * From PatientEnroll
Where ID In (Select e.ID,
Sum(Case e.Status When 1 Then 1 Else 0 End) As ValidCnt,
Sum(Month(e.EnrollDate)) As MonthSum
From PatientEnroll e Inner Join
(Select ID, Max(EnrollDate) As MaxEnroll
From PatientEnroll
Group By ID) As q
On e.ID=q.ID
Where e.EnrollDate Between dateadd(month,-11,q.MaxEnroll) And q.MaxEnroll
Group By e.ID
Having Sum(Case Status When 1 Then 1 Else 0 End) = 12
And Sum(Month(e.EnrollDate)) = 78)

Please let me know if you have any questions or problems with the code. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.