SQL开始时间结束时间查询

Question

我有这样的桌子

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

数据如下所示，开始时间和结束时间是连续的时间跨度：

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

如前所述：由于您的日期已经是连续的，您可以简单地将开始和结束时间的可能位置统一在一起。

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

-- Intervals where from is included, but to is not
SELECT 'A' Union_Case, Status, @From StartTime, EndTime FROM Table_Status 
    WHERE StartTime < @From AND @From <= EndTime AND EndTime < @To
  UNION 
-- Intervals where @to and @from are incased in the interval
SELECT 'B', Status, @From, @To FROM Table_Status WHERE StartTime < @From AND EndTime >= @To
  UNION  
-- Intervals where @from is before start and @to is after end
SELECT 'C', Status, StartTime, EndTime FROM Table_Status WHERE StartTime > @From AND StartTime <= @To AND EndTime < @To 
  UNION 
-- intervals where @from is before start but @to ends within
SELECT 'D',  Status, StartTime, @To FROM Table_Status WHERE @From <= StartTime AND EndTime > @To
  ORDER BY 3, 4

Answer 2

我面临着类似的情况，我能够解决的方法是：

DECLARE @TimeStamp as DateTimeType
SET @TimeStamp = cast('2016-10-13 00:00:00.000' as datetime)
DECLARE @DayNum as ApsDayOrdinalType = DATEPART(dw,@TimeStamp)
DECLARE @Time as Time = cast(@TimeStamp as time)
DECLARE @DefaultTime as Time = CAST('00:00' as time)

SELECT @DayNum,@Time,@DefaultTime
-- CTEs don't work pretty well with inline data transformation :'(
DECLARE @WorkCenterShifts as table(
  WorkCenter varchar(15),
  ShiftId varchar(15),
  StartDateTime DateTime,
  EndDateTime DateTime
)

INSERT INTO @WorkCenterShifts
SELECT 
  wc
  ,shiftid
  ,CAST(CAST(DateAdd(dd,-(@DayNum - sday),@TimeStamp) as date) as varchar) +' '+ cast(stime as varchar)
  ,CAST(CAST(DateAdd(dd,(@DayNum - eday),@TimeStamp) as date) as varchar) +' '+ cast(etime as varchar)
FROM workcentershiftview 
WHERE wc = @wc
  and @DayNum >= IsNull(sday,1) 
  and @DayNum <= IsNull(eday,7) 

其中“workcentershiftview”返回如下数据：

WC      ShiftId sDay    eDay    sTime   eTime
MXMCE2  2nd9    6       2       16:00   06:30
MXMCE2  1st9.5  6       6       06:30   16:00
MXMCE2  2nd9    5       6       16:00   06:30
MXMCE2  1st9.5  5       5       06:30   16:00
MXMCE2  2nd9    4       5       16:00   06:30

从那里，我终于可以创建一个简单的查询，如下所示：

SELECT * FROM @WorkCenterShifts
WHERE @TimeStamp between StartDateTime and EndDateTime

得到这样的结果：

WorkCencer    ShiftId     StartDateTime               EndDateTime
MXMCE2        2nd9        2016-10-12 16:00:00.000     2016-10-13 06:30:00.000

希望这对其他人有用：）

Answer 3

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

>

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

返回具有以下内容的组合结果集：

• 完全在您的日期起止参数范围内的所有范围
• 与起始/结束参数开头部分重叠的计算范围
• 与“从/到”参数结尾部分重叠的计算范围

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

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