CODEWITHSUNDEEP
sqlt-sql
Kyle Brandt
Kyle Brandt

Reputation: 28407

T-SQL Query where DateTime Range WHERE conditions are Generate by Another Query

How do I write a T-SQL query where the filter conditions of datetime ranges are generated from another query.

For Example Query A would Return:

StartTime                   EndTime
2011-07-06 04:05:42.137     2011-07-06 04:05:58.503
2011-07-06 04:25:51.103     2011-07-06 04:26:07.017
2011-07-06 04:55:56.240     2011-07-06 04:56:04.480
...

How would I return all results that are within the starttime and endtime pairs?

Upvotes: 2

Views: 850

Answers (4)

Randy
Randy

Reputation: 16677

Join your other table to this query.

Then provide extra AND clauses to match the dates.

Provide table structures or query in use for more help.

Upvotes: 0

Amir Ismail
Amir Ismail

Reputation: 3883

Return these pairs into a temporary table then join the temp table with your main table.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

This WHERE clause will return all records that overlap with a specific window of time.

WHERE
      StartTime < myEndTimeLimit
  AND EndTime   > myStartTimeLimit

Note: Depending on whether your endTimes are inclusive (up to and Including that moment) or excluse (Everything Before that moment) you may need <= and >= rather than < and >.


Equally, this can be part of a join...

  TableA
INNER JOIN
  TableB
    ON  <some matching condition>
    AND TableA.StartTime < TableB.EndTime
    AND TableA.EndTime   > TableB.StartTime

(TableB could be a sub-query)


Note: This type of query is Exceptionally poor in terms of optimisation with an Index.

If you have a year's worth of data, and you look for an overlap with "today", the first condition (A.Start < B.End), nearly all of the records are returned, and then scanned to match with (A.End > B.Start).

The simplest optimisation is to "know" the maximum length of any window of time. If all entries are ALWAYS less than 30 days, you could do the following (Amend for your RDBMS's notation).

  TableA
INNER JOIN
  TableB
    ON  <some matching condition>
    AND TableA.StartTime <  TableB.EndTime
    AND TableA.StartTime >= TableB.StartTime - 30
    AND TableA.EndTime   >  TableB.StartTime

This now gives a 30 day range in which TableA.StartTime could exist, giving much better performance over time.

Upvotes: 2

IAmTimCorey
IAmTimCorey

Reputation: 16757

To use the results from one query to filter the other, it would look something like this:

SELECT StartTime, EndTime
FROM LogEntries
WHERE StartTime > (SELECT Min(TimeColumn) FROM Times)
AND EndTime < (SELECT Max(TimeColumn) FROM Times)

If you want to be more complex, you could create a join on the two tables so that you could pull values from the "Times" table or query statement.

Upvotes: 0

Related Questions