How to get the nearest earlier Time

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

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.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources