CODEWITHSUNDEEP
sqlsql-server
Ervis Trupja
Ervis Trupja

Reputation: 2800

SQL - Modify query to get values from an Interval of Time instead of a fixed Time

I have a query which return an exact time span:

SELECT ScanDate FROM tTimes WHERE personId = 5

The value for example could be:

2016-08-10 16:26:42.000

BY using the query below:

SELECT executedTime FROM tTimetable as tc
WHERE tc.lastExecutedTime IN (SELECT ScanDate FROM tTimes WHERE personId =5)

I would get:

SELECT executedTime FROM tTimetable as tc
    WHERE tc.lastExecutedTime  IN (2016-08-10 16:26:42.000)

But, I would like to get values from 

SELECT executedTime FROM tTimetable as tc
        WHERE tc.lastExecutedTime  IN (between 2016-08-10 16:16:42.000 and  2016-08-10 16:36:42.000)So everything which is between the `2016-08-10 16:26:42.000-10sec` and `2016-08-10 16:26:42.000+10secs`

How can I archieve this result?

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I would approach this using exists:

SELECT executedTime
FROM tTimetable as tc
WHERE EXISTS (SELECT 1
              FROM tTimes
              WHERE personId = 5 AND
                    tc.lastExecutedTime BETWEEN DATEADD(second, -10, ScanDate) AND DATEADD(second, 10, ScanDate)
             );

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81990

If I understand your question

 SELECT executedTime 
  FROM  tTimetable as tc
  Join (
        Select DateR1 = DateAdd(SECOND,-10,ScanDate)
              ,DateR2 = DateAdd(SECOND,10,ScanDate) 
         FROM tTimes WHERE personId = 5
       ) B
   on  tc.lastExecutedTime between DateR1 and DateR2

Upvotes: 3

Related Questions