Create Status table Column and update

Question

Post reply

Create Status table Column and update

komal145

SSCrazy Eights

Points: 9918
More actions
May 23, 2012 at 10:16 am

#261476

I need Status Update column like :
1)All (both completed and pending i.e which went to Enrollment Analyst step’
2)Pending
3)Complete
Have Main table:
Create Table [dbo].[tbl_doc_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))
-------And have EVENT LOOK UP TABLE...just giving this table for understanding---
CREATE TABLE [dbo].[event_look_up](
[Event_Description] [varchar](50) NULL,
[Event_Type_Id] [int] NOT NULL)
Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)
values(1,'STATUS')
Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)
values(2,'FOLDERNAME')
Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)
values(3,'sys_Routing_Action')
Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)
values(4,'sys_WkfStep_Previous')
Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)
values(5,'sys_WkfStep_Current')
Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)
values(6,'Pending ')
---------------------------getting all records with current step is Enrollment Analyst-------------
select et.Event_Description, da.* from dbo.tbl_doc_activity da
inner join [dbo].[event_look_up] et
on da.Event_Type_Id = et.Event_Type_Id
where da.Event_Type_Id=5 and da.Event_Value like 'Enrollment Analyst'
------Incomplete since it has no additional current_step after "Enrollment_analsyt" : so it is in pending-----
Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(107,1,'Imported','2012-05-12 13:49:03.740','ABD')
Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(107,5,'Enrollment Analyst','2012-05-12 13:49:03.757','ABD2')
Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(107,1,'Routing','2012-05-12 13:49:07.927','ABD3')
Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(107,3,'ENROLLMENT TECHNICIAN','2012-05-12 13:49:07.943','ABD4'
--These are completed "Enrollment_analsyt_step as their previous step is "enrollment analyst"
or we can say that it has additional current_step after "Current_step"=Enrollment_analsyt------
select et.Event_Description, da.* from dbo.tbl_doc_activity da
inner join [dbo].[event_look_up] et
on da.Event_Type_Id = et.Event_Type_Id
where da.Event_Type_Id=4 and da.Event_Value like 'Enrollment Analyst'
Example:
------Completed Enrollment_step since entry has previous step =EA------------------------
Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(108,1,'Routing','2012-05-18 02:03:51.887','ABD')
Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(108,5,'Enrollment Analyst','2012-05-18 02:03:55.560','ABD2')
Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(108,5,'Intake','2012-05-18 02:03:46.373','ABD3')
Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(108,3,'INDEXING','2012-05-18 10:29:01.463','ABD4')
Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)
VALUES(108,4,'Enrollment Analyst','2012-05-18 10:53:06.597','ABD5')
------I need to Create some status table like below in which i have: status which tells:
--1) records which came out of enrollment_analyst step(completed) or whose previous_Step=enrollmet analsyt
--2) records still in enrollment analsyt i.e they have no current_step after current_step=enrollment_analsyt-----------
create Table #Status_of_Enrollment
( Entry_id int,
event_type_id int,
Event_value varchar(30),
Event_time varchar(30),
last_updated_user varchar(40),
Status varchar(40))
My Final table should look like :
Entry_id Event_type_idEvent_valueEvent_timeusernameStatus
108 4Enrollment Analyst2012-05-18 10:53:06.597ABD5Completed
107 5Enrollment Analyst2012-05-18 02:03:55.560ABD2pending
I know what to do , but don’t know how to do???
Any help help is appreciated.

Viewing 15 posts - 1 through 15 (of 19 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Pretty sure I understand what you are looking for but your sample data doesn't match your desired output.

Take a look at what you stated is desired output.

107 5 Enrollment Analyst 2012-05-18 02:03:55.560 ABD2 pending

There are no rows with that time stamp.

select et.Event_Description, da.*

from dbo.tbl_doc_activity da

inner join [dbo].[event_look_up] et

on da.Eventy_Type_Id = et.Event_Type_Id

where Entry_ID = 107

See if something like this will produce the correct results:

select *, Case Event_Value when 'Enrollment Analyst' then 'Completed' else 'Pending' end

from

(

select et.Event_Description, da.*, ROW_NUMBER() over(partition by Entry_ID order by Event_Time desc) as RowNum

from dbo.tbl_doc_activity da

inner join [dbo].[event_look_up] et

on da.Eventy_Type_Id =

et.Event_Type_Id

) x

where x.RowNum = 1

_______________________________________________________________

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/

komal145 SSCrazy Eights Points: 9918 More actions · Answer 2

From your query i am getting all the records whose current_step is not "enrollment_analyst and setting sattus = "pending"

But I need only those records which are in Current_step ="Enrollment_Analsyt" and those which are "pending " or completed"

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

komal145 (5/23/2012)
From your query i am getting all the records whose current_step is not "enrollment_analyst and setting sattus = "pending"
But I need only those records which are in Current_step ="Enrollment_Analsyt" and those which are "pending " or completed"

Your sample output showed two rows. If you can try to explain more clearly what you are looking for perhaps I can help you get the data you want.

_______________________________________________________________

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/

komal145 SSCrazy Eights Points: 9918 More actions · Answer 4

Example of entries which has no current+step=5 and event_value=’enrollment anaylst”---------