Calculate time difference for consecutive lines
Can anyone help me with the SQL query as per my below requirement? I have a table like this.
S_ID S_ACTV_CODE S_USER S_DATETIME S_ACT_IND
AAA-111 NULL USER1 2015-06-15 00:21:06 0
AAA-111 2 USER1 2015-06-15 00:21:07 0
AAA-111 2 USER1 2015-06-15 00:25:12 0
AAA-111 4 USER2 2015-06-17 03:20:33 0
AAA-111 3 USER1 2015-06-17 03:43:25 0
AAA-111 4 USER3 2015-06-22 05:02:37 0
AAA-111 4 USER4 2015-06-23 05:25:05 1
AAA-112 NULL USER4 2015-06-25 11:11:11 0
AAA-112 4 USER3 2015-06-25 11:11:12 0
AAA-112 4 USER4 2015-06-26 20:25:49 0
AAA-112 4 USER2 2015-06-29 18:04:32 1
AAA-113 NULL USER2 2015-06-24 07:10:37 0
AAA-113 NULL USER1 2015-06-24 07:10:41 0
AAA-113 3 USER1 2015-06-24 07:10:43 1
Basically, I want to calculate the time taken by S_Users for a specific S_ACTV_CODE:
- S_ACTV_CODE_PREV means previous active entries.
- S_START_TIME - S_DATETIME time when starting S_ACTV_CODE
- S_END_TIME is the time before S_ACTV_CODE changes to another S_ACTV_CODE
- For the first record, S_ACTV_CODE is NULL, so S_ACTV_CODE_PREV is missing, so S_ACTV_CODE_PREV is NULL
- For the second entry, S_ACTV_CODE has some value, but S_ACTV_CODE_PREV is NULL for the first entry. So the second entry S_ACTV_CODE_PREV is also NULL
- For the last entry (meaning S_ACTV_IND = 1), the user is currently working on it and S_ACTV_CODE is unchanged. So S_END_TIME is open time and we want to store it as NULL
So the result should be as follows:
S_ID S_ACTV_CODE_PREV S_ACTV_CODE_CURR S_USER S_START_TIME S_END_TIME TIME_SPENT (in Sec)
AAA-111 NULL NULL USER1 2015-06-15 00:21:06 2015-06-15 00:21:07 1
AAA-111 NULL 2 USER1 2015-06-15 00:21:07 2015-06-17 03:20:33 183566
AAA-111 2 4 USER2 2015-06-17 03:20:33 2015-06-17 03:43:25 1372
AAA-111 4 3 USER3 2015-06-17 03:43:25 2015-06-22 05:02:37 436752
AAA-111 3 4 USER4 2015-06-22 05:02:37 NULL NULL
AAA-112 NULL NULL USER4 2015-06-25 11:11:11 2015-06-25 11:11:12 1
AAA-112 NULL 4 USER3 2015-06-25 11:11:12 NULL NULL
AAA-113 NULL NULL USER2 2015-06-24 07:10:37 2015-06-24 07:10:43 6
AAA-113 NULL 3 USER1 2015-06-24 07:10:43 NULL NULL
+3
source to share
2 answers
Re-wrote SQL to get first date and last user for rows. This made it uglier, but this should work:
select
s_id,
lag(s_actv_code, 1) over (partition by s_id order by s_datetime asc) as s_actv_code_prev,
s_actv_code,
s_user,
s_datetime as start_time,
lead(s_datetime, 1) over (partition by s_id order by s_datetime asc) as end_time,
datediff (second, s_datetime,
lead(s_datetime, 1) over (partition by s_id order by s_datetime asc)) as duration
from
(
select distinct
s_id,
S_ACTV_CODE,
last_value(s_user)
over (partition by s_id, S_ACTV_CODE, GRP
order by S_datetime asc
rows between current row and unbounded following) as s_user,
first_value(s_datetime)
over (partition by s_id, S_ACTV_CODE, GRP
order by S_datetime asc
ROWS UNBOUNDED PRECEDING
) as s_datetime
from (
select
*,
row_number() over (partition by s_id order by s_datetime asc) -
row_number() over (partition by s_id, s_actv_code order by s_datetime asc) as GRP
from
table1
) X
) Y
order by s_id, start_time
Edit: Added s_id section due to new changes to the example.
You can check this in SQL Fiddle
+3
source to share
thank you for your responses. I've updated the request below.
SELECT
S_ID,
LAG(S_ACTV_CODE, 1) OVER (PARTITION BY S_ID ORDER BY S_DATETIME ASC) AS S_ACTV_CODE_PREV,
S_ACTV_CODE,
S_USER,
S_DATETIME AS START_TIME,
LEAD(S_DATETIME, 1) OVER (PARTITION BY S_ID ORDER BY S_DATETIME ASC) AS END_TIME,
DATEDIFF (SECOND, S_DATETIME,
LEAD(S_DATETIME, 1) OVER (PARTITION BY S_ID ORDER BY S_DATETIME ASC)) AS DURATION
FROM (
SELECT
S_ID,
LAG(S_ACTV_CODE, 1, 'N/A') OVER (PARTITION BY S_ID ORDER BY S_DATETIME ASC) AS S_ACTV_CODE_PREV,
S_ACTV_CODE,
S_USER,
S_DATETIME
FROM TABLE1
) X WHERE ISNULL(S_ACTV_CODE_PREV,'') != ISNULL(S_ACTV_CODE,'')
ORDER BY S_ID, START_TIME;
This query worked for me and gave the expected results. Thanks again for all your answers.
0
source to share