时间戳

首页 > 解决方案 > 在 SQL Server 中使用存储过程提取值

sql - 在 SQL Server 中使用存储过程提取值

问题描述

我需要您的帮助才能在 SQL Server (v12.0.6024.0) 中创建视图。我的一个客户有一个表格，其中一些时间段以这种格式保存：

ID ID_EVENT 时隙
1000 24 08:30:00.0000
1000 24 09:00:00.0000
1000 24 09:30:00.0000

每个时间段持续 30 分钟，上例表示 ID 为 24 的事件（保存在另一个表中）从 8:30 持续到 10:00（第 3 个时间段从 9:30 开始，持续 30 分钟，因此在 10:00 结束）。问题是在某些情况下时间值不是连续的，中间可能会有停顿，所以我会有这样的事情：

ID ID_EVENT 时隙
1000 24 08:30:00.0000
1000 24 09:00:00.0000
1000 24 09:30:00.0000
1000 24 11:30:00.0000
1000 24 12:00:00.0000
1000 24 12:30:00.0000

在这种情况下，ID 为 24 的事件从 8:30 持续到 10，停止，然后从 11:30 到 13:00 再次开始。我被要求为外部开发人员准备一个视图，其中我不仅必须报告事件开始的时间（在我的示例中，8:30）和它永久停止的时间（在我的示例中为 13:00），而且还有暂停开始的时间（在我的示例中为 10:00）和暂停完成的时间（在我的示例中为 11:30）。

我对前两个值没有问题，但我不知道如何提取另外两个值。我认为我们可以考虑当 2 个时隙不连续时发生暂停，同一事件不能有多个时段。我想我需要一个程序，但很难写；我需要有一个观点说

ID ID_EVENT 时间1 时间2 时间3 时间4
1000 24 08:30:00.0000 10:00:00.0000 11:30:00.0000 13:00:00.0000

有什么帮助吗？

标签： sqlsql-serverdatetimestored-proceduresgaps-and-islands

解决方案

declare @t table(ID int, ID_EVENT int, TimeSlot time)
insert into @t
values
(1000,  24, '08:30:00.0000'),
(1000,  24, '09:00:00.0000'),
(1000,  24, '09:30:00.0000'),
--
(1000,  24, '11:30:00.0000'),
(1000,  24, '12:00:00.0000'),
(1000,  24, '12:30:00.0000'),
--
(1000,  24, '15:00:00.0000'),
(1000,  24, '15:30:00.0000'),
(1000,  24, '16:00:00.0000'),
--
(1000,  25, '15:30:00.0000'),
(1000,  25, '16:30:00.0000');


select Id, ID_EVENT, 
    min(TimeSlot) as StartTimeSlot, 
    dateadd(minute, 30, max(TimeSlot)) as EndTimeSlot
from 
(
    select *,
    datediff(minute, '00:00:00', Timeslot)/30 - row_number() over(partition by Id, ID_EVENT order by TimeSlot) as grpid
    from @t
) as t
group by Id, ID_EVENT, grpid;



--first two groups per event&id row
select Id, ID_EVENT,
--1
min(case when grpordinal = 1 then TimeSlot end) as StartSlot1,
dateadd(minute, 30, max(case when grpordinal = 1 then TimeSlot end)) as EndSlot1,
--2
min(case when grpordinal = 2 then TimeSlot end) as StartSlot2,
dateadd(minute, 30, max(case when grpordinal = 2 then TimeSlot end)) as EndSlot2
from 
(
    select Id, ID_EVENT, TimeSlot,
        dense_rank() over(partition by Id, ID_EVENT order by grpid) as grpordinal
    from 
    (
        select *,
        datediff(minute, '00:00:00', Timeslot)/30 - row_number() over(partition by Id, ID_EVENT order by TimeSlot) as grpid
        from @t
    ) as t
) as src
--where grpordinal <= 2 --not really needed
group by Id, ID_EVENT;



--!!!!only when there are max two groups/periods
--if there could be more than 2 periods this will not work
select Id, ID_EVENT,
--1
min(case when grpid = 0 then TimeSlot end) as StartSlot1,
dateadd(minute, 30, max(case when grpid = 0 then TimeSlot end)) as EndSlot1,
--2
min(case when grpid <> 0 then TimeSlot end) as StartSlot2,
dateadd(minute, 30, max(case when grpid <> 0 then TimeSlot end)) as EndSlot2
from
(
select *,
/*
    1
    + datediff(minute, '00:00:00', Timeslot)/30 - row_number() over(partition by Id, ID_EVENT order by TimeSlot) 
    - datediff(minute, '00:00:00', min(Timeslot) over(partition by Id, ID_EVENT)) /30
*/
    1
    + datediff(minute, min(Timeslot) over(partition by Id, ID_EVENT), TimeSlot)/30  
    - row_number() over(partition by Id, ID_EVENT order by TimeSlot)
    as grpid --1st groupid is always 0
from @t
) as t
group by Id, ID_EVENT;

推荐阅读