I hope I am explaining this clearly enough for someone to figure the result out. I left joined two tables based on one common variable which represents in this case NHS number. Both tables have a unique row identifier but they are independent of each other (ID2, TUMOUR_STAGE_LINENO) (if that makes sense). My problem is to identify the correct linkage between them based on a minimum days difference between the dates of diagnosis from the two tables but also each row identifier should not be linked twice to the other row identifiers. I will show you an extract of the linked data with a few examples.
UPDATE NBOCAP.dbo.temp1 SET LINKAGE = 'TRUE' FROM NBOCAP.dbo.temp1 JOIN (SELECT ID2, MIN(DAYSDIFF) D FROM NBOCAP.dbo.temp1 GROUP BY ID2) AS X ON temp1.ID2 = X.ID2 AND temp1.DAYSDIFF = X.D AND DATE_OF_DIAGNOSIS < '2013-01-01' WHERE temp1.TUMOUR_STAGE_LINENO IN (SELECT a.TUMOUR_STAGE_LINENO FROM temp1 a INNER JOIN temp1 b ON b.TUMOUR_STAGE_LINENO <> a.TUMOUR_STAGE_LINENO)
I don't think my WHERE condition does anything in this instance....
The linked table is this:
Apologies but I am a new user and am not allowed to post images.
As you can see with nhs numbers 2 & 6 I am getting the same TUMOUR_STAGE_LINENO linked twice to both ID2s because DAYSDIFF are the smallest. My question is how to write in sql that after looking at MIN(DAYSDIFF) to make sure that the next linked TUMOUR_STAGE_LINENO should not be the same as the first.
I appreciate everyone's time taken to look at this.
PS. As you may have noticed there is also the possibility of having same DAYSDIFF thus creating a duplicate linkage. But that is probably another issue I need to consider.
It is important to mention that my interest is that the ID2 gets the correct linked row.