Getting Worker History from Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting Worker History from Table

Author

Topic

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-15 : 15:47:04

Hello everyone,

I have a table called "CaseHistory" which has the following columns: CaseHistoryID, CaseID, DepartmentID, UserID, Entered, Closed, Accessed.

What I need to do is create a new table that lists the worker history for each case. Basically I need the following information: CaseID, UserID, StartDate, EndDate where StartDate is the date the worker started on the case and EndDate is the date the worker stopped working on the case.

The way CaseHistory is updated is that everytime there is something done on the case, this table is updated with the required updates and "Accessed" is the date the changes were made. So if the worker was involved from the beginning and there were 7 changes made on different dates, the table is updated 7 times with new Accessed dates (UserID would be unchanged, other columns would be different).

Any ideas on how I can get the worker history for this beast??

Thanks!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-15 : 15:57:28

you mean this?


select CaseID
       , UserID
       , min(accessed) as StartDate
       , max(accessed) as EndDate
from  casehistory
group by CaseID
       , UserID

Be One with the Optimizer
TG

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-16 : 00:04:41

Thanks for the reply TG! Although your solution would work for some cases it will not work for others. The reason is because the user could be the worker more than once while the case is open. This is where my problem kicks my logic to the curb. For example a worker could go on leave so the case is assigned to someone else. Upon the worker returning the case is assigned back to the original worker.

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-16 : 08:29:10

Just wanted to clarify things as I realize now that it may be more difficult that I previously thought. Below I have given an example of what the table might look like for a case.

CaseHistoryID -- CaseID -- DepartmentID -- UserID -- Entered -- Closed -- Accessed (notes about what happened - not actually a database field)

1 -- 12345 -- 2 -- 125 -- 2014/01/23 -- NULL -- 2014/01/23 14:43:23.392 (first entry for this case)
2 -- 12345 -- 2 -- 125 -- 2014/01/23 -- NULL -- 2014/01/27 14:43:23.392 (another field not shown was updated)
3 -- 12345 -- 2 -- 15 -- 2014/01/23 -- NULL -- 2014/02/09 14:43:23.392 (worker changed)
4 -- 12345 -- 2 -- 125 -- 2014/01/23 -- NULL -- 2014/03/15 14:43:23.392 (worker changed back to original worker)
5 -- 12345 -- 2 -- 125 -- 2014/01/23 -- 2014/04/10 -- 2014/04/10 14:43:23.392 (case was closed)

So what I need is the history to show:

CaseID -- UserID -- StartDate -- EndDate
12345 -- 125 -- 2014/01/23 14:43:23.392 -- 2014/02/09 14:43:23.392
12345 -- 15 -- 2014/02/09 14:43:23.392 -- 2014/03/15 14:43:23.392
12345 -- 125 -- 2014/03/15 14:43:23.392 -- 2014/04/10 14:43:23.392

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-16 : 11:39:29

Ok - here's one way that should work.


if object_id('tempdb.dbo.#ch') is not null
       drop table #ch
create table #ch 
       (rid int identity(1,1) primary key clustered
       , caseid int
       , userid int
       , accessed datetime
       , grp int null
       , startDate datetime null
       , endDate datetime null)

-----------------------------------------------------------------------
--This is just my sample data - you won't need this part obviously
;with caseHistory (caseid, userid, accessed) as
(
       select 1,1,'2013-04-16 10:00:00.000' union all
       select 1,1,'2013-04-16 10:00:01.000' union all
       select 1,2,'2013-04-16 10:00:02.000' union all
       select 1,1,'2013-04-16 10:00:03.000' union all
       select 1,1,'2013-04-16 10:00:04.000' union all
       select 1,3,'2013-04-16 10:00:05.000' union all
       select 1,1,'2013-04-16 10:00:06.000' union all
       select 2,1,'2013-04-16 10:00:00.000' union all
       select 2,1,'2013-04-16 10:00:01.000' union all
       select 2,2,'2013-04-16 10:00:02.000' union all
       select 2,1,'2013-04-16 10:00:03.000' union all
       select 2,1,'2013-04-16 10:00:04.000'
)
-----------------------------------------------------------------------

--create a temp table with a clustered index so we can apply an update of type: @var=col=expression
insert #ch (caseid, userid, accessed)
select caseid, userid, accessed
from   caseHistory
order by caseid, accessed

----------------------------------------------
--establish a Grp to Group By
declare @grp int
       ,@caseid int
       ,@userid int

