基于SSRS的生产批次运行/停机时长报表开发及更新咨询

Hey there! Since you're new to SQL and working on an SSRS report in Visual Studio 2010 to calculate batch run/downtime from timestamp events, let's walk through this step by step—nice work picking up these skills, by the way!

Before diving into code, we need to map those 1-4 Event IDs to actual batch states. For example, I’ll assume:

• Event ID 1 = Batch starts running
• Event ID 2 = Batch pauses (downtime begins)
• Event ID 3 = Batch resumes running
• Event ID 4 = Batch finishes entirely

If your IDs represent different actions (like maybe 1 = shutdown, 2 = startup), just adjust the logic below to match your real workflow.

We’ll use SQL window functions (specifically LEAD()) to pair each event with the next one in the same batch—this is how we’ll get the time between states. Here’s a query you can adapt to your table:

-- First, we'll get each event plus the next event in the batch sequence
WITH BatchEventSequence AS (
    SELECT
        BatchID,
        EventID,
        Timestamp,
        -- Grab the next event's ID for the same batch
        LEAD(EventID) OVER (PARTITION BY BatchID ORDER BY Timestamp) AS NextEventID,
        -- Grab the next event's timestamp
        LEAD(Timestamp) OVER (PARTITION BY BatchID ORDER BY Timestamp) AS NextTimestamp
    FROM YourBatchTable -- Replace this with your actual table name!
)
-- Now calculate duration for each state interval
SELECT
    BatchID,
    -- Calculate time in seconds (swap SECOND for MINUTE/HOUR if you need)
    CASE
        -- Run time from start/pause-resume to next pause/finish
        WHEN EventID IN (1, 3) AND NextEventID IN (2, 4) 
            THEN DATEDIFF(SECOND, Timestamp, NextTimestamp)
        -- Downtime from pause to resume
        WHEN EventID = 2 AND NextEventID = 3 
            THEN DATEDIFF(SECOND, Timestamp, NextTimestamp)
    END AS DurationInSeconds,
    -- Label if this is run time or downtime
    CASE
        WHEN EventID IN (1, 3) THEN 'Run Time'
        WHEN EventID = 2 THEN 'Downtime'
    END AS DurationType
FROM BatchEventSequence
WHERE NextTimestamp IS NOT NULL -- Ignore the last event (no next state to compare to)
-- Optional: Add filters here for date ranges or specific batches
-- AND Timestamp BETWEEN '2024-01-01' AND '2024-01-31'
-- AND BatchID = 123

Quick breakdown for SQL newbies: #

• PARTITION BY BatchID: Groups all events by their 3-digit batch number, so we only compare events within the same batch.
• ORDER BY Timestamp: Makes sure we process events in the order they happened.
• LEAD(): Pulls the next event’s ID and timestamp right after the current one—super handy for sequence-based calculations.
• DATEDIFF: Calculates the time between two timestamps. I used seconds here, but you can use MINUTE or HOUR if you want larger units.

If you want total run/downtime per batch (instead of individual intervals), tweak the query to sum the durations and format it into readable time (HH:MM:SS):

WITH BatchEventSequence AS (
    SELECT
        BatchID,
        EventID,
        Timestamp,
        LEAD(EventID) OVER (PARTITION BY BatchID ORDER BY Timestamp) AS NextEventID,
        LEAD(Timestamp) OVER (PARTITION BY BatchID ORDER BY Timestamp) AS NextTimestamp
    FROM YourBatchTable
),
BatchIntervalDurations AS (
    SELECT
        BatchID,
        CASE
            WHEN EventID IN (1, 3) AND NextEventID IN (2, 4) 
                THEN DATEDIFF(SECOND, Timestamp, NextTimestamp)
            WHEN EventID = 2 AND NextEventID = 3 
                THEN DATEDIFF(SECOND, Timestamp, NextTimestamp)
        END AS DurationInSeconds,
        CASE
            WHEN EventID IN (1, 3) THEN 'Total Run Time'
            WHEN EventID = 2 THEN 'Total Downtime'
        END AS DurationType
    FROM BatchEventSequence
    WHERE NextTimestamp IS NOT NULL
)
-- Sum and format the totals
SELECT
    BatchID,
    DurationType,
    SUM(DurationInSeconds) AS TotalSeconds,
    -- Convert seconds to HH:MM:SS for readability
    CONVERT(VARCHAR(8), DATEADD(SECOND, SUM(DurationInSeconds), 0), 108) AS TotalTimeFormatted
FROM BatchIntervalDurations
WHERE DurationInSeconds IS NOT NULL
GROUP BY BatchID, DurationType
ORDER BY BatchID, DurationType;

1. Set up your data source: Right-click Shared Data Sources (or Report Data Sources) in your SSRS project, add a connection to your SQL Server database.
2. Create a dataset: Right-click Datasets, make a new one, paste your adjusted SQL query, and link it to your data source.
3. Design your report: Add a table to your report layout, drag BatchID, DurationType, and TotalTimeFormatted into the table columns.
4. Optional: Add parameters: If you want users to filter by date range or batch ID, add report parameters and update your SQL query to use them (e.g., WHERE Timestamp BETWEEN @StartDate AND @EndDate).

• If LEAD() throws an error: It’s available in SQL Server 2012 and later. If you’re on an older version, let me know and I can share a self-join alternative.
• Test your SQL first! Run it in SQL Server Management Studio (SSMS) before bringing it into SSRS—way easier to fix errors there.
• Double-check your Timestamp column is a datetime type (not text/varchar). If it’s stored as text, date calculations will break.

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