July 2, 2015 at 7:02 am
Can someone help me with a SQL query as per my below requirement? I have a table like this.
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)
;
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
July 2, 2015 at 7:35 am
Hi and welcome to the forums. Since you are posting in the 2012 forum I assume you must be using sql 2012. You can use the LEAD/LAG functions to do this kind of thing.
I would help you with code but as posted this just isn't legible at all. If you need some help with the coding please take a few minutes to read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2015 at 7:57 am
Hi Sean
Thanks for your reply. I have written a code using LEAD/LAG but it is working partially.
As I am new to this forum, not sure where I can create a table. If you can advice me where I can create table with some data, then I can create a table with some data on it.
Thanks
July 2, 2015 at 8:21 am
rupas2000 (7/2/2015)
Hi SeanThanks for your reply. I have written a code using LEAD/LAG but it is working partially.
As I am new to this forum, not sure where I can create a table. If you can advice me where I can create table with some data, then I can create a table with some data on it.
Thanks
No problem. When posting you will see IFCode shortcuts on the left side. You need to generate the sql script first and then you can wrap the create table statement and inserts in a code block. It will end up looking like this.
create table SomeTable
.
.
.
Insert SomeTable
.
.
.
You just need to provide the details for the create table and the insert statements. That make sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2015 at 8:49 am
Hi Sean,
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.
July 2, 2015 at 9:09 am
rupas2000 (7/2/2015)
Hi Sean,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.
What does "not working completely" mean?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2015 at 9:16 am
What I mean is, it's working partially. It's giving all columns correct values, except End_Time.
For a single S_ID it is giving correct value but when I query on all S_IDs it is not giving proper results.
July 2, 2015 at 9:20 am
rupas2000 (7/2/2015)
What I mean is, it's working partially. It's giving all columns correct values, except End_Time.For a single S_ID it is giving correct value but when I query on all S_IDs it is not giving proper results.
I don't have 2012 at work so I can't run this code to see what is going on. If nobody else stops by to help I will take a look at this later tonight from home.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2015 at 9:29 am
Ok, no problem. Thanks once again for your prompt response.
July 2, 2015 at 9:48 am
I haven't run it (busy packing up at work), but as a guess, do you need a PARTITION BY S_ID in your OVER clauses? At the moment, they're working across every row in the table, not the rows of the same S_ID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2015 at 9:56 am
I tried using several ways including using 'Partition by' but couldn't succeed. The above query is bit closer to my results.
July 2, 2015 at 10:09 am
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;
July 2, 2015 at 10:14 am
Thanks Gail and Luis. Don't know how I missed that. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2015 at 10:22 am
Sean Lange (7/2/2015)
Thanks Gail and Luis. Don't know how I missed that. 😛
Probably your mind is already in a long weekend.
July 2, 2015 at 10:25 am
Luis Cazares (7/2/2015)
Sean Lange (7/2/2015)
Thanks Gail and Luis. Don't know how I missed that. 😛Probably your mind is already in a long weekend.
Yes indeed. Pretty sure I checked out right around 2pm on Monday!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply