Date comparison in two table by returning the nearest date of table A in table B

Question

Date comparison in two table by returning the nearest date of table A in table B

arnold-491793

Say Hey Kid

Points: 709
More actions
June 9, 2014 at 9:13 pm

#294243

Hi,
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 1

arnold-491793 (6/9/2014)
Hi,
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A003 CreatedDatetime: 2014-06-03 21:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000

Is there error in wanted result set?

Should it be A002 for W001?

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 2

Ville-Pekka Vahteala (6/9/2014)
arnold-491793 (6/9/2014)
Hi,
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A003 CreatedDatetime: 2014-06-03 21:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
Is there error in wanted result set?
Should it be A002 for W001?

Yes you are right. It should be A002 for W001.

VIG SSC-Addicted Points: 488 More actions · Answer 3

select ta.*,b.id,b.CreatedDate

from @TableA ta cross apply

(select tb.id,tb.CreatedDate,ROW_NUMBER() over(partition by ta.id order by(abs(DATEDIFF(S,ta.CreatedDate,tb.CreatedDate)))) rn

from @TableB tb ) b

where rn=1