Help me with SQL query - Calculate time difference for consecutive rows

Other SQL Server Topics
1

Can someone help me with a SQL query as per my below requirement? I have a table like this.

[code="sql"]CREATE TABLE Table1
([S_ID] varchar(7), [S_ACTV_CODE] varchar(4), [S_USER] varchar(5), [S_DATETIME] varchar(19), [S_ACT_IND] int)
;

INSERT INTO Table1
([S_ID], [S_ACTV_CODE], [S_USER], [S_DATETIME], [S_ACT_IND])
VALUES
('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', 0),
('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 18:48:03', 1)
;
[/code]

Basically I want to calculate the time spent by S_Users on a particular S_ACTV_CODE:

  • S_ACTV_CODE_PREV means the previous active records.
  • S_START_TIME is the time of S_DATETIME when a S_ACTV_CODE starts
  • S_END_TIME is the time before a S_ACTV_CODE changes to another S_ACTV_CODE
  • For the first record, S_ACTV_CODE is null, so there is no S_ACTV_CODE_PREV, so S_ACTV_CODE_PREV is NULL
  • For the second record S_ACTV_CODE has some value, but S_ACTV_CODE_PREV is NULL for first record. So second record S_ACTV_CODE_PREV is also NULL
  • For the last record (means S_ACTV_IND = 1), the user is currently working on it and S_ACTV_CODE is not changed. So S_END_TIME is a open time and we want to keep it as NULL

So the result should be as below:

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

2

Would you please:

  1. provide your data in a consumable format. That is, CREATE TABLE statements and INSERT INTO statements
  2. post the query you are working on, whatever state it is in.
3

Hi,
Thanks for your reply. I have written a code like below:

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,'')
ORDER BY S_ID, S_DATETIME

But it is not working completely. Can you please look into this.

Thanks in advance.

4

Please provide your data in a consumable format. That is, CREATE TABLE statements and INSERT INTO statements

5

I believe they have:

CREATE TABLE Table1
([S_ID] varchar(7), [S_ACTV_CODE] varchar(4), [S_USER] varchar(5), [S_DATETIME] varchar(19), [S_ACT_IND] int)
;

INSERT INTO Table1
([S_ID], [S_ACTV_CODE], [S_USER], [S_DATETIME], [S_ACT_IND])
VALUES
('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', 0),
('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 18:48:03', 1)
;
6

I think I modified the same in my initial topic. Please check..

7

Hi all, I have modified my query with Partition By, and it gave the correct results.
Thanks once again for your reply.