select @grp = 1
       ,@caseid = caseid
       ,@userid = userid
from   #ch
where  rid = 1

update h set
       @grp = h.grp = case when caseid = @caseid and userid = @userid then @grp else @grp+1 end
       ,@caseid = h.caseid
       ,@userid = h.userid
from #ch h

----------------------------------------------
--initialize the start and end dates.  
--The endDates may change in the next statement if multiple users worked on the same case

update h set
       h.startDate = d.startDate
       ,h.enddate = d.endDate
from   #ch h
join   (
       select grp
              ,min(accessed) as StartDate
              ,max(accessed) as endDate
       from #ch
       group by grp
       ) d on d.grp = h.grp

----------------------------------------------
--set the endDate as the accessed date of the next user when multiple users worked the same case

update h set
       h.endDate = d.endDate
from   #ch h
join   (
       select h1.grp
              ,min(h2.accessed) as endDate
       from   #ch h1
       inner join #ch h2 on h2.caseid = h1.caseid and h2.grp = h1.grp+1
       group by h1.grp
       ) d on d.grp = h.grp

----------------------------------------------
--select out the results
select distinct caseid, userid, startDate, endDate
from   #ch
order by caseid, startDate, endDate

OUTPUT:
caseid      userid      startDate               endDate
----------- ----------- ----------------------- -----------------------
1           1           2013-04-16 10:00:00.000 2013-04-16 10:00:02.000
1           2           2013-04-16 10:00:02.000 2013-04-16 10:00:03.000
1           1           2013-04-16 10:00:03.000 2013-04-16 10:00:05.000
1           3           2013-04-16 10:00:05.000 2013-04-16 10:00:06.000
1           1           2013-04-16 10:00:06.000 2013-04-16 10:00:06.000
2           1           2013-04-16 10:00:00.000 2013-04-16 10:00:02.000
2           2           2013-04-16 10:00:02.000 2013-04-16 10:00:03.000
2           1           2013-04-16 10:00:03.000 2013-04-16 10:00:04.000

Be One with the Optimizer
TG

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-16 : 11:55:17

You, TG, are AMAZING!!! This is exactly what I need! Around here people say they need to clone me because of the things I come up with to get them their results. This far exceeds what I would have even dreampt of so I can only imagine what they would think of your skills.

I haven't gone through all of the data yet, but for a few of the cases that I have checked so far the results are perfect.

Thank you so much!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-16 : 11:57:11

Please take all the credit and ask for a raise

Be One with the Optimizer
TG

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-25 : 11:04:04

Hello TG! I'm hoping you have a chance to look at this reply :) I'm going through the exercise of verifying the information from this so that I can move forward to the next phase of my project. However I have run into an issue of end dates being assigned even if the case is still open. Basically everything is correct with the exception of the last worker on a case that is still open should not have an end date. What I have found so far is that the end date corresponds to the last accessed date for this particular worker. I need to show it as null. I've tried to do some CASE type work where I basically say if the case is still open then return NULL and if the case is closed give me the max(accessed)...however it keeps erroring out on me.

Any thoughts?

Thanks,

Richard.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-25 : 11:12:38

>>however it keeps erroring out on me
post your code and the exact error you're getting.

or:

populate sample data that would illustrate the issue using code like above. add column(s) if you need and post the desired results based on your data.


--This is just my sample data - you won't need this part obviously
;with caseHistory (caseid, userid, accessed) as

Be One with the Optimizer
TG

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-25 : 11:30:55

Thanks for the very prompt reply! I'll do both actually. Here is the latest code that I've tried:

"if object_id('tempdb.dbo.#cha') is not null
drop table #cha
create table #cha
(rid int identity(1,1) primary key clustered
, caseid int
, familyid int
, userid int
, accessed datetime
, grp int null
, startDate datetime null
, endDate datetime null
, involvementtype CHAR(10) null)
--create a temp table with a clustered index so we can apply an update of type: @var=col=expression
insert #cha (caseid, familyid, userid, accessed, involvementtype)
select caseid, familyid, userid, accessed, null
from caseHistory
where UserId NOT IN (-1,0)
AND PersonId = -1
order by caseid, accessed

declare @grp int
,@caseid int
,@userid int

select @grp = 1
,@caseid = caseid
,@userid = userid
from #cha
where rid = 1

update h set
@grp = h.grp = case when caseid = @caseid and userid = @userid then @grp else @grp+1 end
,@caseid = h.caseid
,@userid = h.userid
from #cha h

