question

caoneill avatar image
caoneill asked

Derive an additional table from an existing table

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

sql server 2014
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
caoneill avatar image
caoneill answered

Never mind! I figured it out. I 'm using

LAG(CHANGE_VALUE,1,0)OVER (PARTITIONBY CHANGE_TYPE ORDER BYSTART_DATE ASC)AS PREVIOUS_CHANGE_VALUE,

thanks!

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.