SQL开始时间结束时间查询

Question

SQL开始时间结束时间查询

5

我有这样的一个表格

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)，这一点比较棘手，我已经为此奋斗了整整一天。请给予指导。

非常感谢您的帮助！

- Username Not Exist

似乎分割字符串可以解决您的问题。您尝试了什么，它现在是如何工作的？ - Rick S

你的意思是要拆分时间段吗？我使用了“SELECT * FROM Table_Status WHERE (EndTime >= '2007-11-17 00:00:00.000' AND StartTime <= '2007-11-17 23:59:59:000')”，但这并不是拆分。 - Username Not Exist

你不应该只是选择并更改值，要从逻辑上考虑输出。你的第一行是参数#From处的状态，然后每一行都是状态更改，直到结束时间>参数#To。（不能使用@符号，因为它会认为我在引用用户） - pancho018

你正在使用哪个版本的 Microsoft SQL Server？ - Rabbit

SQL 2008 R2，你们能解释一下该如何分割吗？@Rick S - Username Not Exist

显示剩余2条评论

3个回答

1

我曾经遇到过类似的情况，我解决它的方法如下：

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

希望这对其他人有用 :)

- Violantex

0

如前面的评论所提到的：由于您的日期已经连续，您可以简单地将起始时间和结束时间与可能的出发地点和目的地点进行联合。

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

- Jan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ehz · Accepted Answer

获取您想要的结果集有两部分。

将具有相同“状态”值的所有连续日期合并在一起
返回一个组合结果集，具有：
- 完全落在您的日期从/到参数内的所有范围
- 与您的从/到参数开头部分重叠的计算范围
- 与您的从/到参数结尾部分部分重叠的计算范围

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

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