Help with SQL - Calculate time difference for consecutive rows

Question

Help with SQL - Calculate time difference for consecutive rows

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Luis Cazares (7/2/2015)
I'm not sure about your desired results, but I believe that you need to add a partition to your window functions.
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 (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, S_DATETIME;

The results do not quite match what the OP posted as expected results.

Y.B. SSChampion Points: 11649 More actions · Answer 2

Since you (the OP) stated that your query worked with a single user I just used your own code with a specific user for testing.

SELECT

S_ID,

LAG(S_ACTV_CODE, 1) OVER (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 (ORDER BY S_DATETIME ASC) AS END_TIME,

DATEDIFF (SECOND, S_DATETIME,

LEAD(S_DATETIME, 1) OVER (ORDER BY S_DATETIME ASC)) AS DURATION

FROM (

SELECT

S_ID,

LAG(S_ACTV_CODE, 1, 'N/A')

OVER ( 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,'') AND S_USER = 'USER1'

ORDER BY S_ID, S_USER, S_DATETIME

This gave me the desired results you were looking for when querying all of the users.

SELECT

S_ID,

LAG(S_ACTV_CODE, 1) OVER (PARTITION BY S_USER 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_USER ORDER BY S_DATETIME ASC) AS END_TIME,

DATEDIFF (SECOND, S_DATETIME,

LEAD(S_DATETIME, 1) OVER (PARTITION BY S_USER ORDER BY S_DATETIME ASC)) AS DURATION

FROM (

SELECT

S_ID,

LAG(S_ACTV_CODE, 1, 'N/A') OVER (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_USER, START_TIME, S_ACTV_CODE

Give that a try and let us know how you make out.

Cheers,

SELECT quote FROM brain WHERE original = 1
0 rows returned

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

yb751 (7/2/2015)
Since you (the OP) stated that your query worked with a single user I just used your own code with a specific user for testing.
SELECT
S_ID,
LAG(S_ACTV_CODE, 1) OVER (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 (ORDER BY S_DATETIME ASC) AS END_TIME,
DATEDIFF (SECOND, S_DATETIME,
LEAD(S_DATETIME, 1) OVER (ORDER BY S_DATETIME ASC)) AS DURATION
FROM (
SELECT
S_ID,
LAG(S_ACTV_CODE, 1, 'N/A')
OVER ( 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,'') AND S_USER = 'USER1'
ORDER BY S_ID, S_USER, S_DATETIME
This gave me the desired results you were looking for when querying all of the users.
SELECT
S_ID,
LAG(S_ACTV_CODE, 1) OVER (PARTITION BY S_USER 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_USER ORDER BY S_DATETIME ASC) AS END_TIME,
DATEDIFF (SECOND, S_DATETIME,
LEAD(S_DATETIME, 1) OVER (PARTITION BY S_USER ORDER BY S_DATETIME ASC)) AS DURATION
FROM (
SELECT
S_ID,
LAG(S_ACTV_CODE, 1, 'N/A') OVER (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_USER, START_TIME, S_ACTV_CODE
Give that a try and let us know how you make out.
Cheers,

I tried it and the results I see don't match the expected results provided by the OP unless those have changed.

Y.B. SSChampion Points: 11649 More actions · Answer 4

Hmmm...not sure why but I just double checked and it looks fine here. I also checked the other 'users' separately with expected results.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Y.B. SSChampion Points: 11649 More actions · Answer 5

That being said he mentioned it worked for a single S_ID but I went by S_USER.

In that case...

TEST for single ID

SELECT

S_ID,

LAG(S_ACTV_CODE, 1) OVER (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 (ORDER BY S_DATETIME ASC) AS END_TIME,

DATEDIFF (SECOND, S_DATETIME,

LEAD(S_DATETIME, 1) OVER (ORDER BY S_DATETIME ASC)) AS DURATION

FROM (

SELECT

S_ID,

LAG(S_ACTV_CODE, 1, 'N/A')

OVER ( 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,'') AND S_ID = 'AAA-111'

ORDER BY S_ID, S_USER, S_DATETIME

ALL ID's

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 (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, S_USER, S_ACTV_CODE, START_TIME

SELECT quote FROM brain WHERE original = 1
0 rows returned

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Nicely formatted expected results from the OPs initial post:

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

Edit:

Maybe the sort order and crooked eyes on my part.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Thank you for the formatted expected results Lynn. You're right, the results are different. However, I'm not sure if the expected results are wrong or the query results are wrong.

For example, on S_ID AAA-113 there are 3 rows, but the expected results don't match the sample data as the second row shows a different second and the same value on S_ACTV_CODE as the first one (NULL).

I'd need an explanation to know if something should be corrected.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rupas2000 SSC Veteran Points: 222 More actions · Answer 8

Hi all, thanks for replies. I have updated my query the below way.

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;

The above query worked for me and gave the expected results. Thanks once again for all your replies.