Extract start and end per group of rows, where only the end can be identified

Question

I have a table that looks like this.

id  date_from           date_to             description           vehicle
1   2015-10-21 08:00    2015-10-21 10:00    GARAGE TO CLIENT 1    1
2   2015-10-21 12:00    2015-10-21 13:00    CLIENT 2 TO GARAGE    1
3   2015-10-21 14:00    2015-10-21 15:00    RET GARAGE            1
4   2015-10-21 18:00    2015-10-21 19:00    GARAGE TO CLIENT 1    1
5   2015-10-21 20:00    2015-10-21 21:00    CLIENT 2 TO GARAGE    1
6   2015-10-21 21:00    2015-10-21 22:00    RET GARAGE            1

I need to get the total time that the vehicle is in use. He stops being in use on first line and stops on the first description that starts with "RET".

A table like that would return

vehicle   starts              stops
1         2015-10-21 08:00    2015-10-21 15:00
1         2015-10-21 18:00    2015-10-21 22:00

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

You can use count() as window function to identify groups like this:

SELECT vehicle, min(date_from) AS starts, max(date_to) AS stops
FROM  (
   SELECT vehicle, date_from, date_to
        , count(description LIKE 'RET%' OR NULL)
             OVER (PARTITION BY vehicle ORDER BY date_from DESC) AS grp
    FROM  tbl
   ) sub
GROUP  BY vehicle, grp;

Count in descending order, since the end of each group is significant. Then just extract minimum start and maximum end per grp in the outer SELECT.

SQL Fiddle.

Assuming all timestamp columns are defined NOT NULL.

Since timestamps are in ascending order (due to the logic of the problem), we don't need the id column at all for this.

This works for any number of vehicles, not just the one you demonstrate in your example.

Extract start and end per group of rows, where only the end can be identified

1 Answers1