All geek questions in one place

How to update the end date to the next recording start date?

I have a [RoleHistories] table with columns:

[RoleHistoryId] [PersonId] [Start] [End]

The Start column displays the date. The end column is NULL. There are a few lines per person. I need to update the end column to have a date that is the start date of the next record. If there is no subsequent record, the end must remain zero.

eg.

 RoleHistoryId = 999, PersonId =1, Start=2009-1-1, End=null RoleHistoryId = 2677, PersonId =1, Start=2011-5-1, End=null RoleHistoryId = 4637, PersonId =1, Start=2013-9-1, End=null

I would like

 RoleHistoryId = 999, PersonId =1, Start=2009-1-1, End=2011-5-1 RoleHistoryId = 2677, PersonId =1, Start=2011-5-1, End=2013-9-1 RoleHistoryId = 4637, PersonId =1, Start=2013-9-1, End=null
+4
source share
1 answer
 UPDATE rh1 SET End = (SELECT Min(Start) FROM RoleHistories rh2 WHERE rh1.Personid = rh2.Personid AND rh1.Start < rh2.Start) FROM RoleHistories rh1 WHERE End IS NULL
+4
source

Source: https://habr.com/ru/post/1485472/

More articles:


All Articles