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]

I think it's easier to understand when formatted this way (no color added for fairness):

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
          WHERE E1.ChangeReason IN ('Dept Transfer', 
                                     'New Hire') 
          GROUP BY E1.EmpID, E1.Dept, 
                   E1.DateStamp, E1.ChangeReason) 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

but then, it's harder to understand than your first version. Although it does generate a more complete result than requested. Yes, we have beat it to death...another familiar result.
-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]

Can you think of a third way to get the previous record, besides the two already noted here?

-------------------------------------
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]

Okay Guys, thank you.

This does seem way more complicated than I was anticipating. I was thinking of an IF statement. Something like if the employees change reason equals 'dept transfer' then go one record back and get their UNT (department number).

I still think this can be done a lot simpler.

Comments, suggestions?

 

[donutman]

Scooby62, you can always do it in the app with sequential reads. It's not as elegant but it's very easy to understand.
Eric, I'll work on it.
-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]

Just to clarify this will just be a formula field on a Crystal report.

Hope this helps.

 

[donutman]

OMG, it's a snap in Crystal reports as long as you can accept the data in TimeStamp order. You can catch the change in Dept with the running total formulas.
-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]

This sounds good I'll give it a try.

Thanks so much for all the time you guys have put into my question. I'm blown away at your dedication. It means a lot.

Take care.

 

[donutman]

It's ugly but it's probably faster than the sequence number approach.

[Blue]CREATE[/Blue] [Blue]TABLE[/Blue]
   #EmpDetails [Gray]([/Gray]
      DetailID [Blue]int[/Blue] [Fuchsia]Identity[/Fuchsia][Gray]([/Gray]1[Gray],[/Gray]1[Gray])[/Gray][Gray],[/Gray]
      EmpID [Blue]int[/Blue][Gray],[/Gray]
      ChangeReason [Blue]varchar[/Blue][Gray]([/Gray]20[Gray])[/Gray][Gray],[/Gray]
      Dept [Blue]int[/Blue][Gray],[/Gray]
      DateStamp [Blue]smalldatetime[/Blue]
   [Gray])[/Gray]

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

[Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] E3.EmpID[Gray],[/Gray] E3.Dept [Blue]FROM[/Blue] #EmpDetails E3
   [Blue]INNER[/Blue] [Blue]JOIN[/Blue] 
[Gray]([/Gray][Blue]SELECT[/Blue] EmpId[Gray],[/Gray] Dept[Gray],[/Gray] [Fuchsia]MIN[/Fuchsia][Gray]([/Gray]DateStamp[Gray])[/Gray] ds [Blue]FROM[/Blue] #EmpDetails E1
   [Blue]WHERE[/Blue] DateStamp [Blue]IN[/Blue] 
      [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]TOP[/Blue] 2 DateStamp [Blue]FROM[/Blue] #EmpDetails E2
          [Blue]WHERE[/Blue] ChangeReason [Blue]IN[/Blue] [Gray]([/Gray][red]'New Hire'[/red][Gray],[/Gray] [red]'Dept Transfer'[/red][Gray])[/Gray]
             [Gray]AND[/Gray] E1.EmpID[Gray]=[/Gray]E2.EmpID
          [Blue]ORDER[/Blue] [Blue]BY[/Blue] DateStamp[Gray])[/Gray]
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] EmpId[Gray],[/Gray] Dept[Gray])[/Gray] E12 
   [Blue]ON[/Blue] E12.EmpId[Gray]=[/Gray]E3.EmpID [Gray]AND[/Gray] E12.Dept[Gray]=[/Gray]E3.Dept

[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #EmpDetails

-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]

That last On clause should be
on E12.ds=E3.DateStamp
-Karl

 

[donutman]

Ok, there were a few problems and DateStamp was supposed to be unique. I'm appropriately humiliated, but better late than never.

[Blue]CREATE[/Blue] [Blue]TABLE[/Blue]
   #EmpDetails [Gray]([/Gray]

      DetailID [Blue]int[/Blue] [Fuchsia]Identity[/Fuchsia][Gray]([/Gray]1[Gray],[/Gray]1[Gray])[/Gray][Gray],[/Gray]
      EmpID [Blue]int[/Blue][Gray],[/Gray]
      ChangeReason [Blue]varchar[/Blue][Gray]([/Gray]20[Gray])[/Gray][Gray],[/Gray]
      Dept [Blue]int[/Blue][Gray],[/Gray]
      DateStamp [Blue]smalldatetime[/Blue]
   [Gray])[/Gray]

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

[Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] E3.EmpID[Gray],[/Gray] E3.Dept [Blue]FROM[/Blue] #EmpDetails E3
   [Blue]INNER[/Blue] [Blue]JOIN[/Blue] 
[Gray]([/Gray][Blue]SELECT[/Blue] EmpId[Gray],[/Gray] [Fuchsia]MIN[/Fuchsia][Gray]([/Gray]DateStamp[Gray])[/Gray] ds [Blue]FROM[/Blue] #EmpDetails E1
   [Blue]WHERE[/Blue] DateStamp [Blue]IN[/Blue] 
      [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]TOP[/Blue] 2 DateStamp [Blue]FROM[/Blue] #EmpDetails E2
          [Blue]WHERE[/Blue] ChangeReason [Blue]IN[/Blue] [Gray]([/Gray][red]'New Hire'[/red][Gray],[/Gray] [red]'Dept Transfer'[/red][Gray])[/Gray]
             [Gray]AND[/Gray] E1.EmpID[Gray]=[/Gray]E2.EmpID
          [Blue]ORDER[/Blue] [Blue]BY[/Blue] DateStamp [Blue]DESC[/Blue][Gray])[/Gray]
   [Blue]GROUP[/Blue] [Blue]BY[/Blue] EmpId[Gray])[/Gray] E12 
   [Blue]ON[/Blue] E12.ds[Gray]=[/Gray]E3.DateStamp

[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #EmpDetails

[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]

Again, thank you all.

I actually got it working in Crystal and it's very simple as I originally thought.

You guys rock! Thanks so much for your never ending support.

This one's closed.

 

[donutman]

Glad it worked out for you. We had a good time with it as you can see.
-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]