Identify the correct linkage between two tables' rows based on two conditions

90 Views Asked by At

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:

Link to data extract

Apologies for not uploading the image here but I do not have 10 rep points.

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

Many thanks

Adrian

0

There are 0 best solutions below