Hi, I have a table of employee historical records on office location, department, etc. I need to create an additional table which lists the original change (the word "new" if they are fresh to the company) or original office to new office for example. Any help is appreciated!
CREATE TABLE [dbo].[emphistory](
[ACCOUNTNAME] [VARCHAR](20)NULL,
[NAME] [VARCHAR](40)NULL,
[INACTIVE] [VARCHAR](40)NULL,
[START_DATE] [DATETIME] NULL,
[END_DATE] [DATETIME] NULL,
[CHANGE_TYPE] [VARCHAR](100)NULL,
[CHANGE_VALUE] [VARCHAR](100)NULL
)ON [PRIMARY]
GO
insert into emphistory values('JJJ01','Jane','Y','3/23/2015','2/1/2019','Office','Boston')
insert into emphistory values('JJJ01','Jane','Y','3/23/2015','2/1/2019','Division','Administration')
insert into emphistory values('JJJ01','Jane','Y','3/23/2015','6/2/2016','Personnel Type','Other')
insert into emphistory values('JJJ01','Jane','Y','3/23/2015','2/1/2019','Department','Administrative Staff')
insert into emphistory values('JJJ01','Jane','Y','6/3/2016','2/1/2019','Personnel Type','Non Sales Professional')
insert into emphistory values('RRR01','Ryan','N','1/7/1991',NULL,'Division','Administration')
insert into emphistory values('RRR01','Ryan','N','1/7/1991','8/3/2004','Office','Boston')
insert into emphistory values('RRR01','Ryan','N','1/7/1991',NULL,'Department','Administrative Staff')
insert into emphistory values('RRR01','Ryan','N','1/7/1991','8/3/2004','Personnel Type','Administrative Assistant')
insert into emphistory values('RRR01','Ryan','N','8/4/2004',NULL,'Personnel Type','Non Sales Professional')
insert into emphistory values('RRR01','Ryan','N','8/4/2004',NULL,'Office','New York')
insert into emphistory values('SSS01','Steve','N','3/29/2004','7/31/2008','Office','Boston')
insert into emphistory values('SSS01','Steve','N','3/29/2004',NULL,'Division','Administration')
insert into emphistory values('SSS01','Steve','N','3/29/2004',NULL,'Personnel Type','Non Sales Professional')
insert into emphistory values('SSS01','Steve','N','3/29/2004',NULL,'Department','Administrative Staff')
insert into emphistory values('SSS01','Steve','N','8/1/2008',NULL,'Office','New York')
insert into emphistory values('CCC01','Cathy','Y','1/1/2000','8/31/2002','Personnel Type','Summer Intern')
insert into emphistory values('CCC01','Cathy','Y','1/1/2000','9/29/2006','Office','Washington, DC')
insert into emphistory values('CCC01','Cathy','Y','1/1/2000','8/31/2002','Division','Summer Interns')
insert into emphistory values('CCC01','Cathy','Y','1/1/2000','9/29/2006','Department','Sales')
insert into emphistory values('CCC01','Cathy','Y','9/1/2002','9/29/2006','Division','Marketing')
insert into emphistory values('CCC01','Cathy','Y','9/1/2002','9/29/2006','Personnel Type','Sales Professional')
New table should be:
ACCOUNTNAMEEMPLOYEE_NAMEINACTIVESTART_DATEEND_DATECHANGE_TYPEPREVIOUS CHANGE_VALUENEW_CHANGE_VALUEJJJ01JaneY3/23/20152/1/2019OfficeNewBostonJJJ01JaneY3/23/20152/1/2019DivisionNewAdministrationJJJ01JaneY3/23/20156/2/2016Personnel TypeNewOtherJJJ01JaneY3/23/20152/1/2019DepartmentNewAdministrative StaffJJJ01JaneY6/3/20162/1/2019Personnel TypeOtherNon Sales ProfessionalRRR01RyanN1/7/1991NULLDivisionNewAdministrationRRR01RyanN1/7/19918/3/2004OfficeNewBostonRRR01RyanN1/7/1991NULLDepartmentNewAdministrative StaffRRR01RyanN1/7/19918/3/2004Personnel TypeNewAdministrative AssistantRRR01RyanN8/4/2004NULLPersonnel TypeAdministrative AssistantNon Sales ProfessionalRRR01RyanN8/4/2004NULLOfficeBostonNew YorkSSS01SteveN3/29/20047/31/2008OfficeNewBostonSSS01SteveN3/29/2004NULLDivisionNewAdministrationSSS01SteveN3/29/2004NULLPersonnel TypeNewNon Sales ProfessionalSSS01SteveN3/29/2004NULLDepartmentNewAdministrative StaffSSS01SteveN8/1/2008NULLOfficeBostonNew YorkCCC01CathyY1/1/20008/31/2002Personnel TypeNewSummer InternCCC01CathyY1/1/20009/29/2006OfficeNewWashington, DCCCC01CathyY1/1/20008/31/2002DivisionNewSummer InternsCCC01CathyY1/1/20009/29/2006DepartmentNewSalesCCC01CathyY9/1/20029/29/2006DivisionSummer InternsMarketingCCC01CathyY9/1/20029/29/2006Personnel TypeSummer InternSales Professional
