CODEWITHSUNDEEP
sqldatabasesql-server-2005t-sqlstored-procedures
Gullu
Gullu

Reputation: 3529

Get active rows between from and to date range inclusive of boundary

Table testTable has 4 columns

Given two dates say fromDate and toDate, how do I get the first session that started on or after fromDate and the last session that ended on or before toDate. Tricky condition is that if a session is in progress and it's start date >= fromDate we need this. I am guessing it might not be possible to get both the min and max session id in one sql statement and keep the code readable and easy to maintain. Two separate sql statements is ok. one to get min and one to get max. Then I can query rows using between min and max.

This last statement explains it in a different way. Get all sessions that started or was running and ended or was running between from/to dates thank you

Upvotes: 1

Views: 1863

Answers (2)

Justin Drury
Justin Drury

Reputation: 748

After considering your edits and the comments regarding BETWEEN, this should give you the result set you need. It will pull any record where SessionIsRunning = true as well as sessions thats started AND ended in the date range.

SELECT * FROM testTable tt
WHERE (tt.started >= fromDate AND tt.started < DATEADD(DAY, 1, toDate)
   AND tt.ended >= fromDate AND tt.ended < DATEADD(DAY, 1, toDate))
   OR SessionIsRunning = true
ORDER BY tt.sessionid

Upvotes: 2

Kevin Stricker
Kevin Stricker

Reputation: 17388

Getting only the first and last value in a single query (because your question made me curious how to actually write this, even though that doesn't appear to be what you're really asking):

SELECT * FROM
(SELECT TOP 1 * FROM ATable ORDER BY AColumn ASC) first
UNION
SELECT * FROM
(SELECT TOP 1 * FROM ATable ORDER BY AColumn DESC) last

Upvotes: 1

Related Questions