Closest set of data based on nearest date

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)

Closest set of data based on nearest date

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2014-01-21 : 07:37:54

I have two tables as below and I want to return the rows for CARE_ID and WHO_STATUS where the MDT_DATE is the closest date on or before the earliest SURGERY_DATE for each CARE_ID.
For instance for CARE_ID 5 the closest MDT_DATE on or before the earliest SURGERY_DATE of 18/07/2009 is 17/07/2009 so the WHO_STATUS would be 2, and so on. How would I do this please?

CARE_ID	SURGERY_DATE
5	18/07/2009 00:00
5	23/07/2009 00:00
5	23/07/2009 00:00
5	23/07/2009 00:00
5	01/09/2009 00:00
5	03/09/2009 00:00
70	20/07/2009 00:00
70	21/07/2009 00:00
76	03/03/2010 00:00
78	08/07/2009 00:00
81	27/07/2009 00:00
82	27/07/2009 00:00
83	30/07/2009 00:00
86	29/07/2009 00:00
91	30/07/2009 00:00
103	03/08/2009 00:00
106	05/08/2009 00:00
125	07/08/2009 00:00
172	19/05/2010 00:00


CARE_ID	MDT_DATE	WHO_STATUS
5	17/07/2009 00:00	2
5	03/11/2009 00:00	1
70	23/03/2010 00:00	0
81	03/11/2009 00:00	1
81	18/11/2009 00:00	1
81	27/11/2009 00:00	3
81	27/03/2010 00:00	1
103	03/12/2008 00:00	0
103	04/01/2009 00:00	2
103	06/01/2010 00:00	1
103	08/02/2010 00:00	1
103	14/01/2013 00:00	1
172	20/07/2009 00:00	4
172	08/01/2010 00:00	3
172	25/09/2010 00:00	1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 09:39:18

[code]
SELECT t1.*,t2.WHO_STATUS
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY SURGERY_DATE) AS Seq,*
FROM Table1
)t1
CROSS APPLY(SELECT TOP 1 WHO_STATUS
FROM Table2
WHERE CARE_ID = t1.CARE_ID
AND MDT_DATE < = t1.SURGERY_DATE
ORDER BY MDT_DATE DESC)t2
WHERE t1.Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stamford
Starting Member

47 Posts

Posted - 2014-01-21 : 10:24:03

quote:
Originally posted by visakh16

SELECT t1.*,t2.WHO_STATUS
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY SURGERY_DATE) AS Seq,*
FROM Table1
)t1
CROSS APPLY(SELECT TOP 1 WHO_STATUS
FROM Table2
WHERE CARE_ID = t1.CARE_ID
AND MDT_DATE < = t1.SURGERY_DATE
ORDER BY MDT_DATE DESC)t2
WHERE t1.Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thank you, I'll give it a try!

stamford
Starting Member

47 Posts

Posted - 2014-01-21 : 13:01:27

quote:
Originally posted by visakh16

SELECT t1.*,t2.WHO_STATUS
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY CARE_ID ORDER BY SURGERY_DATE) AS Seq,*
FROM Table1
)t1
CROSS APPLY(SELECT TOP 1 WHO_STATUS
FROM Table2
WHERE CARE_ID = t1.CARE_ID
AND MDT_DATE < = t1.SURGERY_DATE
ORDER BY MDT_DATE DESC)t2
WHERE t1.Seq=1

I have now discovered that I need to make this backwards compatible with SQL 2000. I don't believe that CROSS APPLY can be used in SQL 2000. How can the above script be re-worked? Thank you.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 08:27:27

[code]
SELECT t1.*,
(SELECT TOP 1 WHO_STATUS
FROM Table2
WHERE CARE_ID = t1.CARE_ID
AND MDT_DATE < = t1.SURGERY_DATE
ORDER BY MDT_DATE DESC
) AS WHO_STATUS
FROM Table1 t1
INNER JOIN (
SELECT CARE_ID,MIN(SURGERY_DATE) AS First
FROM Table1
GROUP BY CARE_ID
)t2
ON t2.CARE_ID = t1.CARE_ID
AND t2.First = t1.SURGERY_DATE
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

stamford
Starting Member

47 Posts

Posted - 2014-01-22 : 12:02:33

quote:

Originally posted by visakh16


SELECT t1.*,
(SELECT TOP 1 WHO_STATUS
            FROM Table2
            WHERE CARE_ID = t1.CARE_ID
            AND MDT_DATE < = t1.SURGERY_DATE
            ORDER BY MDT_DATE DESC
) AS WHO_STATUS
FROM Table1 t1
INNER JOIN (
            SELECT CARE_ID,MIN(SURGERY_DATE) AS First
            FROM Table1
            GROUP BY CARE_ID
            )t2
ON t2.CARE_ID = t1.CARE_ID
AND t2.First = t1.SURGERY_DATE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

OK thanks this is good stuff. Is there any way of amending the script to allow for a tie breaker if there are two values for MIN(SURGERY_DATE) which are the same?
The primary key for the table would be TREATMENT_ID
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 07:25:31

quote:
Originally posted by stamford

quote:
Originally posted by visakh16

SELECT t1.*,
(SELECT TOP 1 WHO_STATUS
FROM Table2
WHERE CARE_ID = t1.CARE_ID
AND MDT_DATE < = t1.SURGERY_DATE
ORDER BY MDT_DATE DESC
) AS WHO_STATUS
FROM Table1 t1
INNER JOIN (
SELECT CARE_ID,MIN(SURGERY_DATE) AS First
FROM Table1
GROUP BY CARE_ID
)t2
ON t2.CARE_ID = t1.CARE_ID
AND t2.First = t1.SURGERY_DATE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

OK thanks this is good stuff. Is there any way of amending the script to allow for a tie breaker if there are two values for MIN(SURGERY_DATE) which are the same?
The primary key for the table would be TREATMENT_ID
Thank you

it already does that

in case of multiples with same MIN surgerydate it retrieves them all.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources