我再次发布这个问题,因为项目已经更改,以前的答案没有返回所需的结果。救护车和消防车有紧急情况发生时的调度时间和宣布紧急情况结束时的结束时间。
活动1开始于2021年5月1日10:17:33,结束于2021年1月1日10:33:41。
事件2开始于2021年5月1日11:50:52,结束于2021年1月1日13:18:21。
我想解析从开始到结束的时间量,并在发生时将其放入小时部分。例如事件1从10:17开始,到10:33结束。它将在当天10点的时间里安排16分钟。事件2将在11:00小时部分安排10分钟,在12:00小时部分安排60分钟,在13:00小时部分安排18分钟。将分钟放入事件发生的小时中。
结果应该如下所示。虽然我很灵活。例如,如果卡车的名称不能在结果中返回,这是可以的,因为如果EventID在那里,我可以关联回原始表。
EventID | 救护车 | 事件发生日期事件小时 | 分配的分钟数|||
---|---|---|---|---|---|
1 | Medic10 | <121>10 | 16 | ||
1 | 医疗10 | <121>11 | 10 | ||
2 | Ladder73 | 121 | 11 | 10 | |
2 | Ladder73 | 121 | 12 | >60 | |
2 | Ladder73 | 121 | 13 | 18 | |
3 | 发动机41 | 121 | 13 | 33 | |
3 | 发动机41 | 121 | 14 | 21 | |
医疗83 | 121 | 15 | 32 | ||
医疗83 | |||||
5 | Rescue32 | 121 | 16 | >33 | |
医疗09 | 121 | 23 | 16 | ||
医疗09 | |||||
发动机18 | 121 | 23 | 28 | ||
发动机18 | 122 | 0 | 60 | ||
发动机18 | 122 | 1 | 34 | ||
8 | 救援63 | <122>0 | 35 |
…
CREATE TABLE tempFireEvents
(
EventID VARCHAR(8) NOT NULL,
StartDateTime DATETIME NOT NULL,
EndDateTime DATETIME NOT NULL
)
INSERT INTO tempFireEvents
VALUES
('fire0001', 'november 1, 2018 10:45:00', 'november 2, 2018 11:30:00'),
('fire0002', 'november 1, 2018 11:50:00', 'november 1, 2018 13:10:00'),
('fire0003', 'november 1, 2018 13:20:00', 'november 1, 2018 14:20:00'),
('fire0004', 'november 1, 2018 15:25:00', 'november 1, 2018 16:05:00'),
('fire0005', 'november 1, 2018 16:20:00', 'november 2, 2018 17:00:00'),
('fire0006', 'november 1, 2018 16:20:00', 'november 1, 2018 17:01:00');
select e.*, hr.ld,
60 - case when e.startdatetime > hr.ld then datepart(minute, e.startdatetime) else 0 end
+ case when e.enddatetime < hr.ud then datepart(minute, e.enddatetime)-60 else 0 end as allocatedminutes
from tempFireEvents as e
cross apply
(
select
dateadd(hour, datepart(hour,e.startdatetime)+t.rn-1, cast(cast(e.startdatetime as date) as datetime)) as ld,
dateadd(hour, datepart(hour,e.startdatetime)+t.rn, cast(cast(e.startdatetime as date) as datetime)) as ud,
rn
from
(
-- a tally, max 100 rows .. max 100 hours duration
select top (1+datediff(hour,e.startdatetime,dateadd(minute, -1, e.enddatetime))) row_number() over(order by @@spid) as rn
from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as a(n)
cross join (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as b(n)
) as t
) as hr;