Event history query - peer review please? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Event history query - peer review please?

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2009-12-11 : 04:24:17

Hi,

I've got a history of employment events theat looks something like this:

create table #events
(parentuid int,
effdate datetime,
shortdesc varchar(10))

insert into #Events select 1743499,'Apr 22 2003 12:00AM', 'Employed'
insert into #Events select 1743499,'Nov 13 2005 12:00AM', 'Maternity'
insert into #Events select 1743499,'May 31 2006 12:00AM', 'Leaves'
insert into #Events select 1743499,'Jun  1 2007 12:00AM', 'Transfer out event'

As part of a larger query I need to return this information so it looks something like this:

Parentuid   Event1      Effdate    Event2      Effdate
---------   -------     ------     --------    --------
1743499     Employed    22/04/03   Maternity   13/11/05
1743499     Maternity   13/11/05   Leaves      31/05/06
1743499     Leaves      31/05/06   Transfer    01/06/07
1743499     Transfer    01/06/07   Now         getdate()

I usually get this kind of info with 2 alias tables - one with the 'start' kind of events, and one with the 'end' type of events. I then join the 2 tables together where the 'start' dates are less than the 'end' dates. Like this:

select
PS.parentuid,
PS.effdate,
PS.shortdesc,
isnull(PE.Effdate, getdate()) effdate,
isnull(PE.shortdesc, 'Right Now') shortdesc

from

      (select
      e.parentuid,
      e.effdate,
      e.shortdesc
      from #events e
      ) PS

left join

      (select
      e.parentuid,
      e.effdate,
      e.shortdesc
      from #events e
      ) PE

on pe.parentuid = ps.parentuid 
and ps.effdate < pe.effdate

group by ps.parentuid, ps.effdate, ps.shortdesc, pe.effdate, pe.shortdesc

order by ps.parentuid

drop table #events

Unfortunately I can't use this because there are no real 'start' or 'end' events - a person could be employed, then leave, then be re-employed, then go on maternity or part time, or they could start off on part time and move to employed etc. etc.

So the only solution I've been able to come up with is to ID a temporary table, then join my 2 alias tables to each other on the 'start'.id = 'end'.id - 1. Like this:

create table #events
(
ID int identity(1,1),
parentuid int,
effdate datetime,
shortdesc varchar(20))

insert into #Events select 1743499,'Apr 22 2003 12:00AM', 'Employed'
insert into #Events select 1743499,'Nov 13 2005 12:00AM', 'Maternity'
insert into #Events select 1743499,'May 31 2006 12:00AM', 'Leaves'
insert into #Events select 1743499,'Jun  1 2007 12:00AM', 'Transfer out event'

select
PS.parentuid,
PS.effdate,
PS.shortdesc,
isnull(PE.Effdate, getdate()) effdate,
isnull(PE.shortdesc, 'Right Now') shortdesc

from

      (select
      e.id,
      e.parentuid,
      e.effdate,
      e.shortdesc
      from #events e
      ) PS

left join

      (select
      e.id,
      e.parentuid,
      e.effdate,
      e.shortdesc
      from #events e
      ) PE

on pe.parentuid = ps.parentuid 
and ps.id = pe.id -1

group by ps.parentuid, ps.effdate, ps.shortdesc, pe.effdate, pe.shortdesc

order by ps.parentuid

This seems to work fine but I was wondering if there was a more elegant way of getting what I needed?

Cheers,

Yonabout

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-11 : 06:04:29

Can you upgrade to sql server 2005?

Then you could use the windowing functions like ROW_NUMBER()

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

yonabout
Posting Yak Master

112 Posts

Posted - 2009-12-11 : 06:43:21

Sadly I can't upgrade to 2005 in time for when I need this query.

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources