looking for a closer date.. - 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)

looking for a closer date..

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-03 : 11:23:13

I am trying to link a table A to table B using service_dt : service_dt closer to asessment_dt

Table A

Reg_key service_dt
1 07/01/07
2 08/11/07
3 07/23/07

Table B

Reg_key ID assessment_dt
1 1001 07/11/07
1 1111 07/01/07
2 1012 08/01/07
2 1013 08/30/07
2 1187 09/12/07
3 657 07/20/07
3 789 07/20/07
3 1999 09/11/07

I am looking for a matching assessment_dt.. if it doesn;t exist .. i have to grab the closer assessment_dt..if there is more than one same record , then grap the lowest ID..

so the output should look like

REg_key service_dt ID assessment_dt
1 07/01/07 1111 07/01/07
2 08/11/07 1012 08/01/07
2 07/23/07 657 07/20/07

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 11:52:58

[code]SELECT tmp.Reg_Key,tmp.service_dt,MIN(t.ID) AS ID,t.assessment_dt
FROM TableB t
INNER JOIN
(
SELECT a.Reg_Key,a.service_dt,b.assessment_dt
FROM [Table A] a
CROSS APPLY (SELECT MAX(assessment_dt) AS assessment_dt
FROM Table B
WHERE Reg_key=a.Reg_key
AND assessment_dt<=service_dt)b
)tmp
ON tmp.Reg_Key=t.Reg_Key
AND tmp.assessment_dt=t.assessment_dt
GROOUP BY tmp.Reg_Key,tmp.service_dt,t.assessment_dt[/code]

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-03 : 12:23:25

Thank you ..
can you explain to me what CROSS Apply doing in this query..?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 12:45:03

quote:
Originally posted by jung1975

Thank you ..
can you explain to me what CROSS Apply doing in this query..?

It gets for each record in table A the maximun assessment_dt value which is either equal to or just less than service_dt value.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-03 : 12:55:43

problem is there can be a record greater than the service date ..but closer than the earier date... please see the below example..

Table A

Reg_key service_dt
1 07/01/07
2 08/11/07
3 07/23/07
4 08/05/07

Table B

Reg_key ID assessment_dt
1 1001 07/11/07
1 1111 07/01/07
2 1012 08/01/07
2 1013 08/30/07
2 1187 09/12/07
3 657 07/20/07
3 789 07/20/07
3 1999 09/11/07
4 566 07/11/07
4 777 07/20/07
4 888 08/07/07
4 889 08/11/07

REg_key service_dt ID assessment_dt
1 07/01/07 1111 07/01/07
2 08/11/07 1012 08/01/07
3 07/23/07 657 07/20/07
4 08/05/07 888 08/07/07

Do i have to use some kind of date diff function to compare the previous dates and next dates? how can i do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 13:11:03

Then try this:-

SELECT tmp.Reg_Key,tmp.service_dt,MIN(t.ID) AS ID,t.assessment_dt
FROM TableB t
INNER JOIN 
(
SELECT a.Reg_Key,a.service_dt,
CASE WHEN DATEDIFF(dd,ISNULL(b.assessment_dt,a.service_dt),a.service_dt) < DATEDIFF(dd,a.service_dt,ISNULL(c.assessment_dt,a.service_dt)) THEN
b.assessment_dt
ELSE c.assessment_dt 
END AS assessment_dt
FROM [Table A] a
OUTER APPLY (SELECT MAX(assessment_dt) AS assessment_dt
             FROM Table B
             WHERE Reg_key=a.Reg_key
             AND assessment_dt<=a.service_dt)b
OUTER APPLY (SELECT MIN(assessment_dt) AS assessment_dt
             FROM Table B
             WHERE Reg_key=a.Reg_key
             AND assessment_dt>a.service_dt)c
)tmp
ON tmp.Reg_Key=t.Reg_Key
AND tmp.assessment_dt=t.assessment_dt
GROUP BY tmp.Reg_Key,tmp.service_dt,t.assessment_dt

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-03 : 19:27:21

thanks.. but i am getting the null values even though the date fall into one of cross apply condition .. i am not sure why?..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 21:46:58

quote:
Originally posted by jung1975

thanks.. but i am getting the null values even though the date fall into one of cross apply condition .. i am not sure why?..

Ok. Make slight modification and try:-

SELECT tmp.Reg_Key,tmp.service_dt,MIN(t.ID) AS ID,t.assessment_dt
FROM TableB t
INNER JOIN 
(
SELECT a.Reg_Key,a.service_dt,
CASE 
WHEN b.assessment_dt IS NULL THEN c.assessment_dt
WHEN c.assessment_dt IS NULL THEN b.assessment_dt
WHEN DATEDIFF(dd,b.assessment_dt,a.service_dt) < DATEDIFF(dd,a.service_dt,c.assessment_dt) THEN
b.assessment_dt
ELSE c.assessment_dt 
END AS assessment_dt
FROM [Table A] a
OUTER APPLY (SELECT MAX(assessment_dt) AS assessment_dt
             FROM Table B
             WHERE Reg_key=a.Reg_key
             AND assessment_dt<=a.service_dt)b
OUTER APPLY (SELECT MIN(assessment_dt) AS assessment_dt
             FROM Table B
             WHERE Reg_key=a.Reg_key
             AND assessment_dt>a.service_dt)c
)tmp
ON tmp.Reg_Key=t.Reg_Key
AND tmp.assessment_dt=t.assessment_dt
GROUP BY tmp.Reg_Key,tmp.service_dt,t.assessment_dt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources