SQL查询需求：统计WorkLines中各作业类型行数及发货单累计耗时

Got it, let's build the SQL query you need to match your expected output. Here's the breakdown of what we need to do:

1. Calculate the duration of each individual work task: Each task has two rows (worktype 1 for start, worktype 2 for end), so we'll pair these to get the time difference for each task.
2. Aggregate the data by shipment and work class: Count the number of tasks and sum their durations for each work type (SO Pick, Stage, Packing).
3. Pivot the aggregated data into columns: Convert the row-based work class data into the column format you need, and calculate the total time per shipment.

Here's the complete query, tailored to your data structure:

WITH TaskDurations AS (
    -- Pair start and end rows to calculate each task's duration
    SELECT
        s.shipmentid,
        s.workclassid,
        -- Calculate duration in seconds first for easier aggregation
        DATEDIFF(SECOND, s.workclosedutcdatetime, e.workclosedutcdatetime) AS task_duration_sec
    FROM whsworkline s
    JOIN whsworkline e
        ON s.shipmentid = e.shipmentid
        AND s.itemid = e.itemid
        AND s.workclassid = e.workclassid
        AND s.worktype = 1
        AND e.worktype = 2
    WHERE
        s.workclosedutcdatetime > '2017-08-16'
        AND s.workclosedutcdatetime < '2017-10-15'
        AND s.workclassid IN ('SO PICK', 'STAGE', 'PACKING')
),
WorkClassAggregates AS (
    -- Aggregate counts and total durations per shipment and work class
    SELECT
        shipmentid,
        workclassid,
        COUNT(*) AS task_count,
        SUM(task_duration_sec) AS total_duration_sec
    FROM TaskDurations
    GROUP BY shipmentid, workclassid
)
-- Pivot data into desired columns and calculate total shipment time
SELECT
    shipmentid AS [Shipment ID],
    -- SO Pick metrics
    MAX(CASE WHEN workclassid = 'SO PICK' THEN task_count END) AS [# of SO Picks],
    CASE 
        WHEN MAX(CASE WHEN workclassid = 'SO PICK' THEN total_duration_sec END) IS NOT NULL
        THEN FORMAT(CAST(MAX(CASE WHEN workclassid = 'SO PICK' THEN total_duration_sec END) AS INT) / 3600, '00') 
            + ':' + FORMAT((CAST(MAX(CASE WHEN workclassid = 'SO PICK' THEN total_duration_sec END) AS INT) % 3600) / 60, '00') 
            + ':' + FORMAT(CAST(MAX(CASE WHEN workclassid = 'SO PICK' THEN total_duration_sec END) AS INT) % 60, '00')
        ELSE NULL
    END AS [SO Pick Time],
    -- Stage metrics
    MAX(CASE WHEN workclassid = 'STAGE' THEN task_count END) AS [# of Stage Lines],
    CASE 
        WHEN MAX(CASE WHEN workclassid = 'STAGE' THEN total_duration_sec END) IS NOT NULL
        THEN FORMAT(CAST(MAX(CASE WHEN workclassid = 'STAGE' THEN total_duration_sec END) AS INT) / 3600, '00') 
            + ':' + FORMAT((CAST(MAX(CASE WHEN workclassid = 'STAGE' THEN total_duration_sec END) AS INT) % 3600) / 60, '00') 
            + ':' + FORMAT(CAST(MAX(CASE WHEN workclassid = 'STAGE' THEN total_duration_sec END) AS INT) % 60, '00')
        ELSE NULL
    END AS [Stage Time],
    -- Packing metrics
    MAX(CASE WHEN workclassid = 'PACKING' THEN task_count END) AS [# of Packing lines],
    CASE 
        WHEN MAX(CASE WHEN workclassid = 'PACKING' THEN total_duration_sec END) IS NOT NULL
        THEN FORMAT(CAST(MAX(CASE WHEN workclassid = 'PACKING' THEN total_duration_sec END) AS INT) / 3600, '00') 
            + ':' + FORMAT((CAST(MAX(CASE WHEN workclassid = 'PACKING' THEN total_duration_sec END) AS INT) % 3600) / 60, '00') 
            + ':' + FORMAT(CAST(MAX(CASE WHEN workclassid = 'PACKING' THEN total_duration_sec END) AS INT) % 60, '00')
        ELSE NULL
    END AS [Packing Time],
    -- Total shipment time
    CASE 
        WHEN SUM(total_duration_sec) IS NOT NULL
        THEN FORMAT(CAST(SUM(total_duration_sec) AS INT) / 3600, '00') 
            + ':' + FORMAT((CAST(SUM(total_duration_sec) AS INT) % 3600) / 60, '00') 
            + ':' + FORMAT(CAST(SUM(total_duration_sec) AS INT) % 60, '00')
        ELSE NULL
    END AS [Total time of shipment]
FROM WorkClassAggregates
GROUP BY shipmentid
ORDER BY shipmentid;

Key Details: #

• Task Pairing: The first CTE (TaskDurations) matches each start task (worktype=1) to its corresponding end task (worktype=2) using shared identifiers like shipmentid and itemid to ensure accurate duration calculations.
• Time Formatting: We calculate durations in seconds first (simpler for aggregation), then convert to HH:MM:SS using FORMAT (works for SQL Server; use SEC_TO_TIME if you're working with MySQL).
• Null Handling: If a shipment has no tasks for a specific work class, the query returns NULL for those columns, exactly matching your expected output.
• Total Time Calculation: The final total time sums all task durations across work classes for each shipment.

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