Geek asks and answers

Select all the time (hour / minute) that occurs within a set of small ranges connected by a large range - SQL 2000

I have a table containing index data, with each row having a start and end time. It is trivial to query this table and get a list of index rows whose time intervals refer to the total time period (usually one day from 00:00:00 to 23:59:59).

declare @date datetime, @start datetime, @end datetime

set @date = GetDate() //in production code this is a n input to a stored proc

//the date component of the current date/time, starting at midnight
set @start = DateAdd(dd, DateDiff(dd, 0, @date), 0) 

//one second before midnight the next day
set @end = DateAdd(dd, 1, DateAdd(ss, -1, @start)) 

select idx, start_time, end_time
from index_data
where start_time <= @end
and end_time >= @start
order by start_time

The results will be something like this:

idx         start_time                  end_time
---------------------------------------------------------------
495640      2012-05-03 00:17:13.000     2012-05-03 00:17:45.000
495641      2012-05-03 00:18:20.000     2012-05-03 00:18:51.000
495642      2012-05-03 00:18:55.000     2012-05-03 00:19:31.000
495643      2012-05-03 00:34:08.000     2012-05-03 00:34:28.000
495644      2012-05-03 00:36:21.000     2012-05-03 00:36:41.000
495646      2012-05-03 01:22:21.000     2012-05-03 01:22:38.000
495647      2012-05-03 01:24:38.000     2012-05-03 01:24:55.000
495648      2012-05-03 01:30:11.000     2012-05-03 01:30:29.000
495649      2012-05-03 01:31:23.000     2012-05-03 01:31:39.000
495650      2012-05-03 02:09:57.000     2012-05-03 02:10:59.000
495651      2012-05-03 02:11:00.000     2012-05-03 02:11:00.000
495652      2012-05-03 02:14:25.000     2012-05-03 02:14:42.000
495653      2012-05-03 02:31:09.000     2012-05-03 02:31:25.000
495655      2012-05-03 03:02:32.000     2012-05-03 03:02:51.000
...

I need an efficient query (without cursors or other loops) that will create a result set that provides me with a row for every hour and minute of that day, for which at least one second of this minute falls into at least one index time interval:

hour   min
----------
0      17
0      18
0      19
0      34
0      36
1      22
1      24
1      30
1      31
2      9
2      10
2      11
2      14
2      31
3      02
...

start_time end_time , 60 ( , , , ), ; , start_time 02:20:34 end_time 02:23:43. , 2:20, 2:21, 2:22 2:23, UNIONing start_time end_time.

: MSDE, MSS 2000. , CTE ( ).

+3
1

, .., .

, 1440 DATETIME.

DECLARE
  @date  DATETIME,
  @start DATETIME,
  @end   DATETIME
SELECT
  @date  = GetDate(),
  @start = DateAdd(dd, DateDiff(dd, 0, @date), 0),
  @end   = DateAdd(dd, 1, @start)

SELECT
  minute_lookup.timestamp,
  COUNT(*)                    AS total_records
FROM
  index_data
INNER JOIN
  minute_lookup
    ON  minute_lookup.timestamp >= index_data.start_time - @date
    AND minute_lookup.timestamp <  index_data.end_time   - @date
WHERE
      index_data.start_time < @end
  AND index_data.end_time   > @start
GROUP BY
  minute_lookup.timestamp
ORDER BY
  minute_lookup.timestamp

. .


DateTime

, , -.

, 2012-01-01 00:00:00 2012-01-01 23:59:59. , ?

DateTimes , . FROM 2012-01-01 00:00:00 upto, 2012-01-02 00:00:00

  • x >= '2012-01-01' AND x < '2012-01-02'

, , x , - 1 2012 , .

+2

More articles:


All Articles