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
sam2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
sam2929Author Commented:
i just want to do  update st-mt not trigger can you please help with that
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
sam2929Author Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just add the necessary filter:
CREATE TRIGGER trg_ExpireDate
ON dbo.d_employee
AFTER INSERT 
AS  
    UPDATE e
    SET e.dateexpired = i.datecreated
    FROM d_employee e
          INNER JOIN inserted i ON (e.emplid = i.emplid AND e.week = i.week)
    WHERE e.datecreated < i.datecreated AND e.dateexpired IS NULL
GO  

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.