[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