SQL - 按时间段(已完成)计算事件，但包含另一个变量

Question

我对SQL很陌生，如果代码有点邋遢，我很抱歉。

基本上，我每小时都会创建一个正在使用的消防车的数量，我已经这样做了，并且该位有效。所以我统计了过去五年的情况。排序。 但是现在我想为一组特定的事件(大约 300 个(运行它，显示该事件每小时有多少个引擎，以及同时有多少其他引擎在使用，但在其他地方。

我的基本工作代码(我从 https://stackoverflow.com/a/43337534/5880512 修改的(如下。它只计算定义时间的所有 P1 和 P2 动员。

DECLARE @startdate datetime = '2018-05-03 00:00:00'
DECLARE @enddate datetime = '2018-05-05 00:00:00'
;with cte as
(
select @startdate startdate
union all
select DATEADD(minute, 60, startdate) 
FROM cte
WHERE DATEADD(minute, 60, startdate) < @enddate
)
select convert(varchar(20), startdate, 120) as CreationTime, (select count(*) FROM MB_MOBILISATIONS WHERE MB_SEND < startdate and MB_LEAVE > startdate And (MB_CALL_SIGN Like '%P1' Or MB_CALL_SIGN Like '%P2')) as Count
from cte
option (maxrecursion 0)

为了针对特定事件拆分这些内容，我可以将事件引用放入 where 子句中，一个为 =，以便它将在该事件中为我提供引擎，一个作为<>，以便为我提供其余部分。这个位也有效。

select convert(varchar(20), startdate, 120) as CreationTime, (select count(*) FROM MB_MOBILISATIONS WHERE MB_SEND < startdate and MB_LEAVE > startdate And (MB_CALL_SIGN Like '%P1' Or MB_CALL_SIGN Like '%P2') and MB_IN_REF = 1704009991) as 'At Incident'
,   select convert(varchar(20), startdate, 120) as CreationTime, (select count(*) FROM MB_MOBILISATIONS WHERE MB_SEND < startdate and MB_LEAVE > startdate And (MB_CALL_SIGN Like '%P1' Or MB_CALL_SIGN Like '%P2') and MB_IN_REF <> 1704009991) as 'Other Incident'

我无法解决的一点是使它适用于多个事件，而不必在所有 300 个事件的 where 子句中手动更改事件引用。 我想使用的事件引用将存储在临时表中。理想情况下，我希望它选择一个 ID，从该事件的开始和结束开始设置变量@startdate和@enddate，然后在该事件持续时间内进行每小时计数。

希望结果看起来像这样

IncidentRef DateTime                At Incident     Other Incident
A           2018-05-03 1:00         4               2
A           2018-05-03 2:00         7               3
A           2018-05-03 3:00         5               3
A           2018-05-03 4:00         2               4
B           2017-03-01 9:00         7               2
B           2017-03-01 10:00        8               3
B           2017-03-01 11:00        6               1
B           2017-03-01 12:00        4               2

我希望这是有道理的。 谢谢:)

Answer 1

使用类似这样的内容将搜索范围限制为较小的列表。 我刚刚添加并引用了另一个带有过滤器的 CTE。如果要参数化列表，则需要一种不同的方法，例如先将这些 id 值存储在另一个表中。

with cte as (
select @startdate startdate
union all
select dateadd(minute, 60, startdate) 
from cte
where dateadd(minute, 60, startdate) < @enddate
), mobi as (
select * from MB_MOBILISATIONS
where MB_IN_REF in (<insert list here>)
)
select convert(varchar(20), startdate, 120) as CreationTime, m."Count"
from cte cross apply (
select count(*) as "Count" from mobi
where MB_SEND < startdate and MB_LEAVE > startdate and
(MB_CALL_SIGN like '%P1' or MB_CALL_SIGN like '%P2')
) m;

我继续重写了您的标量子查询，但我想这只是个人喜好。