SQL批量统计多事件时段内消防车调用数的实现需求

Hey there! Since you're new to SQL, let's work through this problem to automate your hourly statistics for all incidents in your temp table—no more manually updating incident IDs one by one.

First, Let's Clarify the Requirements #

We need to:

• For each incident in your temp table, get its start and end time
• Generate an hourly time sequence covering the entire duration of each incident
• For each hour, count:
  • Number of P1/P2 fire trucks assigned to the current incident
  • Number of P1/P2 fire trucks assigned to other incidents during the same hour
• Output results grouped by incident and hour, matching your desired format

Assumptions About Your Temp Table #

Let's assume your temp table (let's call it #Incidents) has these columns—adjust the names if yours differ:

• IncidentRef: Unique identifier for each incident (like 'A', 'B', or numeric IDs like 1704009991)
• IncidentStart: The start datetime of the incident
• IncidentEnd: The end datetime of the incident

Full SQL Code #

First, here's how to structure the query (with comments to explain each part):

-- Step 1: If you don't already have a temp table, create one (replace with your actual data)
CREATE TABLE #Incidents (
    IncidentRef VARCHAR(10), -- Change to INT if your incident IDs are numeric
    IncidentStart DATETIME,
    IncidentEnd DATETIME
);

-- Optional: Insert sample data (remove this in production and use your actual data)
INSERT INTO #Incidents VALUES
('A', '2018-05-03 01:00:00', '2018-05-03 04:00:00'),
('B', '2017-03-01 09:00:00', '2017-03-01 12:00:00');

-- Step 2: Recursive CTE to generate hourly time slots for each incident
WITH IncidentHourly AS (
    -- Base case: Get the first hour of each incident (truncated to the nearest hour)
    SELECT 
        IncidentRef,
        DATEADD(HOUR, DATEDIFF(HOUR, 0, IncidentStart), 0) AS HourStart,
        IncidentEnd
    FROM #Incidents

    UNION ALL

    -- Recursive case: Generate the next hour until we pass the incident's end time
    SELECT 
        IncidentRef,
        DATEADD(HOUR, 1, HourStart),
        IncidentEnd
    FROM IncidentHourly
    -- Keep generating hours until we reach the hour after the incident ends
    WHERE DATEADD(HOUR, 1, HourStart) < DATEADD(HOUR, DATEDIFF(HOUR, 0, IncidentEnd) + 1, 0)
)

-- Step 3: Join with mobilizations data and calculate counts
SELECT 
    ih.IncidentRef,
    CONVERT(VARCHAR(20), ih.HourStart, 120) AS DateTime,
    -- Count trucks assigned to the current incident
    COUNT(CASE WHEN m.MB_IN_REF = ih.IncidentRef THEN 1 END) AS [At Incident],
    -- Count trucks assigned to other incidents
    COUNT(CASE WHEN m.MB_IN_REF <> ih.IncidentRef THEN 1 END) AS [Other Incident]
FROM IncidentHourly ih
-- Left join to ensure we keep all hourly slots even if no trucks were mobilized
LEFT JOIN MB_MOBILISATIONS m
    ON m.MB_SEND < ih.HourStart 
    AND m.MB_LEAVE > ih.HourStart
    AND (m.MB_CALL_SIGN LIKE '%P1' OR m.MB_CALL_SIGN LIKE '%P2')
GROUP BY ih.IncidentRef, ih.HourStart
ORDER BY ih.IncidentRef, ih.HourStart
-- Disable recursion limit (needed for incidents spanning more than 100 hours)
OPTION (MAXRECURSION 0);

Key Explanations #

• Recursive CTE (IncidentHourly): This generates every hour between the start and end of each incident. It starts with the first hour of the incident, then keeps adding one hour at a time until it covers the entire incident duration.
• Left Join: We use LEFT JOIN instead of inner join so that even hours with no fire truck mobilizations show up in the results (with counts of 0).
• Conditional Counting: Instead of using nested subqueries (which can be slow for large datasets), we use COUNT(CASE...) to calculate both counts in a single pass over the MB_MOBILISATIONS table. This is more efficient, especially with 300+ incidents.
• Time Truncation: DATEADD(HOUR, DATEDIFF(HOUR, 0, IncidentStart), 0) converts the incident start time to the nearest preceding hour (e.g., 2018-05-03 01:45 becomes 2018-05-03 01:00).

Notes for Adjustment #

• If your incident IDs are numeric (like 1704009991), change the IncidentRef column type in the temp table to INT.
• Double-check that MB_IN_REF is the correct column linking mobilizations to incidents, and that MB_SEND/MB_LEAVE represent the correct time window for when a truck is active.
• If your temp table has a different name, replace #Incidents with your actual temp table name.

内容的提问来源于stack exchange，提问作者Clare Nolan