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 2012 Forums
 Transact-SQL (2012)
 How to get the nearest earlier Time

Author  Topic 

redhat69
Starting Member

2 Posts

Posted - 2014-11-10 : 18:20:54
I need to find the nearest earlier time From Table A, Given the time in table B. Example:

Table A has
3:00
3:45
4:00
4:30
5:25
6:00

I need to match

Table B
4:17
5:28

The end result should be
Table A <-->Table B
4:00 <--> 4:17
5:25 <--> 5:28

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2014-11-11 : 01:15:32
Try something like this:

SELECT A.time, B.time
FROM TableB AS B
CROSS APPLY
(SELECT TOP(1) A.time
FROM TableA AS A
WHERE A.time < B.time
ORDER BY A.time DESC) AS A;



For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-11-18 : 01:40:03
Something like this..

CREATE TABLE #TableA(TableA varchar(100))
INSERT INTO #TableA VALUES('3:00'),('3:45'),('4:00'),('4:30'),('5:25'),('6:00')

CREATE TABLE #TableB(TableB varchar(100))
INSERT INTO #TableB VALUES('4:17'),('5:28')

SELECT a.TableA,
b.TableB
FROM #TableA AS a
LEFT JOIN #TableB as b
ON a.TableA < b.TableB
WHERE b.TableB BETWEEN SUBSTRING(a.TableA,1,1) and DATEADD(hour,1,SUBSTRING(a.TableA,1,1)+':00')


DROP TABLE #TableB
DROP TABLE #TableA

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page
   

- Advertisement -