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

Transact-SQL
1

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 table example

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.

Many thanks

Adrian

2

In all honesty I'm none too clear what your query is trying to achieve, as it doesn't actually seem to create any "links" in your data. As you've not had any response I'll try to hazard a guess what might get you on track or at least help.

Try the following query. It will list records from your table in pairs. It will only pair records with the SAME ID2 value, and will pair them based on their Daysdiff value (lowest first) then the Tumour_Stage_Linenno. Obviously feel free to change the conditions if it's not what you have in mind.

WITH CTE AS (SELECT ID2, DaysDiff, Tumour_Stage_Lineno, ROW_NUMBER() OVER (PARTITION BY ID2 ORDER BY DaysDiff, Tumour_Stage_Lineno) 'RowNo'
FROM temp1)
SELECT ls., rs.
FROM CTE
INNER JOIN temp1 ls
ON CTE.ID2 = ls.ID2
AND CTE.Tumour_Stage_Lineno = ls.Tumour_Stage_Lineno
LEFT OUTER JOIN temp1 rs
ON CTE.ID2 = rs.ID2
AND CTE.Tumour_Stage_Lineno = rs.Tumour_Stage_Lineno