We need to know the formula of the PATIENT ::DATE_OF_DEATH-DATE_OF_SURGERY field.
DATE_OF_DEATH is a date field in the PATIENT table and DATE_OF SURGERY is in the treatment table. The calculation is in the treatment table as it relates to that particular treatment in a 1 to many relationship.
I think this is where I am going wrong;
If I have the values
DATE_OF DEATH as 02/01/2008
DATE_OF SURGERY as 01/01/2008 - then date of death within 7 days is true, but if I have DATE OF DEATH as 29/01/2008 then death within 7 days is false and death within 30 days is true.
However, if I have the DATE OF DEATH as 16/01/2009 then the calculation is displaying Yes as per calculation ;-(
What I expected, is that this would be incremental ie 1 2 3 4 etc. etc.
I think what is happening is that the calculation is staying at a low number even after a year - viz. 02/01/2009 would then be 1 again?
Assuming you mean to find out if a patient died within 7 days of their last surgery, try:
DATE_OF_DEATH - Max ( Treatments:: DATE_OF_SURGERY ) ≤ 7
This calculation should be in the Patients table. Make sure both referenced fields are of type Date.
Many thanks for the suggestion, will give it a go and let you know.