Compare records and insert data

[dm5085]

Hi All,
I don't do things this odd very often so I need someone to point me in the right direction. I have a table name STGDEPTTREE with data as follows

Deptid Tree Effdt BU
0000A 12/1/2005 ALL
0000A 1/1/2006 ALL
0000A 2/1/2006 ALL
0000A 3/1/2006 ALL
0000A 4/1/2006 ALL
0000A 5/1/2006 ALL
0000A 6/1/2006 ALL
0000A 7/1/2006 FIN
0000A 8/1/2006 FIN

There are 2 parts to what I need to do. First, identify if the DeptID has changed BU from one date to the next.
If it has (as 7/1/2006 example), I need to insert a row in another table called TBLJOBHISTORY that looks like this

EmplID EffDate Action Reason StdHrs DeptID BU
00001 1/1/2006 DTA BBR 40 6321S LGBU
00001 2/26/2006 PAY ARJ 40 0000A ALL
00001 7/26/2006 PAY ARJ 40 0000A FIN

With EffDate being the date the BU change happenend on the First table (7/1/2006)

Action being DTA
Reason being BUC
BU being the new BU identified

All other fields for the inserted row should mimic the row directly prior. If the employee wasn't in that DeptID on the date that the BU changed, we don't need to insert the record.

Any help would be greatly appreciated! I just can't get my head around this one. Thanks in advance!

 

[Golom]

This should give you a single record showing the "Before" (B Alias) and After (A Alias) values where a change occurred.

Select B.DeptID, B.EffDt, B.BU, A.EffDt, A.BU

From STGDEPTTREE B INNER JOIN STGDEPTTREE A
     ON B.DeptID = A.DeptID And B.BU <> A.BU 

Where A.EffDt = (Select MIN(EffDt) From STGDEPTTREE 
                 Where EffDt > B.EffDt)

You could then use that as a source for doing the insert which obviously involves values and/or fields that don't appear in the table that you presented.

 

[dm5085]

Golom,
Thanks so much! The second part was pretty simple once I had the first part.
I appreciate you taking the time to answer!