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!

Register Log in
  • Congratulations Rhinorhino 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

Scooby62

MIS
Joined
Jul 10, 2002
Messages
97
Location
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.
 
Sort by date Sort by votes
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]
 
Upvote 0 Downvote
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.)
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
Just to clarify this will just be a formula field on a Crystal report.

Hope this helps.
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
That last On clause should be
on E12.ds=E3.DateStamp
-Karl
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question Question
Pick up value from combobox
Replies
0
Views
788
bharons
bharons
SashaBuilder3
  • Locked
  • Question Question
select a single row based on conditions in multiple rows
Replies
3
Views
1K
SashaBuilder3
SashaBuilder3
gussy81
  • Locked
  • Question Question
Decimal Datatype with negative value
Replies
5
Views
2K
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question Question
Field types question
Replies
9
Views
950
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question Question
How to make pivot for features dynamically based on feature name and feature value ?
Replies
1
Views
465
TamarGranor
TamarGranor

Part and Inventory Search

Sponsor

Back
Top