Obtaining old field value based on antother old field value

[Scooby62]

Hi there.

I have a situation where I want to pull back a person's previous cost centre (department) only if they have had a department transfer.

So the logic is something like if person A has a change reason of department transfer go back to the record before this and get me their cost centre (department).

Anyone know how to do this?

Thanks.

 

[donutman]

A description of your table and a few examples of data rows would help.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Scooby62]

Yes that's true. Here's a couple of records from the table for one employee. It's a detail table so there are multiple records per employee

emlid emp emlchgrsn unt
8231 2135 New Hire 011090
11668 2135 Merit 011090
13131 2135 Merit 011090
13427 2135 Dept Transfer 011023
5623 2135 Merit 011023

I want a program to find the Dept Transfer record and go to the next earliest record and pull back the Unt. So in this example I see the new unt is 011023 and the old is 011090.

Does this make sense now?

Thanks.

 

[ESquared]

How is your data ordered? Row order should not be relied on, there must be an ORDER BY clause to ensure data integrity.

So how, for example, do we know that 13427 comes before 5623, and 13131 comes after 11668?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[Scooby62]

It is ordered by the employee number and a date stamp field which I did not include above.

 

[ESquared]

Assuming each DateStamp per EmpID transaction is unique...

SELECT
      EmpId,
      OldDept = O.Unt,
      NewDept,
      TransDate     
   FROM EmpDetail O
      INNER JOIN (
         SELECT
            E.EmpId,
            NewDept,
            TransDate,
            OldDate = Max(E.DateStamp)
         FROM
            EmpDetail E
            INNER JOIN (
               SELECT
                  EmpID,
                  TransDate = DateStamp,
                  NewDept = Unt
               FROM
                  EmpDetail
               WHERE
                  emlchgrsn = 'Dept Transfer'
            ) T ON E.EmpID = T.EmpID AND E.DateStamp < T.TransDate
         GROUP BY
            E.EmpID,
            NewDept,
            TransDate
      ) N ON E.EmpID = N.EmpID AND E.DateStamp = N.OldDate

If datestamps can be the same for different transactions, then this will be more complicated, but it can be done.

Also, this query will return multiple records if the employee has been transferred multiple times. If you only want the most recent transfer, you'll have to do some more modification.

There are other methods to grab the most recent record besides the one I used (such as numbering records) but for whatever reason I chose this one, using Max and joining on <.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

I think e² meant to use

ON O.EmpID = N.EmpID AND O.DateStamp = N.OldDate

for the last line of code. Also the INNER JOIN between table E and T should be a LEFT JOIN in order to accommodate those employees that have not transfered departments. I assume you want the current department in that event. Also this assumes that the department transfer transaction must be the first transaction possible within that department for a given employee. Of course, that is a reasonable assumption.
-Karl



[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Thanks, Karl!

You're right about the join criteria. A complicated query!

Also good call about the left join if he wants to see all employees rather than just those that have had a transfer. Actually, he could just do a UNION select with the New Hire lines:

{above query...}
UNION SELECT
      EmpID,
      0,
      Unt,
      DateStamp
   FROM
      EmpDetail
   WHERE 
      emlchgrsn = 'New Hire'
   ORDER BY
      EmpID,
      DateStamp

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[ESquared]

But maybe the LEFT JOIN is better... hmmmm...

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

Here's another approach:

[Blue]SELECT[/Blue] E1.emp[Gray],[/Gray] E1.unt[Gray],[/Gray] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]TimeStamp1[Gray])[/Gray] 
   [Blue]FROM[/Blue] [Gray]([/Gray][Blue]SELECT[/Blue] emp[Gray],[/Gray] unt[Gray],[/Gray] emlchgrsn[Gray],[/Gray] 
         [Fuchsia]MAX[/Fuchsia][Gray]([/Gray][Blue]timestamp[/Blue][Gray])[/Gray] TimeStamp1 
           [Blue]FROM[/Blue] EmpDetail 
           [Blue]WHERE[/Blue] emlchgrsn [Blue]IN[/Blue] [Gray]([/Gray][red]'New Hire'[/red][Gray],[/Gray] [red]'Dept Transfer'[/red][Gray])[/Gray]
           [Blue]GROUP[/Blue] [Blue]BY[/Blue] emp[Gray],[/Gray] unt[Gray])[/Gray] E1
   [Blue]INNER[/Blue] [Blue]JOIN[/Blue]
        [Gray]([/Gray][Blue]SELECT[/Blue] emp[Gray],[/Gray] unt[Gray],[/Gray] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray][Blue]timestamp[/Blue][Gray])[/Gray] TimeStamp2 
           [Blue]FROM[/Blue] EmpDetail 
           [Blue]WHERE[/Blue] emlchgrsn [Blue]IN[/Blue] [Gray]([/Gray][red]'New Hire'[/red][Gray],[/Gray] [red]'Dept Transfer'[/red][Gray])[/Gray]
           [Blue]GROUP[/Blue] [Blue]BY[/Blue] emp[Gray],[/Gray] unt[Gray])[/Gray] E2
   [Blue]ON[/Blue] [Gray]([/Gray]E1.emp[Gray]=[/Gray]E2.emp [Gray]AND[/Gray] 
          [Gray]([/Gray]TimeStamp1[Gray]<[/Gray]TimeStamp2 [Gray]OR[/Gray] E1.emlchgrsn[Gray]=[/Gray][red]'New Hire'[/red][Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Darn, forgot to add the final ) and Group By clause:

        [Gray]([/Gray]TimeStamp1[Gray]<[/Gray]TimeStamp2 [Gray]OR[/Gray] E1.emlchgrsn[Gray]=[/Gray][red]'New Hire'[/red][Gray]))[/Gray]  
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] E1.emp[Gray],[/Gray] E1.unt

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Karl,

