CODEWITHSUNDEEP
sql-serverdatetimeselect
ahmd0
ahmd0

Reputation: 17293

SQL Server select statement for a date range?

I have a database with thousands of entries, that have two date/time fields, let's call them In and Out:

In                                      Out
2011-06-16 13:45:11.000    2011-06-16 13:49:12.000
2011-06-16 13:51:31.000    2011-06-16 14:23:17.000
2011-06-16 14:51:54.000    2011-06-16 14:58:43.000

and so on...

On the input I have a time frame (timeIn and timeOut). On the output I need to know the cumulative number of seconds between 'In' and 'Out' times in each of the records that fall into this time frame. It's easier to illustrate with this daigram:

http://i150.photobucket.com/albums/s99/dc2000_bucket/sql_graph.jpg

Taking names from the diagram, I need to know "Time Interval 1 (in seconds)" + "Time Interval 2 (in seconds)" + "Time Interval 3 (in seconds)". How to compose such SQL statement?

Upvotes: 2

Views: 184

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

There is no GREATEST() or LEAST() function in SQL-Server, or it would be like like this:

SELECT LEAST(t.timeOut, @TimeOut) - GREATEST(t.timeIn,@TimeIn) 
FROM tableX AS t
WHERE t.timeIn <= @TimeOut
  AND @TimeIn <= t.timeOut

Using CASE :

SELECT DATEDIFF( second
               , CASE WHEN t.timeIn <= @TimeIn
                        THEN @TimeIn
                        ELSE t.timeIn
                 END
               , CASE WHEN t.timeOut <= @TimeOut
                        THEN t.timeOut
                        ELSE @TimeOut
                 END 
               )
       AS TimeInterval 
FROM tableX AS t
WHERE t.timeIn <= @TimeOut
  AND @TimeIn <= t.timeOut
ORDER BY t.timeIn

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select sum(datediff(second, [In], case when @TimeOut < [Out] 
                                    then @TimeOut 
                                    else [Out] 
                                  end))
from YourTable
where [In] between @TimeIn and @TimeOut

Or like this if you want to include intervals that ends within @TimeIn - @TimeOut as well.

select sum(datediff(second, case when @TimeIn < [In] 
                              then [In]
                              else @TimeIn
                            end, 
                            case when @TimeOut < [Out] 
                              then @TimeOut 
                              else [Out] 
                            end))
from YourTable
where [In] <= @TimeOut and 
      [Out] >= @TimeIn

Upvotes: 2

Related Questions