# 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.

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)``````

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What dbms are you using?