conditional join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

conditional join

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-04-27 : 18:53:48

Table A
Registation_key discharge_dt

1001 7/15/07
1002 6/4/07
1003 7/30/07


Table B
Registration_key ID end_dt value 
1001 879 07/11/07 1
1001 888 07/15/07 2 
1002 777 06/01/07 5 
1002 778 06/01/07 2
1002 779 06/30/07 6 
1003 234 07/31/07 3
1003 235 07/31/07 1
1003 236 08/01/07 7

I am trying to join Table A to Table B using the below log . Table A should have one unique mathincg record from Table B

option 1. Using registration_key and discharge_dt , looking for exact matching date( end_dt) in table B , if there is more than one record that matches then select lowest ID

option 2.if there is no record that matches option1 then, serch for the previous record with end_dt in table A less than end_dt table B. if there is more than one record then select lowest ID

option 3. if there is no record option 2 then search the next record that matches .. discharge_dt greater than end_Dt , if there is more than one record then select lowest Id

so basically, I am looking for an exact matching date in the same registration_key .. if the exact dt doesn't exist looking for the previous record and get the most closer dt and if there is no prvious record than look for next record,.

so the output should look like

output
Registration_key ID end_dt discharge_dt value

1001 888 07/15/07 7/15/07 2
1002 777 06/01/07 6/4/07 5
003 234 07/31/07 7/30/07 3

How can I do this conditional join ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 00:37:47

[code]SELECT a.Registration_key ,
COALESCE(b.ID,c.ID,d.ID) AS ID,
COALESCE(b.end_dt,c.end_dt,d.end_dt) AS end_dt,
a.discharge_dt ,
COALESCE(b.value,c.value,d.value) AS [value]
FROM @TableA a
OUTER APPLY (SELECT ROW_NUMBER() OVER(Order By ID) AS RowNo,*
FROM @TableB
WHERE Registration_key=a.Registration_key
AND end_dt=a.discharge_dt)b
OUTER APPLY (SELECT ROW_NUMBER() OVER(Order By ID) AS RowNo,*
FROM @TableB
WHERE Registration_key=a.Registration_key
AND end_dt<a.discharge_dt)c
OUTER APPLY (SELECT ROW_NUMBER() OVER(Order By ID) AS RowNo,*
FROM @TableB
WHERE Registration_key=a.Registration_key
AND end_dt>a.discharge_dt)d
WHERE (b.RowNo=1 OR b.RowNo IS NULL)
AND (c.RowNo=1 OR c.RowNo IS NULL)
AND (d.RowNo=1 OR d.RowNo IS NULL)

output
------------------------------------------------
Registration_key ID end_dt discharge_dt value
---------------- ----------- ----------------------- ----------------------- -----------
1001 888 2007-07-15 00:00:00.000 2007-07-15 00:00:00.000 2
1002 777 2007-06-01 00:00:00.000 2007-06-04 00:00:00.000 5
1003 234 2007-07-31 00:00:00.000 2007-07-30 00:00:00.000 3[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources