在时间范围内查找事件

2021-09-10 00:00:00 tsql sql-server

试图找出适用于多种情况的查询.简而言之，数据可能处于两种情况之一.假设我正在寻找在给定时间范围内发生的事件的 record_id:6/26/2012 10:00AM 和 6/27/2012 11:00AM 数据库中的记录可能如下所示:

Trying to figure out a query that could work for multiple situations. In a nutshell the data can be in one of twosituations. Lets say I'm looking for the record_id for events happened during a given time frame: 6/26/2012 10:00AM and 6/27/2012 11:00AM The records can look like this in the database:

Record    Event     Time
1         Start     6/26/2012 10:05AM
1         End       6/26/2012 10:45AM
2         Start     6/26/2012 09:55AM
2         End       6/26/2012 11:05AM

获取记录 1 很容易，只需使用 between 函数，但我在试图找出一个查询以返回记录 1 和 2 时遇到困难.

Getting record 1 is easy, just using the between function, but I'm stumbling trying to figure out a query to return both records 1 and 2.

建议?

推荐答案

您可以简单地扩大范围，但这可能会带回与您预期不同的记录.

You can simply widen the range, but this may bring back different records than you intend.

SELECT  RECORD, EVENT, TIME 
FROM SO_RecordEvent AS R
WHERE TIme BETWEEN  '6/26/2012 9:55AM'  AND '6/26/2012 11:06AM'

这将返回具有该范围内开始或结束时间的任何记录的所有记录，包括落在该范围之外的相关记录(换句话说，只有一次在该范围内的记录 - 但在此之前或结束之前开始)after - 它仍然会显示它之外的开始或结束时间)并允许您缩短范围.

This will return all records for anything that has either a start or end time within the range, including the associated records falling outside of the range (In other words records that only have one time in the range - but began before or ended after - it will still show the start or end time outside of it) and allow you you shorten your range.

 ;WITH X AS 
 (
    SELECT RECORD,EVENT,TIME FROM SO_RecordEvent 
 )
 SELECT R.RECORD,R.EVENT,R.TIME 
 FROM SO_RecordEvent AS R
 INNER JOIN X ON R.Record = X.Record
 WHERE X.TIme BETWEEN  '6/26/2012 10:05AM'  AND '6/26/2012 11:05AM'
 GROUP BY R.RECORD,R.EVENT,R.TIME

但我认为您可能真的想要这样的东西，它确实为您提供了在那段时间开始的所有内容，即使它在范围之外开始和结束，就像您的记录 2 示例一样.

But I think you may really want something like this, which truly gives you everything that was started during that time, even if it started AND ended outside of the range, as is your record 2 example.

改变逻辑 - 决定以这种方式思考它而不是解决情况 - 任何在开始和结束内开始的事情，在开始和结束内结束的任何事情，以及在开始和结束之后开始的任何事情.我认为这涵盖了在此期间运行的任何内容(开始之前结束之内，开始和结束之内，开始之内和结束之后，以及开始之前和之后结束)

Changed logic - instead of addressing situations decided to think about it this way - anything that started within start and end, anything that ended within start and end, and anything that started before and ended after. I think this covers anything that runs during this time (starts before ends within, starts and ends within, starts within and ends after, and starts before and ends after) 

  SELECT X.RECORD,X.TIME AS STARTTIME,Y.TIME AS ENDTIME 
  FROM SO_RecordEvent AS X
  INNER JOIN SO_RecordEvent Y ON Y.Record = X.Record AND Y.EVENT = 'END'
  WHERE  X.EVENT = 'START'
  AND 
  ((X.TIME >= '6/26/2012 10:00AM' AND X.TIME <= '6/26/2012 11:00AM')
  OR (Y.TIME >= '6/26/2012 10:00AM' AND Y.TIME <= '6/26/2012 11:00AM')
  OR (X.TIME <= '6/26/2012 10:00AM' AND Y.TIME >= '6/26/2012 11:00AM'))

要使用的变量:

 DECLARE @START datetime, @END datetime
 SET @START = '6/26/2012 10:00AM'
 SET @END = '6/26/2012 11:00AM'

   SELECT X.RECORD,X.TIME AS STARTTIME,Y.TIME AS ENDTIME 
   FROM SO_RecordEvent AS X
   INNER JOIN SO_RecordEvent Y ON Y.Record = X.Record AND Y.EVENT = 'END'
   WHERE  X.EVENT = 'START'
   AND 
  ((X.TIME >= @START AND X.TIME <= @END)
  OR (Y.TIME >= @START AND Y.TIME <= @END)
  OR (X.TIME <= @START AND Y.TIME >= @END))

相关文章