SQL开始时间结束时间查询

Question

所以我有这样的 table

CREATE TABLE Table_Status
(
Status VARCHAR(10) NOT NULL,
StartTime DATETIME NOT NULL, 
EndTime DATETIME NOT NULL
); 

和数据看起来像这样， StartTime 和 Endtime 是连续的时间跨度 :

Status1 2007-10-16 18:38:25.000 2007-10-17 05:30:22.000 
Status2 2007-10-17 05:30:22.000 2007-10-17 18:48:46.000
Status2 2007-10-17 18:48:46.000 2007-10-17 21:48:46.000
Status1 2007-10-17 21:48:46.000 2007-10-18 08:11:59.000

所以想法是到 在任何时间段内选择 * , 如果用户传递两个参数

SET @From = '2007-10-17 00:00:00.000'
SET @To = '2007-10-17 23:59:59.000' 

不知何故，它应该像这样返回表:

Status1 2007-10-17 00:00:00.000 2007-10-17 05:30:22.000
Status2 2007-10-17 05:30:22.000 2007-10-17 21:48:46.000
Status1 2007-10-17 21:48:46.000 2007-10-17 23:59:59.000

你看，棘手的部分是将原始时间跨度缩短为用户定义的时间跨度(@From - @To)，我一整天都在苦苦挣扎。请指教。

预先非常感谢您!!!

Answer 1

有两个部分可以获得您正在寻找的结果集。

将具有相同“状态”值的所有连续日期合并在一起

返回具有以下组合的结果集:

完全在您的日期范围内的所有范围从/到参数

计算的范围与起始/结束参数的开头部分重叠

与起始/终止参数的结尾部分重叠的计算范围

对于日期合并，您可以查看这两个链接以了解合并连续日期范围的方法:

http://www.sqlservercentral.com/Forums/Topic1364849-392-1.aspx

http://sqlmag.com/blog/solutions-packing-date-and-time-intervals-puzzle

我使用了那里最易读的示例之一。但是，如果您正在寻找性能更高的查询，则可以查看其他查询。

以下是返回示例中的结果集的示例查询:

合并具有相同状态的连续日期行

with all_times (time_type,date_range_part,status) as (
select 'start',
       starttime,
       status
from table_status
union all
select 'end',
       endtime,      
       status
from table_status),

ordered_starts as (
select date_range_part,
       status,
       row_number() over(partition by status order by date_range_part, time_type desc) as rnboth,
       2*(row_number() over(partition by status,time_type order by date_range_part))-1 as rnstartend
from all_times),

ordered_ends as (
select date_range_part,
       status,
       row_number() over(partition by status order by date_range_part desc,time_type) as rnbothrev,
       2*(row_number() over(partition by status,time_type order by date_range_part desc))-1 as rnstartendrev
from all_times),

starts as (
select date_range_part,
       status,
       row_number() over(partition by status order by date_range_part) as rn
from ordered_starts
where rnboth=rnstartend),

ends as (
select date_range_part,
       status,
       row_number() over(partition by status order by date_range_part) as rn
from ordered_ends
where rnbothrev=rnstartendrev)

select 
s.status,
s.date_range_part [start_time],
e.date_range_part [end_time]
into #table_status_merged
from starts s
inner join ends e on e.status=s.status and e.rn=s.rn and s.date_range_part<=e.date_range_part
order by s.date_range_part;

返回一个结果集，该结果集的所有范围都完全在您的日期参数内、计算的开始范围和计算的结束范围

declare @from datetime
declare @to datetime

set @from = '2007-10-17 00:00:00.000'
set @to = '2007-10-17 23:59:59.000'

select
[status],
@from,
end_time
from #table_status_merged
where start_time < @from
and end_time <= @to
union all
select
[status],
start_time,
end_time
from #table_status_merged
where start_time >= @from
and end_time <= @to
union all
select
[status],
start_time,
@to
from #table_status_merged
where start_time >= @from
and end_time > @to

drop table #table_status_merged