SQL: Query to get three timestamp consecutive and put in the columns

user_11215814 0
Viewed 32

Imagine we have the following table where 0 is a start time, and 1 is an end time of the same kind x process.

way time kind
0 12:12.34 x
1 12:12.55 x
0 12:15.22 x
1 12:15.59 x
0 12:16.07 x
1 12:16.42 x

I would like get as result:

kind start end next_start
x 12:12.34 12:12.55 12:15.22
x 12:15.22 12:15.59 12:16.07
x 12:16.07 12:16.42 (so on)

In time order the first three events should be pone in start, end, next start column because following the way 0 = start, 1= end, and so on.

i did in this way but there is an error regarding next_start

SELECT kind, start, end, next_start 
FROM(
    SELECT kind, F.time as start, S.time as end, max(F.time) as next_start 
    from Table F
    INNER JOIN Table S
        ON F.kind = S.kind
    WHERE end>start
        AND next_start> end
    GROUP BY kind, start, end
)
order by next_start
timestamp oracle sql
1 Answers

We can try to use SUM window function to get each start and end time grouping number, then use MIN and MAX aggregate function to get the start and end time.

Final, we can try to use LEAD window function to get next_start column.

Query 1:

SELECT kind,
       "start",
       "end",
       LEAD("start") OVER(ORDER BY "start") next_start
    FROM (
     SELECT kind,
           MIN("time") "start",
           MAX("time") "end"
    FROM (
        SELECT t1.*,
               SUM(CASE WHEN way = 0 THEN 1 ELSE 0 END) OVER(ORDER BY "time") grp
        FROM "Table" t1 
    ) t1
    GROUP BY kind,grp
) t1

Results:

| KIND |                 start |                   end |            NEXT_START |
|------|-----------------------|-----------------------|-----------------------|
|    x | 2022-06-15 12:12:34.0 | 2022-06-15 12:12:55.0 | 2022-06-15 12:15:22.0 |
|    x | 2022-06-15 12:15:22.0 | 2022-06-15 12:15:59.0 | 2022-06-15 12:16:07.0 |
|    x | 2022-06-15 12:16:07.0 | 2022-06-15 12:16:42.0 |                (null) |
user_51760710
Related