If an employee has multiple transfers, won't your last query end up matching multiple records in the join for the 2nd and onward transfers, ending up with duplicate records? And if an employee transfers in or out of a particular department twice, the earlier transfer/new hire will be hidden.


-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[Scooby62]

All I can say is "Wow" you guys are awesome!

I will try all of this and let you know.

Thank you very much

 

[donutman]

I'm not sure if I understand your point unless you posted without seeing my final Group By clause that limits the rows to the Max(TimeStamp1). Have I missed something or does that in fact return the next to last transfer?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

OMG, totally bogus. I would to need to do another join! Can't believe I posted that.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

It's so complicated at this point that I'd need to set up some temp tables with fake data to find a better way.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

Hehe, I was heading into the same query as yours! Actually, this thread illustrates another reason to "think in terms of derived tables." You build them from the "ground up". And keep joining back to the base table until you get the fields you want. You just have to remember what your final goal is and not quit before then!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Now that we've bashed this to death, here's another method. I got tired of mistakes so I made a fake EmpDetails table.

CREATE TABLE
   #EmpDetails (
      DetailID int identity(1,1),
      EmpID int,
      ChangeReason varchar(20),
      Dept int,
      DateStamp smalldatetime
   )

INSERT INTO #EmpDetails (EmpID, ChangeReason, Dept, DateStamp)
SELECT 2135, 'New Hire',      011090, '1/15/2004 14:02' UNION
SELECT 2135, 'Merit',         011090, '3/02/2004 9:17' UNION
SELECT 2135, 'Merit',         011090, '4/20/2004 16:27' UNION
SELECT 2135, 'Dept Transfer', 011023, '6/10/2004 15:59' UNION
SELECT 2135, 'Merit',         011023, '8/27/2004 11:19' UNION
SELECT 2135, 'Dept Transfer', 011067, '9/29/2004 11:19' UNION
SELECT 2157, 'New Hire',      011023, '2/01/2004 15:03' UNION
SELECT 2192, 'New Hire',      011023, '1/15/2003 14:02' UNION
SELECT 2192, 'Merit',         011023, '3/02/2003 9:17' UNION
SELECT 2192, 'Merit',         011023, '4/20/2003 16:27' UNION
SELECT 2192, 'Dept Transfer', 011045, '6/10/2003 15:59' UNION
SELECT 2192, 'Merit',         011045, '8/27/2003 11:19' UNION
SELECT 2192, 'Dept Transfer', 011023, '9/29/2003 11:19'

SELECT * From #EmpDetails

SELECT
   S1.EmpId,
   OldDept,
   NewDept,
   TransDate      
FROM
   (
      SELECT
         E1.EmpId,
         TransDate = E1.DateStamp,
         NewDept = E1.Dept,
         SeqNum = Count(*)
      FROM
         #EmpDetails E1
         INNER JOIN #EmpDetails E2
            ON E1.EmpId = E2.EmpID
            AND E1.DateStamp >= E2.DateStamp
      GROUP BY
         E1.EmpID,
         E1.Dept,
         E1.DateStamp,
         E1.ChangeReason
      HAVING
         E1.ChangeReason IN ('Dept Transfer', 'New Hire')
   ) S1 LEFT JOIN (
      SELECT
         E1.EmpId,
         OldDept = E1.Dept,
         SeqNum = Count(*)
      FROM
         #EmpDetails E1
         INNER JOIN #EmpDetails E2
            ON E1.EmpId = E2.EmpID
            AND E1.DateStamp >= E2.DateStamp
      GROUP BY
         E1.EmpId,
         E1.Dept,
         E1.DateStamp
   ) S2 ON S2.EmpId = S1.EmpID AND S2.SeqNum = S1.SeqNum - 1
ORDER BY
   S1.EmpId,
   TransDate

DROP TABLE #EmpDetails

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[ESquared]

P.S.

This can be understood more simply as follows.

Create a view called EmpDetailsSequence:

SELECT
      E1.EmpId,
      E1.DateStamp,
      Dept = E1.Dept,
      E1.ChangeReason,
      SeqNum = Count(*)
   FROM
      #EmpDetails E1
      INNER JOIN #EmpDetails E2
         ON E1.EmpId = E2.EmpID
         AND E1.DateStamp >= E2.DateStamp
   GROUP BY
      E1.EmpID,
      E1.Dept,
      E1.DateStamp,
      E1.ChangeReason

Now, referencing this view, my last query looks like this:

SELECT
   S1.EmpId,
   OldDept = S2.Dept,
   NewDept = S1.Dept,
   TransDate = S1.DateStamp
FROM
   EmpDetailsSequence S1
   LEFT JOIN EmpDetailsSequence S2 ON S2.EmpId = S1.EmpID AND S2.SeqNum = S1.SeqNum - 1
WHERE
   S1.ChangeReason IN ('Dept Transfer', 'New Hire')
ORDER BY
   S1.EmpId,
   TransDate

Which should make things fairly clear.

Like I've said before, one of the cool things about derived tables is that they can be run independently of the main query... totally useful for debugging or splitting out as in this fashion.

This latter method is probably slower because it has to apply the ChangeReason criterion after the whole view is 'created.' Speaking of which, in my above query, that HAVING should have been a WHERE...

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[ESquared]

... and E1.ChangeReason can be removed from the GROUP BY List for derived table S1 in the full query starting in post "Now that we've bashed this to death..."



-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)