Hello Hi peeps I am trying to figure out a complicated scenario (Complicated for me) and was looking to see if anyone could help me out I have a case when a patient is comes in for a prescription multiple times and each time he comes he gets prescribed for a certain days. I need to find out if the patient was using the drug 3 months from the date he was first prescribed. Patient Prescription Days Prescribed for 3421 9/5/2007 15 3421 9/20/2007 14 3421 10/4/2007 14 3421 10/18/2007 21 3421 11/8/2007 12 3421 11/20/2007 22 3421 12/12/2007 36 3421 1/17/2008 28 3421 2/14/2008 28 so in this case the patient was prescribed for the first time on 9/5/2007 and 3 months later ie on 12/5/2007 he was still on medication (As he was prescribed on 11/20/2007 for 20 days which means he was covered for 12/5/2007) I wanted to know if there is a way i could figure this out Thank you for any help in advance. Please let me know if i am not clear in explaining the scenario
Here's a solution that works in your scenario: create table #prescriptions (Patient int, PrescriptionDate datetime, DaysPrescribed int) go set dateformat mdy go insert #prescriptions select 3421, '9/5/2007', 15 union all select 3421, '9/20/2007', 14 union all select 3421, '10/4/2007', 14 union all select 3421, '10/18/2007', 21 union all select 3421, '11/8/2007', 12 union all select 3421, '11/20/2007', 22 union all select 3421, '12/12/2007', 36 union all select 3421, '1/17/2008', 28 union all select 3421, '2/14/2008', 28 -- check for an individual declare @check_date datetime select @check_date = dateadd(month,3,min(PrescriptionDate)) from #prescriptions where Patient = 3421 ; with cte as ( select * , CoverageEndDate = dateadd(day,DaysPrescribed,PrescriptionDate) from #prescriptions where Patient = 3421 ) select OnMeds = max( case when @check_date between PrescriptionDate and CoverageEndDate then 'Yes' else 'No' end ) from cte -- check for a group -- add a second patient insert #prescriptions select 3422, '9/5/2007', 15 union all select 3422, '9/20/2007', 14 union all select 3422, '10/4/2007', 14 -- this time, isolate the first record per patient and compare against the remaining data ; with cte as ( select Patient , StartDate = PrescriptionDate , CheckDate = dateadd(month,3,PrescriptionDate) , Picker = row_number() over (partition by Patient order by PrescriptionDate) from #prescriptions ) select cte.Patient , StartDate , CheckDate , OnMeds = case when d.Patient is null then 'No' else 'Yes' end from cte left join ( select Patient , PrescriptionDate , CoverageEndDate = dateadd(day,DaysPrescribed,PrescriptionDate) from #prescriptions ) d on cte.Patient = d.Patient and cte.CheckDate between d.PrescriptionDate and d.CoverageEndDate where cte.Picker = 1 But I'd imagine that things could be more complicated. How are breaks in treatment handled? E.g. starts meds for 20 days, has a month off, then starts again for 40 days. What about overlapping prescriptions? E.g. 1st Sep I get 20 days of pills, then on 15th Sep I get another 20 days? That gives me enough pills to get through to 10th Oct, but this calculation will treat it as if I run out on the 5th.
I may be completely misunderstanding, but isn't this just a simple SELECT? Borrowing Ian's test data... select * from #prescriptions where @check_date between PrescriptionDate and dateadd(day,DaysPrescribed, PrescriptionDate); If that returns any rows, they were covered (it returns 20th Nov 2007).