update h set
h.startDate = d.startDate
,h.enddate = d.endDate
from #cha h
join (
select hh.grp
,min(hh.accessed) as StartDate
,max(CASE
WHEN (SELECT c.Closed
FROM Cases c
WHERE hh.caseid = c.CaseId) IS NOT NULL
THEN accessed
ELSE NULL
END) as endDate
from #cha hh
group by grp
) d on d.grp = h.grp

update h set
h.endDate = d.endDate
from #cha h
join (
select h1.grp
,min(h2.accessed) as endDate
from #cha h1
inner join #cha h2 on h2.caseid = h1.caseid and h2.grp = h1.grp+1
group by h1.grp
) d on d.grp = h.grp

select distinct caseid, familyid, userid, startDate, endDate, involvementtype
from #cha
order by familyid, caseid, startDate, endDate"

The error I get is "Cannot perform an aggregate function on an expression containing an aggregate or a subquery".

The case history table has the following: CaseID -- Entered -- Closed -- UserID -- Accessed -- etc (same as above basically). For the case history the entries would be similar to what I have above with one exception: The Closed field is NOT populated until the case is actually closed. So for the first few entries there would be no closed date, the last entry would have the closed date. What I would like as results would be something like:

CaseID -- UserID -- StartDate -- EndDate

12345 -- 25 -- 1/1/2014 -- 2/1/2014
12345 -- 124 -- 2/1/2014 -- 3/25/2014
12345 -- 64 -- 3/25/2014 -- NULL

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-25 : 15:14:52

You say that [Closed] is in [CaseHistory] but in your code you seem to be pulling it from a table called [Cases].
So that we don't go back and forth on this please take the extra trouble to populate (with actual executable code) your sample data. Use the format that I did above in this section:
--This is just my sample data - you won't need this part obviously

But make sure it has your actual columns. Then make sure to post the expected output based on your sample data. Finally use [ code][ /code] tags (without the spaces) to enclose your code so that you don't lose your formatting.

If [Closed] is in another table then provide that table as well in the same format.

friendly warning:
if you just post more text like this I'm not going to take the time to type it all into executable code - i'll just move on to the actual work I'm supposed to be doing

quote:

CaseID -- UserID -- StartDate -- EndDate
12345 -- 25 -- 1/1/2014 -- 2/1/2014
12345 -- 124 -- 2/1/2014 -- 3/25/2014
12345 -- 64 -- 3/25/2014 -- NULL

Be One with the Optimizer
TG

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-25 : 15:56:08

Thank you TG! I will do this on Monday if you don't mind. It's just about quitting time and I won't have time to finish it off as I cannot stay later tonight.

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-28 : 08:28:31

Hello TG! Sorry for the delay in getting this. After looking through what I posted again I realised an error and reading your post confirmed it :) The closed date that I am trying to get is from the CaseHistory table as well, not Cases. Hopefully I am doing this correctly:


-----------------------------------------------------------------------
--
;with caseHistory (caseid, userid, accessed,closed) as
(
       select 1,1,'2013-04-16 10:00:00.000',NULL union all
       select 1,1,'2013-04-16 10:00:01.000',NULL union all
       select 1,2,'2013-04-16 10:00:02.000',NULL union all
       select 1,1,'2013-04-16 10:00:03.000',NULL union all
       select 1,1,'2013-04-16 10:00:04.000',NULL union all
       select 1,3,'2013-04-16 10:00:05.000',NULL union all
       select 1,1,'2013-04-16 10:00:06.000','2014-03-24 00:00:00.000' union all
       select 2,1,'2013-04-16 10:00:00.000',NULL union all
       select 2,1,'2013-04-16 10:00:01.000',NULL union all
       select 2,2,'2013-04-16 10:00:02.000',NULL union all
       select 2,1,'2013-04-16 10:00:03.000',NULL union all
       select 2,1,'2013-04-16 10:00:04.000',NULL
)
-----------------------------------------------------------------------

OUTPUT:
caseid      userid      startDate               endDate
----------- ----------- ----------------------- -----------------------
1           1           2013-04-16 10:00:00.000 2013-04-16 10:00:02.000
1           2           2013-04-16 10:00:02.000 2013-04-16 10:00:03.000
1           1           2013-04-16 10:00:03.000 2013-04-16 10:00:05.000
1           3           2013-04-16 10:00:05.000 2013-04-16 10:00:06.000
1           1           2013-04-16 10:00:06.000 2014-03-24 00:00:00.000
2           1           2013-04-16 10:00:00.000 2013-04-16 10:00:02.000
2           2           2013-04-16 10:00:02.000 2013-04-16 10:00:03.000
2           1           2013-04-16 10:00:03.000 NULL

