在SQL Server中使用存储过程提取值 [英] Extract values with stored procedure in SQL Server

问题描述

我需要您的帮助才能在SQL Server(v12.0.6024.0)中创建视图.我的一位客户有一张表格，其中某些时隙以这种格式保存:

I need your help to create a view in SQL Server (v12.0.6024.0). One of my customer has a table in which some time slot are saved in this format:

<身体>

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(第三个时隙的开始时间为9:30，持续了30分钟，因此其结束时间为10:00).问题是，在某些情况下，时间值不是连续的，中间可能会有暂停，所以我会遇到类似这样的情况:

Every time slot lasts 30 minutes, the example above means that event with ID 24 (saved in another table) lasted form 8:30 to 10:00 (3rd slot started at 9:30, lasted 30 minutes so it finished at 10:00). The problem is that in some cases the time values are not consecutive and there may be a pause in the middle, so I would have something like this:

<身体>

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).

In this case event with ID 24 lasted from 8:30 to 10, stopped, then started again from 11:30 to 13:00. I have been asked to prepare a view for an external developer in which I have to report not only the time the event started (in my example, 8:30) and the time it stopped for good (in my example 13:00) but also the time the pause started (in my example 10:00) and the time the pause finished (in my example 11:30).

我对前两个值没有问题，但是我不知道如何提取其他两个值.我认为我们可以考虑在两个时隙不是连续的情况下发生暂停，同一事件的周期不能超过一个.我想我需要一个程序，但是发现很难编写它.我必须要说一个观点

I have no problem with the first 2 values but I don't know how to extract the other two. I think we can consider a pause happening when 2 time slots are not consecutive, there cannot be more than periods for the same event. I suppose I need a procedure but find it difficult to write it; I need to have a view that says

<身体>

ID	ID_EVENT	Time1	Time2	Time3	Time4
1000	24	08:30:00.0000	10:00:00.0000	11:30:00.0000	13:00:00.0000

有帮助吗?

推荐答案

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;

这篇关于在SQL Server中使用存储过程提取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！