SQL Server 2012：扩展离岗原因关联的在岗时长计算与分组

Alright, let's tackle this problem head-on. The key issue here is linking each "on" record to its corresponding "off" record (which holds the Reason value) so we can calculate the time spent on-site for each session, then aggregate those totals by user and reason. Here's a straightforward approach that works perfectly with SQL Server 2012:

Step-by-Step Explanation #

First, we need to pair up each "on" entry with the next "off" entry for the same user. Since the Reason is only stored when OnOffSite = 'off', we'll use the LEAD() window function (available in SQL Server 2012) to "look ahead" and grab the details of the next record for each user. This lets us tie the on-site duration (from "on" to "off") directly to the Reason from the "off" entry.

Full SQL Query #

WITH UserSessionPairs AS (
    SELECT 
        UserName,
        Time AS OnTime,
        OnOffSite,
        -- Grab the next record's OnOffSite status, timestamp, and Reason
        LEAD(OnOffSite) OVER (PARTITION BY UserName ORDER BY Time) AS NextOnOffSite,
        LEAD(Time) OVER (PARTITION BY UserName ORDER BY Time) AS OffTime,
        LEAD(Reason) OVER (PARTITION BY UserName ORDER BY Time) AS SessionReason
    FROM YourTableName -- Replace with your actual table name
)
SELECT 
    UserName,
    SessionReason AS Reason,
    -- Calculate total on-site duration (adjust DATEPART to your needs: MINUTE, HOUR, SECOND, etc.)
    SUM(DATEDIFF(MINUTE, OnTime, OffTime)) AS TotalOnSiteMinutes
FROM UserSessionPairs
-- Only include valid on-site sessions that end with an off entry (and have a Reason)
WHERE OnOffSite = 'on'
  AND NextOnOffSite = 'off'
  AND SessionReason IS NOT NULL
GROUP BY UserName, SessionReason
ORDER BY UserName, SessionReason;

Breakdown of the Query #

1. CTE (UserSessionPairs):

   • We partition the data by UserName so we only look at each user's records individually.
   • LEAD() fetches the next record's values: whether it's an "off" entry, its timestamp, and the associated Reason.
   • We alias the original Time as OnTime for clarity, since we're focusing on "on" records here.

2. Main Query:

   • We filter to only include "on" records that are immediately followed by an "off" record (so we have a complete session start and end).
   • We exclude any sessions where SessionReason is NULL (since only "off" entries have valid Reasons, this ensures we're only using valid paired sessions).
   • DATEDIFF() calculates the duration of each on-site session—adjust the first parameter (e.g., HOUR instead of MINUTE) if you need a different time unit.
   • Finally, we group by UserName and SessionReason to get total on-site time per user and reason.

Handling Edge Cases #

• If you have a trailing "on" record with no corresponding "off" entry, it will be automatically excluded (since LEAD() returns NULL for the next record, and our filter removes those rows).
• If your records are ordered in reverse (e.g., "off" entries come before "on" entries), you can swap LEAD() with LAG() and adjust the filter to look for "off" records preceded by "on" entries—just tweak the logic to match your data flow!

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