Nick Nick - 23 days ago 14
SQL Question

Oracle 11g - SQL to Calculate time difference between several rows

PROBLEM

I'm still finding my feet with SQL and trying to calculate how long a certain user has been scanning items during their shift.

Each scan is timestamped generating a unique 9 digit sequence number (SEQ column) and date/time in the format

05-NOV-16 15:35:24
(THE_DATE column).

The person may be scanning for several hours, and what im trying to do is subtract the first timestamp they generated from the very last timestamp at the end of their shift.

So for example given this data sample:

+-----------+--------------------+--------+---------+---------+------------+-----------+
| SEQ | THE_DATE | SCANID | LOCATN | USER_ID | FIRST_NAME | LAST_NAME |
+-----------+--------------------+--------+---------+---------+------------+-----------+
| 103939758 | 05-NOV-16 14:36:22 | 194972 | DOOR 19 | AX9868 | Mike | Derry |
| 103939780 | 05-NOV-16 14:38:07 | 194972 | DOOR 19 | AX9868 | Mike | Derry |
| 103939792 | 05-NOV-16 14:39:24 | 194972 | DOOR 19 | AX9868 | Mike | Derry |
| 103940184 | 05-NOV-16 15:16:53 | 194972 | DOOR 19 | AX9868 | Mike | Derry |
| 103940185 | 05-NOV-16 15:51:41 | 194972 | DOOR 19 | AX9868 | Mike | Derry |
| 103940214 | 05-NOV-16 09:51:42 | 194993 | DOOR 16 | BC1910 | Tony | McCann |
| 103940215 | 05-NOV-16 15:19:06 | 194993 | DOOR 16 | BC1910 | Tony | McCann |
|+-----------+--------------------+--------+---------+---------+------------------------


DESIRED RESULT

I would like to subtract the timestamp in the first row for Mike Derry, from the last row on which he appears, row 5 in this case, so that i have an answer in hours (1.25).

the final result should be grouped by day and by user_id,first_name and last_name.

So far i have looked online and at the oracle documentation ,which led me to try using the LEAD function which seemed promising. It looks at the next rows to find the next timestamp where a userid appears next and then partitions by this userid to create a new column with that timestamp.

So the SQL looked like this

SELECT SEQ, THE_DATE,SCANID,LOCATN,USER_ID,LEAD(SYSDAT ) OVER (PARTITION BY USER_ID ORDER BY SYSDAT) AS NEXT_SCAN
FROM myTable...


However this is giving me incorrect results as it seems to double count the time difference. Im sure you SQL gurus have a more elegant way around this as i dont think this function suits this particular problem :)

So the final result im trying to achieve is:

+-----------+---------+------------+-----------+-----------+
| THE_DATE | USER_ID | FIRST_NAME | LAST_NAME | TOTAL_HRS |
+-----------+---------+------------+-----------+-----------+
| 05-NOV-16 | AX9868 | Mike | Derry | 1.25 |
| 05-NOV-16 | BC1910 | Tony | McCann | 5.47 |
+-----------+---------+------------+-----------+-----------+


Your help is much appreciated

Answer
SELECT TRUNC(THE_DATE) as THE_DATE, USER_ID, FIRST_NAME, LAST_NAME,
       MAX(THE_DATE) - MIN(THE_DATE) as TOTAL_HRS 
FROM yourTable
GROUP BY TRUNC(THE_DATE), USER_ID, FIRST_NAME, LAST_NAME
Comments