-1

I have table named vehicledata which consists 4 columns: id, Veh No,Veh Mode,Timestamp.

My data looks like this:

id  Veh No  Veh Mode    Timestamp
1   KA03-003    IDLE    16-05-2020 09:01
2   KA03-003    IDLE    16-05-2020 09:06
3   KA03-003    IDLE    16-05-2020 09:10
4   KA03-003    DRIVE   16-05-2020 09:18
5   KA03-003    DRIVE   16-05-2020 09:40
6   KA03-003    DRIVE   16-05-2020 09:41
7   KA03-003    DRIVE   16-05-2020 09:42
8   KA03-003    DRIVE   16-05-2020 09:43
9   KA03-003    IDLE    16-05-2020 09:57
10  KA03-003    IDLE    16-05-2020 10:00
11  KA03-003    IDLE    16-05-2020 10:01
12  KA03-003    DRIVE   16-05-2020 10:05
13  KA03-003    DRIVE   16-05-2020 10:07
14  KA03-003    DRIVE   16-05-2020 10:08
15  KA03-003    DRIVE   16-05-2020 10:09
16  KA05-005    IDLE    16-05-2020 09:01
17  KA05-005    IDLE    16-05-2020 09:06
18  KA05-005    IDLE    16-05-2020 09:10
19  KA05-005    DRIVE   16-05-2020 09:18
20  KA05-005    DRIVE   16-05-2020 09:40
21  KA05-005    DRIVE   16-05-2020 09:41
22  KA05-005    DRIVE   16-05-2020 09:42
23  KA05-005    DRIVE   16-05-2020 09:43
24  KA05-005    IDLE    16-05-2020 09:57
25  KA05-005    IDLE    16-05-2020 10:00
26  KA05-005    IDLE    16-05-2020 10:01
27  KA05-005    DRIVE   16-05-2020 10:05
28  KA05-005    DRIVE   16-05-2020 10:07
29  KA05-005    DRIVE   16-05-2020 10:08
30  KA05-005    DRIVE   16-05-2020 10:09

In the above table there are 2 Veh No i.e KA03-003 & KA05-005. From id 4 to 8 & 12 to 15 there are two DRIVE cycles for KAO3-003. Now refer to timestamp column where from id 4 to 8 total time of DRIVE mode is 00:25 & from id 12 to 15 total time of DRIVE mode is 00:03.Now the total time of 2 drive cycles sums up to 00:28

Similarly In Veh No KA05-005 From id 19 to 23 & 27 to 30 there are two DRIVE cycles

For these Vehicles i need to get count of 'DRIVE' cycles occured in a table & sum of time in hours format.In which result should look as below:

Veh No     No.of.Drive Cycles   Total Duration
KA03-003         2                 00:28
KA05-005         2                 00:28
Pradeep Gn
  • 13
  • 4
  • How is the duration calculated? – Gordon Linoff May 21 '20 at 12:22
  • Welcome to SO. Please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry May 21 '20 at 12:23
  • [How is this different from your previous question from one hour ago, where you never accepted any answer?](https://stackoverflow.com/questions/61932441/sql-query-to-get-count-of-cycles-if-it-matches-value-of-column-in-consecutive-ro) – Tim Biegeleisen May 21 '20 at 12:26
  • select veh no,timediff(MAX(timestamp),MIN(timestamp)) as 'Total Duration' from vehicle data where veh mode='DRIVE' group by veh no; used this query to get result however the output is wrong. i am getting result as 00:51 instead of 00:28 – Pradeep Gn May 21 '20 at 12:26

1 Answers1

1

This is a gaps-and-islands problem, probably most easily solved using the difference of row numbers. The following calculates the difference as a number of seconds. It is pretty easy to modify for minutes or hours. And you can convert to a time format if you really want, but a number is usually simpler for durations:

select veh_no, count(*), sum(diff_seconds),
       time('00:00:00') + interval sum(diff_seconds) second
from (select veh_no, count(*) as num_drive_cycles,
             timestampdiff(second, min(timestamp), max(timestamp)) as diff_seconds
      from (select t.*,
                   row_number() over (partition by veh_no order by timestamp) as seqnum,
                   row_number() over (partition by veh_no, veh_mode order by timestamp) as seqnum_s
            from t
           ) t
      where veh_mode = 'DRIVE'
      group by veh_no, (seqnum - seqnum_s)
     ) v
group by veh_no;

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786