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.