Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple Rows to Single Row

Status
Not open for further replies.

chazgaz

Technical User
May 14, 2004
13
US
I have a table that creates a new row of data each time an employees record is updated.

In the example below, I have an employee that has been hired, terminated, rehired, reterminated, and rehired again.

I would like for my query results to associate the employee terminate row with the appropriate hire row... meaning that the oldest terminate row would be tied to the oldest hire row, the second terminate date would be tied to the second hire row, etc...

Current Table Layout / SQL Results
ID EFFDT E_STATUS ACT
1234567 6/8/2005 A H
1234567 7/30/2005 T T
1234567 12/19/2005 A R
1234567 1/14/2006 T T
1234567 8/15/2006 A R


Desired SQL Results
ID EFFDT E_STATUS ACT EFFDT2 E_STATUS2 ACT2
1234567 6/8/2005 A H 7/30/2005 T T
1234567 12/19/2005 A R 1/14/2006 T T
1234567 8/15/2006 A R

Below is the code I have used to generate the Current SQL Results above. Please advise if there is a way to look for an and E_STATUS of 'T' and associate it with the first 'H' or 'R' EFFDT less than the 'T' EFFDT. Since there isn't a 'T' row for the last hire date, I would like those result to remain empty.

Hopefully this makes some sense.

select
a.id
,a.effdt
,a.e_status
,a.act

from
x_ps_job a

where
a.emplid = '1234567'
and a.act in ('H','R','T')

order by
a.id

 
Hi,

I was hoping someone would respond, as I work with this issue all the time. Since the Job table does not hold a true 'end date' for each row, this can only be done programatically.

What I do to get around this is to do the result sorting in Excel. Run your query to Excel - just picking up the hire, erhire and termiation actions. Sort the result set by emplid, date sequence, and then write a formula to test emplid matches betwen rows, action types etc to pivot the results.

So a result set like
ID date Action
123 2/2/06 HIR
123 2/5/06 TER

The excel formula would be something like ' =if(a2=a3,if(a3='HIR',if(c3 = 'TER', c2-1,""))) you can extend this to bring in actions etc as required.

Would give you the pivoted row - with the last working day as the end date rather than the 'termination date'. I have ignored status as this is derivable from the action.

I have tried it with query using an outer join, but since you need to evaluate max effective dates, to match appropriate dates and actions it can't work.

Hope this helps - but would be good to see a sql example of how to do this reliably.

Good luck.
 
Code:
select 
  a.id,a.effdt,a.e_status,a.act,
  b.effdt,b.e_status,b.act
from
 (  
  select
    id,effdt,e_status,act,
    (select min(effdt) 
     from x_ps_job a2
     where a2.act = 'T' 
     and a2.effdt > a.effdt) as nextdt
  from
    x_ps_job a
  where
    a.emplid = '1234567'
  and a.act in ('H','R')
 ) a 
left join 
 (  
  select
    id,effdt,e_status,act
  from
    x_ps_job
  where
    emplid = '1234567'
  and act in = 'T'
 ) b
on a.id = b.id 
and a.nextdt = b.effdt
If your RDBMs supports SQL:1999 OLAP-functions, it's probably easier...

Dieter
 
Hi Dieter,

This would work ok if everyone had the same data pattern as the provided example employee.

Where the issue is with this approach is that any ID with only a 'H' event will not return since they do not have a corresponding 'T' row and potentially 'H' row following. I guess it could probably be done using your code, then a union to another query that tests for the second scenario...

Maybe I am reading too much into chazgaz's question. Great code though - will have a play with it to see what I can do to make it work on this site (DB2).
 
Hi notadba,

"a 'H' event will not return since they do not have a corresponding 'T' row"

Of course it will, that's why i used an Outer Join :)

Dieter
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top