Alex_Kuznetsov

CREATE TABLE dbo.TaskStatuses(TaskID INT NOT NULL,
  Status VARCHAR(20),
  StartedAt DATETIME NOT NULL,
  FinishedAt DATETIME NOT NULL,
  PreviousFinishedAt DATETIME NULL,
  CONSTRAINT PK_TaskStatuses_TaskID_FinishedAt PRIMARY KEY(TaskID, FinishedAt),
  CONSTRAINT UNQ_TaskStatuses_TaskID_PreviousFinishedAt UNIQUE(TaskID, PreviousFinishedAt),
  CONSTRAINT FK_TaskStatuses_TaskID_PreviousFinishedAt
    FOREIGN KEY(TaskID, PreviousFinishedAt)
    REFERENCES dbo.TaskStatuses(TaskID, FinishedAt),
  CONSTRAINT CHK_TaskStatuses_PreviousFinishedAt_Before_StartedAt CHECK(PreviousFinishedAt <= StartedAt)
)
go

INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
  VALUES(1, 'Pending', '20070101', '20070103', NULL)

-- you cannot have more than one beginning of history chain per task:
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
  VALUES(1, 'Pending', '20070104', '20070105', NULL)
/*
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UNQ_TaskStatuses_TaskID_PreviousFinishedAt'. Cannot insert duplicate key in object 'TaskStatuses'.
The statement has been terminated.
*/
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
  VALUES(1, 'Pending', '20070104', '20070105', '20070103')

-- history windows cannot overlap:
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
  VALUES(1, 'Opened', '20070104', '20070109', '20070105')
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'CHK_TaskStatuses_PreviousFinishedAt_Before_StartedAt'. The conflict occurred in database 'RiskCenter', table 'TaskStatuses'.
The statement has been terminated.
*/
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
  VALUES(1, 'Opened', '20070114', '20070119', '20070105')

-- you cannot fill a gap in one insert
-- Gap between Jan 5 and Jan 14

INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
  VALUES(1, 'Reviewed', '20070105', '20070114', '20070105')
/*
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UNQ_TaskStatuses_TaskID_PreviousFinishedAt'. Cannot insert duplicate key in object 'TaskStatuses'.
The statement has been terminated.
*/

-- to fill a gap, add to the end of the chain:
INSERT INTO dbo.TaskStatuses(TaskID, Status, StartedAt, FinishedAt, PreviousFinishedAt)
  VALUES(1, 'Reviewed', '20070125', '20070129', '20070119')

/*
TaskID      Status               StartedAt FinishedAt PreviousFinishedAt
----------- -------------------- --------- ---------- ------------------
1           Pending              20070101  20070103   NULL
1           Pending              20070104  20070105   20070103
1           Opened               20070114  20070119   20070105
1           Reviewed             20070125  20070129   20070119

(4 row(s) affected)
*/


-- then move the last period to fill the gap
UPDATE dbo.TaskStatuses SET StartedAt = CASE WHEN FinishedAt = '20070129' THEN '20070105' ELSE StartedAt END,
  FinishedAt = CASE WHEN FinishedAt = '20070129' THEN '20070114' ELSE FinishedAt END,  
  PreviousFinishedAt = CASE WHEN FinishedAt = '20070129' THEN '20070105' ELSE '20070114' END
WHERE TaskID = 1 AND FinishedAt IN('20070129', '20070119')

SELECT TaskID, Status, CONVERT(CHAR(8), StartedAt, 112) StartedAt,
    CONVERT(CHAR(8), FinishedAt, 112) FinishedAt,
    CONVERT(CHAR(8), PreviousFinishedAt, 112) PreviousFinishedAt
    FROM dbo.TaskStatuses ORDER BY FinishedAt

/*
TaskID      Status               StartedAt FinishedAt PreviousFinishedAt
----------- -------------------- --------- ---------- ------------------
1           Pending              20070101  20070103   NULL
1           Pending              20070104  20070105   20070103
1           Reviewed             20070105  20070114   20070105
1           Opened               20070114  20070119   20070114

(4 row(s) affected)
*/