update old record

Please look at sample.
All i need is expiry old record when new one is inserted.

d_employee

emplid week datecreated dateexpired
001 212 2018-03-19
001 212 2018-03-19 2018-03-19

001 212 2018-03-22

001 212 2018-03-22 2018-03-19

result should be

d_employee

emplid week datecreated dateexpired
001 212 2018-03-19 2018-03-22 ---expire this record
001 212 2018-03-19 2018-03-19

001 212 2018-03-22

001 212 2018-03-22 2018-03-19

Microsoft SQL Server

Last Comment

Vitor Montalvão

8/22/2022 - Mon

Vitor Montalvão

3/26/2018

Create an ON INSERT trigger where you'll update the record with an empty Expired Date. Example:

CREATE TRIGGER trg_ExpireDate
ON dbo.d_employee
AFTER INSERT 
AS  
    UPDATE e
    SET e.dateexpired = GETDATE()
    FROM d_employee e
          INNER JOIN inserted i ON (e.emplid = i.emplid AND e.week = i.week AND e.datecreated = i.datecreated)
GO

Open in new window

sam2929

3/26/2018

ASKER

i just want to do update st-mt not trigger can you please help with that

Vitor Montalvão

3/26/2018

Well, by your explanation is a trigger that you need.
If I misunderstood it, you already have the UPDATE statement in my trigger example. Just take and adapt it to your case.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

sam2929

3/26/2018

ASKER

this will not work if you look at source data dateexpiry is null for record 1 and 3 i just want to expire first record as new record now is 2018-03-22.

emplid week datecreated dateexpired
001 212 2018-03-19
001 212 2018-03-19 2018-03-19

001 212 2018-03-22

001 212 2018-03-22 2018-03-19

ASKER CERTIFIED SOLUTION

Vitor Montalvão

3/26/2018

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Plans and Pricing