Essentially, for the cases that are still open I need to show that the current worker does not have an end date.

Thanks,

Richard

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-29 : 15:11:05

Thanks - that is exactly what I needed. You were very close. You just over complicated the CASE statement with an unnecessary sub query:


if object_id('tempdb.dbo.#ch') is not null
       drop table #ch
create table #ch 
       (rid int identity(1,1) primary key clustered
       , caseid int
       , userid int
       , accessed datetime
       , closed datetime
       , grp int null
       , startDate datetime null
       , endDate datetime null)

-----------------------------------------------------------------------
--This is just my sample data - you won't need this part obviously
;with caseHistory (caseid, userid, accessed,closed) as
(
       select 1,1,'2013-04-16 10:00:00.000',NULL union all
       select 1,1,'2013-04-16 10:00:01.000',NULL union all
       select 1,2,'2013-04-16 10:00:02.000',NULL union all
       select 1,1,'2013-04-16 10:00:03.000',NULL union all
       select 1,1,'2013-04-16 10:00:04.000',NULL union all
       select 1,3,'2013-04-16 10:00:05.000',NULL union all
       select 1,1,'2013-04-16 10:00:06.000','2014-03-24 00:00:00.000' union all
       select 2,1,'2013-04-16 10:00:00.000',NULL union all
       select 2,1,'2013-04-16 10:00:01.000',NULL union all
       select 2,2,'2013-04-16 10:00:02.000',NULL union all
       select 2,1,'2013-04-16 10:00:03.000',NULL union all
       select 2,1,'2013-04-16 10:00:04.000',NULL
)

-----------------------------------------------------------------------

--create a temp table with a clustered index so we can apply an update of type: @var=col=expression
insert #ch (caseid, userid, accessed, closed)
select caseid, userid, accessed, closed
from   caseHistory
order by caseid, accessed

----------------------------------------------
--establish a Grp to Group By
declare @grp int
       ,@caseid int
       ,@userid int

select @grp = 1
       ,@caseid = caseid
       ,@userid = userid
from   #ch
where  rid = 1

update h set
       @grp = h.grp = case when caseid = @caseid and userid = @userid then @grp else @grp+1 end
       ,@caseid = h.caseid
       ,@userid = h.userid
from #ch h

----------------------------------------------
--initialize the start and end dates.  
--The endDates may change in the next statement if multiple users worked on the same case

update h set
       h.startDate = d.startDate
       ,h.enddate = d.endDate
from   #ch h
join   (
       select grp
              ,min(accessed) as StartDate
              ,case when max(closed) is null then null else max(accessed) end as endDate
       from #ch
       group by grp
       ) d on d.grp = h.grp

----------------------------------------------
--set the endDate as the accessed date of the next user when multiple users worked the same case

update h set
       h.endDate = d.endDate
from   #ch h
join   (
       select h1.grp
              ,min(h2.accessed) as endDate
       from   #ch h1
       inner join #ch h2 on h2.caseid = h1.caseid and h2.grp = h1.grp+1
       group by h1.grp
       ) d on d.grp = h.grp

----------------------------------------------
--select out the results

select distinct caseid, userid, startDate, endDate
from   #ch
order by caseid, startDate, endDate

OUTPUT:

caseid      userid      startDate               endDate
----------- ----------- ----------------------- -----------------------
1           1           2013-04-16 10:00:00.000 2013-04-16 10:00:02.000
1           2           2013-04-16 10:00:02.000 2013-04-16 10:00:03.000
1           1           2013-04-16 10:00:03.000 2013-04-16 10:00:05.000
1           3           2013-04-16 10:00:05.000 2013-04-16 10:00:06.000
1           1           2013-04-16 10:00:06.000 2013-04-16 10:00:06.000
2           1           2013-04-16 10:00:00.000 2013-04-16 10:00:02.000
2           2           2013-04-16 10:00:02.000 2013-04-16 10:00:03.000
2           1           2013-04-16 10:00:03.000 NULL

Be One with the Optimizer
TG

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-30 : 12:51:21

Thank you so much TG!! I've been busy with other things the past couple of days but snuck in the adjustment just now and from what I can tell, it appears to be working properly. I've done a few spot checks and the results from your query are perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources