SQL Server SQL Query for finding the start and endtime

yeotifhr  于 2023-05-05  发布在  其他
关注(0)|答案(2)|浏览(134)

Grouping the start time and end time based on the gaps.

My input:
| ID | start_time | end_time |
| ------------ | ------------ | ------------ |
| 1 | 08:00:00 | 08:05:00 |
| 1 | 08:05:00 | 09:55:00 |
| 1 | 09:55:00 | 10:15:00 |
| 1 | 10:15:00 | 11:03:00 |
| 1 | 11:03:00 | 12:01:00 |
| 1 | 13:02:00 | 13:44:00 |
| 1 | 14:44:00 | 14:55:00 |
| 1 | 14:55:00 | 15:03:00 |
| 1 | 15:03:00 | 17:00:00 |

Output:

IDstart_timeend_timereal_startreal_end
108:00:0008:05:0008:00:0012:01:00
108:05:0009:55:0008:00:0012:01:00
109:55:0010:15:0008:00:0012:01:00
110:15:0011:03:0008:00:0012:01:00
111:03:0012:01:0008:00:0012:01:00
113:02:0013:44:0013:02:0013:44:00
114:44:0014:55:0014:44:0017:00:00
114:55:0015:03:0014:44:0017:00:00
115:03:0017:00:0014:44:0017:00:00

To find real start and end time ,I need to get only three rows start and end time should change, if any gaps.

IDreal_startreal_end
108:00:0012:01:00
113:02:0013:44:00
114:44:0017:00:00
kd3sttzy

kd3sttzy1#

You can use the LAG() function to compare start times with prior end times to detect a gap. You can then assign groups by counting the number of preceding gaps using a running SUM() . Once group numbers are assigned, MIN() and MAX() functions can be used to calculate the "real start" and "real end" for each group.

The following uses CTEs (common table expressions to build up the above logic leading to the final select.

;WITH CteGap AS (
    SELECT *,
        CASE WHEN start_time > LAG(end_time) OVER(PARTITION BY ID ORDER BY start_time)
            THEN 1 ELSE 0 END AS IsGap
    FROM times
),
CteGroupNum AS (
   SELECT *,
       SUM(IsGap) OVER(PARTITION BY ID ORDER BY start_time) AS GroupNum
    FROM CteGap
)
SELECT ID, MIN(start_time) AS real_start, MAX(end_time) AS real_end
FROM CteGroupNum
GROUP BY ID, GroupNum
ORDER BY ID, GroupNum

The same can be accomplished using nested selects:

SELECT ID, MIN(start_time) AS real_start, MAX(end_time) AS real_end
FROM (
   SELECT *,
       SUM(IsGap) OVER(PARTITION BY ID ORDER BY start_time) AS GroupNum
    FROM (
        SELECT *,
            CASE WHEN start_time > LAG(end_time) OVER(PARTITION BY ID ORDER BY start_time)
                THEN 1 ELSE 0 END AS IsGap
        FROM times
    ) Gaps
) Groups
GROUP BY ID, GroupNum
ORDER BY ID, GroupNum

See this db<>fiddle for a working example with some extra data to show independent calculations per ID.

Results:
| ID | real_start | real_end |
| ------------ | ------------ | ------------ |
| 1 | 08:00:00 | 12:01:00 |
| 1 | 13:02:00 | 13:44:00 |
| 1 | 14:44:00 | 17:00:00 |
| 2 | 08:00:00 | 08:45:00 |
| 2 | 09:00:00 | 10:00:00 |

jmo0nnb3

jmo0nnb32#

Assuming your data is in CREATE TABLE times (ID int, start_time time(7), end_time time(7)) and ID changes with each day, and you want your results in CREATE TABLE real_times (ID int, real_start time(7), real_end time(7)) then this is how you could do it:

SELECT * FROM times ORDER BY ID, start_time

DECLARE @starts AS TABLE (num int IDENTITY, ID int, [time] time(7))
DECLARE @ends AS TABLE (num int IDENTITY, ID int, [time] time(7))

INSERT INTO @starts
  SELECT ID, start_time FROM times AS a
  WHERE NOT EXISTS (SELECT end_time FROM times WHERE end_time = a.start_time AND ID = a.ID)

INSERT INTO @ends
  SELECT ID, end_time FROM times AS a
  WHERE NOT EXISTS (SELECT start_time FROM times WHERE start_time = a.end_time AND ID = a.ID)

INSERT INTO real_times (ID, real_start, real_end)
  SELECT s.ID, s.[time], e.[time] FROM @starts s
  LEFT OUTER JOIN @ends e ON s.num = e.num AND s.ID = e.ID

SELECT * FROM real_times ORDER BY ID, real_start

相关问题