SQL syntax needed.

I have a table that has columns for patient id, eye, visit date, monthSinceStart, measurement.

The table has patient data over a number of years.  Each visit to the clinic results in a new row in the table.

I would like to be able to delete the last year of data for every patient.  This is because I am only interested in data when a patient has completed a full year of treatment.

The column MonthsSinceStart indicates how many months has passed since the patients first visit.  On the patients first visit this column has a value of zero.

So to have a full year of data the patient must have a value of greater than 11 to be able to include him/her for one year, and greater than 23 for two years.

To be able to delete the uncompleted years I would like to look up the maximum value of MonthsSinceStart (which should be when VisitDate is at its maximum value). Then work out which year it is (simple maths divide by 12) and then delete all rows that are part of the last year.

So for a patient that has visits at 0, 3, 8, 13, 16 months I find that 16 is the maximum.  This gives a year of 1. So i want to delete the rows with 13 and 16.

Similarity a patient that has longer treatment - say 0, 3, 8, 13, 16, 20, 26, 28 - would result in a maximum value of 2 for year, and rows with 26 and 28 would be deleted.

Can someone write me the sql.  Remember its the combination of patient id and eye that must be unique here.

soozhAsked:
Who is Participating?
 
LowfatspreadCommented:
to actually delete
delete 
  from Yourtable as E 
 
 where exists 
(Select 'Y'
   from (select patientid,eye,max(monthsincestart) as MM
               from yourtable
              group by patientid,eye) as x
 where e.patientid=x.patientid
   and e.eye=x.eye
   and e.monthsincestart > 12*(x.mm/12)
)

Open in new window

0
 
LowfatspreadCommented:
not sure you really want to delete the data...

would a view suffice?  to hide the last incomplete years data?



create view CompleteYears (
select e.*
  from Yourtable as E 
 inner join (select patientid,eye,max(monthsincestart) as MM
               from yourtable
              group by patientid,eye) as x
 on e.patientid=x.patientid
 and e.eye=x.eye
 where e.monthsincestart <= 12*(x.mm/12)

Open in new window

0
 
awking00Commented:
What dbms are you using?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.