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 Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Obtaining old field value based on antother old field value

Status
Not open for further replies.

Scooby62

MIS
Jul 10, 2002
97
CA
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.
 
I think it's easier to understand when formatted this way (no color added for fairness):
Code:
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]
 
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.)
 
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?
 
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]
 
Just to clarify this will just be a formula field on a Crystal report.

Hope this helps.
 
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]
 
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.
 
It's ugly but it's probably faster than the sequence number approach.
Code:
[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]
 
Ok, there were a few problems and DateStamp was supposed to be unique. I'm appropriately humiliated, but better late than never. :)
Code:
[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]
 
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.
 
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top