asked on

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

Vitor Montalvão

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

ASKER

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

Vitor Montalvão

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.

sam2929

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial