1

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
  • Are the date_from and date_to columns ALWAYS whole hours? – Max Vernon Oct 21 '15 at 16:41
  • Yes they are whole hours. – Guilherme Reda Oct 21 '15 at 16:42
  • Also, is the id column actually unique for each row? – Max Vernon Oct 21 '15 at 16:42
  • Yes, it is unique – Guilherme Reda Oct 21 '15 at 16:43
  • 2
    I'm sorry, forgot to make unique for the example. – Guilherme Reda Oct 21 '15 at 16:44
0

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.

Related answer with more details:

About my counting method count(<expression> OR NULL):

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.