June 18, 2012 at 11:25 am
I have table something as below
Acivity_id Entry_id Eventy_type_id Event_value Event_time Username
1107043 Pending2012-05-18 14:17:19.540Test0
2107046 Pending2012-05-18 14:17:20.710test1
3107041 New InfoReceived2012-05-18 14:18:13.957test2
4107043 ENROLLMENT TECHNICIAN2012-05-18 14:19:16.547 test3
5107046 Pending2012-05-18 14:24:45.057test4
6107041 New Info Received2012-05-18 14:25:14.780test5
Needed
Entry_id Start_time End_time Username
107042012-05-18 14:17:19.540 2012-05-18 14:17:20.710 Test0
107042012-05-18 14:24:45.057 2012-05-18 14:25:14.780 test3
Right now able to get Start time and end_time but unable to get the username:
Select End_time ,Entry,start_time
from
(
Select min(event_time)as End_time,A.Entry_Id as Entry,a.start_time
from dbo.activity B
Right join
(
select Activity_ID,entry_id,min(Event_Time) as start_time from dbo.activity doc
where doc.Event_Type_Id =6
and Event_Value like 'pending%'
group by Entry_Id,Activity_ID
)A on A.Entry_Id =B.Entry_Id and B.event_time > A.start_time
and B.Event_Type_Id =1
and B.Activity_ID > A.Activity_ID
group by A.Entry_Id,A.start_time
) T1
Can anyone modify this query to get username???
Script for table and data:
Create Table [dbo].[activity] (
Acivity_id int not null identity(1,1),---identity column
Entry_id int ,
Eventy_type_id int,
Event_value varchar(1023),
Event_time datetime,
Username Varchar(200))
--------------------------------------------------Scenario:record goes from pending to newinforecieved , then PENDING:sysrouing action is set after which goes to "pending"status again and then "newinfo recieved"----------------------------------
Insert INTO [dbo].[activity] (Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(10704,3,'Pending','2012-05-18 14:17:19.540','Test0')
Insert INTO [dbo].[activity] (Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(10704,6,'Pending','2012-05-18 14:17:20.710','test1')
Insert INTO [dbo].[activity] (Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(10704,1,'New Info Received','2012-05-18 14:18:13.957','test2')
Insert INTO [dbo].[activity] (Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(10704,3,'ENROLLMENT TECHNICIAN','2012-05-18 14:19:16.547','test3')
Insert INTO [dbo].[activity] (Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(10704,6,'Pending','2012-05-18 14:24:45.057','test4')
Insert INTO [dbo].[activity] (Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(10704,1,'New Info Received','2012-05-18 14:25:14.780','test5')
June 18, 2012 at 11:37 am
Not enough information. How are the records releated to each other in your expected results?
June 18, 2012 at 12:14 pm
The Start_time will be the each time when eventy_type_id =6 and event_value=pending
and end_time will be the each time when event_type_id changes from 6 to 1.
ans user_name will be the user who has event_type_id=3 immedediately before it changes to event_type_id =6
Hope you understand.
June 18, 2012 at 1:45 pm
Yeap clear as mud. So the query you said works doesn't work with the ddl and sample data provided. The ddl is full of typos.
I don't quite understand why you have an outer query around the whole thing at all.
This will produce the same results.
Select min(event_time)as End_time,A.Entry_Id as Entry,a.start_time
from dbo.activity B
Right join
(
select Acivity_ID,entry_id,min(Event_Time) as start_time from dbo.activity doc
where doc.Eventy_Type_Id =6
and Event_Value like 'pending%'
group by Entry_Id,Acivity_ID
)A on A.Entry_Id =B.Entry_Id and B.event_time > A.start_time
and B.Eventy_Type_Id =1
and B.Acivity_ID > A.Acivity_ID
group by A.Entry_Id,A.start_time
I am 100% certain this can be done a lot cleaner but you need to explain what it is you are looking for first.
_______________________________________________________________
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/
June 18, 2012 at 2:01 pm
I am trying to get the Start_time and end_time for an entry_id when the eventy_type_id changes from 6 to 1.I am getting start_time (event_time) when it has eventy_type_id=6 and end_time(event_time) wheneventy_type_id = 1.
and the Username who changes that event from 6 to 1.i.e the immediate event(username whose event_type_id=3 ) when cahnges from 6 to 1 .
Example :
entry_id event_type_id event_value username event_time
10704 6 pending test1 2012-05-18 14:17:20.710( will be start_time)
10704 1 newinfo test 2 2012-05-18 14:18:13.957( END_time)
10704 3 ENROLLMENT test3(required username) 2012-05-18 14:18:13.957
June 18, 2012 at 2:04 pm
That didn't exactly make it very clear. Can you show what the expected output should be based on your sample data?
_______________________________________________________________
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/
June 18, 2012 at 2:13 pm
This should be my expected result of my smaple data provided in (insert)
entry_id Start_time End_time Username
10704 2012-05-18 14:17:20.710 2012-05-18 14:18:13.957 test3
10704 2012-05-18 14:24:45.057 2012-05-18 14:25:14.780 NULL ( as no eventy_type_id=3 )
June 18, 2012 at 2:18 pm
OK now I am even more confused. I just realized that you tried to demonstrate output in your first post. There were two rows originally but now you say only 1. It would really help if you could make your desired output a hardcoded insert to a temp table. It is completely unreadable as a series of characters the way it is formatted here. I honestly am trying to help you but I don't see you putting much effort into helping me help you with your issue.
_______________________________________________________________
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/
June 18, 2012 at 2:26 pm
select '10704' as entry_id , '2012-05-18 14:17:20.710' as Start_time ,'2012-05-18 14:18:13.957' as End_time ,'test3' as USername
Union all
select '10704' as entry_id , '2012-05-18 14:24:45.057' as Start_time ,'2012-05-18 14:25:14.780' as End_time ,'NULL' as USername
hope you understand!!
June 18, 2012 at 2:40 pm
komal145 (6/18/2012)
select '10704' as entry_id , '2012-05-18 14:17:20.710' as Start_time ,'2012-05-18 14:18:13.957' as End_time ,'test3' as USernameUnion all
select '10704' as entry_id , '2012-05-18 14:24:45.057' as Start_time ,'2012-05-18 14:25:14.780' as End_time ,'NULL' as USername
hope you understand!!
hope you understand!! << Really?? Please drop the attitude, he really is trying to help.
June 18, 2012 at 2:50 pm
Lynn Pettis (6/18/2012)
komal145 (6/18/2012)
select '10704' as entry_id , '2012-05-18 14:17:20.710' as Start_time ,'2012-05-18 14:18:13.957' as End_time ,'test3' as USernameUnion all
select '10704' as entry_id , '2012-05-18 14:24:45.057' as Start_time ,'2012-05-18 14:25:14.780' as End_time ,'NULL' as USername
hope you understand!!
hope you understand!! << Really?? Please drop the attitude, he really is trying to help.
Lynn I don't think there is attitude here. I do think there is a bit of a language barrier and perhaps some misunderstanding on how to make this clear. At least I didn't read any attitude into it. 😀
_______________________________________________________________
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/
June 18, 2012 at 2:51 pm
komal145 (6/18/2012)
select '10704' as entry_id , '2012-05-18 14:17:20.710' as Start_time ,'2012-05-18 14:18:13.957' as End_time ,'test3' as USernameUnion all
select '10704' as entry_id , '2012-05-18 14:24:45.057' as Start_time ,'2012-05-18 14:25:14.780' as End_time ,'NULL' as USername
hope you understand!!
OK now your desired output is 100% clear. Makes testing a query a lot easier when I have something I can easily read to confirm.
_______________________________________________________________
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/
June 18, 2012 at 2:51 pm
Sean Lange (6/18/2012)
Lynn Pettis (6/18/2012)
komal145 (6/18/2012)
select '10704' as entry_id , '2012-05-18 14:17:20.710' as Start_time ,'2012-05-18 14:18:13.957' as End_time ,'test3' as USernameUnion all
select '10704' as entry_id , '2012-05-18 14:24:45.057' as Start_time ,'2012-05-18 14:25:14.780' as End_time ,'NULL' as USername
hope you understand!!
hope you understand!! << Really?? Please drop the attitude, he really is trying to help.
Lynn I don't think there is attitude here. I do think there is a bit of a language barrier and perhaps some misunderstanding on how to make this clear. At least I didn't read any attitude into it. 😀
You could say I took exception to the !! at the end of his statement. That to me was attitude that wasn't really needed.
June 18, 2012 at 2:58 pm
Sean Lange (6/18/2012)
komal145 (6/18/2012)
select '10704' as entry_id , '2012-05-18 14:17:20.710' as Start_time ,'2012-05-18 14:18:13.957' as End_time ,'test3' as USernameUnion all
select '10704' as entry_id , '2012-05-18 14:24:45.057' as Start_time ,'2012-05-18 14:25:14.780' as End_time ,'NULL' as USername
hope you understand!!
OK now your desired output is 100% clear. Makes testing a query a lot easier when I have something I can easily read to confirm.
Actually I take this back...it really isn't very clear. You said
ans user_name will be the user who has event_type_id=3 immedediately before it changes to event_type_id =6
But in your desired output that doesn't quite match. The first event type of 3 before a 6 then 1 is Test0. Or are you trying to find the first eventType of 3 AFTER the first 6 , 1.
I have to say this is an incredibly bizarre requirement...
_______________________________________________________________
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 18 total)
You must be logged in to reply to this topic. Login to reply