{"id":1041,"date":"2010-11-25T00:00:00","date_gmt":"2010-11-25T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/modifying-contiguous-time-periods-in-a-history-table\/"},"modified":"2021-09-29T16:21:55","modified_gmt":"2021-09-29T16:21:55","slug":"modifying-contiguous-time-periods-in-a-history-table","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/modifying-contiguous-time-periods-in-a-history-table\/","title":{"rendered":"Modifying Contiguous Time Periods in a History Table"},"content":{"rendered":"
\n

Modifying Contiguous Time Periods <\/h1>\n

This article explains how to modify contiguous time periods that were described in Joe Celko’s article ‘Contiguous Time Periods in SQL<\/a>‘.  Joe describes the table itself that he calls the ‘Kuznetsov History Table’. He explains how it is used to store contiguous time intervals with constraint to ensure that the date periods really are contiguous, The editor suggested that I give a brief description of how to modify the data in the History table as this may not be entirely obvious.<\/p>\n

When trusted constraints enforce data integrity, the data is guaranteed to be valid at the end of any statement, even if it is not committed. When we modify contiguous time periods, in order to get from one valid state to another we may need to insert a row and update another one, or we may need to delete a row and update another one. This is one of those cases when MERGE really shines – it allows us to get from one valid state to another in one statement, inserting, updating, and deleting rows as needed.<\/p>\n

Prerequisites.<\/h2>\n

All we need is an empty table, as follows:<\/p>\n

\nCREATE TABLE dbo.Responsibilities\n    (\n      TaskId INT NOT NULL ,\n      PersonInCharge VARCHAR(50) NOT NULL ,\n      StartedAt DATETIME NOT NULL ,\n      FinishedAt DATETIME NOT NULL ,\n      PreviousFinishedAt DATETIME NULL ,\n      CONSTRAINT PK_Responsibilities_TaskId_FinishedAt \n           PRIMARY KEY ( TaskId, FinishedAt ) ,\n      CONSTRAINT UNQ_Responsibilities_TaskId_PreviousFinishedAt \n           UNIQUE ( TaskId, PreviousFinishedAt ) ,\n      CONSTRAINT FK_Responsibilities_TaskId_PreviousFinishedAt \n           FOREIGN KEY ( TaskId, PreviousFinishedAt ) \n           REFERENCES dbo.Responsibilities ( TaskId, FinishedAt ) ,\n      CONSTRAINT CHK_Responsibilities_PreviousFinishedAt_NotAfter_StartedAt \n           CHECK ( PreviousFinishedAt <= StartedAt ) ,\n      CONSTRAINT CHK_Responsibilities_StartedAt_Before_FinishedAt \n           CHECK ( StartedAt < FinishedAt )\n    ) ;  \n\n<\/pre>\n
Some Easy Modifications.<\/h2>\n
It is easy to begin a new series of time periods<\/p>\n
INSERT  INTO dbo.Responsibilities\n        ( TaskId ,\n          PersonInCharge ,\n          StartedAt ,\n          FinishedAt ,\n          PreviousFinishedAt\n        )\nVALUES  ( 1 ,\n          'Joe' ,\n          '20101002' ,\n          '20101023' ,\n          NULL\n        ),\n        ( 1 ,\n          'Andrew' ,\n          '20101023' ,\n          '20101103' ,\n          '20101023'\n        ) ;  \n<\/pre>\n
It is just as easy to continue adding periods to the end of the series.<\/p>\n
INSERT  INTO dbo.Responsibilities\n        ( TaskId ,\n          PersonInCharge ,\n          StartedAt ,\n          FinishedAt ,\n          PreviousFinishedAt\n        )\nSELECT    1 AS TaskId ,\n          'Alex' AS PersonInCharge ,\n          '20101120' AS StartedAt ,\n          '20101125' AS FinishedAt ,\n          '20101103' AS PreviousFinishedAt\nUNION ALL           \nSELECT    1 AS TaskId ,\n          'Andrew' AS PersonInCharge ,\n          '20101126' AS StartedAt ,\n          '20101127' AS FinishedAt ,\n          '20101125' AS PreviousFinishedAt  \n<\/pre>\n
Deleting one or more rows from the end is just as easy, and we shall skip the example. As we have seen, it is easy to perform typical, the most common operations against history of periods. <\/p>\n
However, some other operations are less easy and need more explanations. Now that we have enough test data, let us move on to more complex examples. Here is the test data at this moment:<\/p>\n
\"1191-clip_image002.jpg\"<\/p>\n
Adding periods to the beginning.<\/h2>\n
Each series of periods has exactly one first period – this is enforced by the following constraint: UNQ_Responsibilities_TaskId_PreviousFinishedAt<\/strong><\/span>.<\/p>\n
As a result, when we are inserting one or more periods to the beginning of the series, we have to update the period that used to be the first before, as follows:<\/p>\n
\nMERGE dbo.Responsibilities AS Target\n    USING \n        ( SELECT    1 AS TaskId ,\n                    'Alex' AS PersonInCharge ,\n                    '20090301' AS StartedAt ,\n                    '20090306' AS FinishedAt ,\n                    NULL AS PreviousFinishedAt\n          UNION ALL\n          SELECT    1 AS TaskId ,\n                    'Joe' AS PersonInCharge ,\n                    '20101002' AS StartedAt ,\n                    '20101023' AS FinishedAt ,\n                    '20090306' AS PreviousFinishedAt\n        ) AS source ( TaskId, PersonInCharge, StartedAt, FinishedAt,\n                      PreviousFinishedAt )\n    ON ( Target.TaskId = source.TaskId\n         AND Target.StartedAt = source.StartedAt\n       )\n    WHEN MATCHED \n        THEN \n        UPDATE\n          SET   PersonInCharge = source.PersonInCharge ,\n                StartedAt = source.StartedAt ,\n                FinishedAt = source.FinishedAt ,\n                PreviousFinishedAt = source.PreviousFinishedAt\n    WHEN NOT MATCHED \n        THEN   \nINSERT  (\n          TaskId ,\n          PersonInCharge ,\n          StartedAt ,\n          FinishedAt ,\n          PreviousFinishedAt\n        ) VALUES\n        ( source.TaskId ,\n          source.PersonInCharge ,\n          source.StartedAt ,\n          source.FinishedAt ,\n          source.PreviousFinishedAt\n        );  \n<\/pre>\n
\n
Now we will verify that our test data looks as expected, with a new row at the beginning, and PreviousFinishedAt <\/strong>column is modified to point to the new row for the row that used to be the first before this modification:<\/p>\n
\"1191-clip_image004.jpg\"<\/p>\n
We are also going to discuss some other scenarios, such as adding\/deleting periods in the middle of the series. In all these cases we shall be using MERGE, and the DML looks quite similar, so let us wrap it up in a stored procedure.<\/p>\n
Creating a stored procedure<\/h2>\n
The following code implements this merging functionality with a stored procedure that uses a table valued parameter, as follows:<\/p>\n
\nCREATE TYPE ResponsibilitiesChangesList AS TABLE ( \nTaskId INT NOT NULL ,\nPersonInCharge VARCHAR(50) NOT NULL ,\nStartedAt DATETIME NOT NULL ,\nFinishedAt DATETIME NOT NULL ,\nPreviousFinishedAt DATETIME NULL,\nDeleteThisRow CHAR(1)\n) ;\nGO\n\nCREATE PROCEDURE dbo.MergeResponsibilities\n    @changes ResponsibilitiesChangesList READONLY\n\nAS \n    BEGIN ;\n        SET NOCOUNT ON ;\n        MERGE dbo.Responsibilities AS Target\n            USING \n                ( SELECT    TaskId ,\n                            PersonInCharge ,\n                            StartedAt ,\n                            FinishedAt ,\n                            PreviousFinishedAt ,\n                            DeleteThisRow\n                  FROM      @changes AS c\n                ) AS source ( TaskId, PersonInCharge, StartedAt, FinishedAt,\n                              PreviousFinishedAt, DeleteThisRow )\n            ON ( Target.TaskId = source.TaskId\n                 AND Target.StartedAt = source.StartedAt\n               )\n            WHEN MATCHED AND DeleteThisRow = 'Y'\n                THEN \n        DELETE\n            WHEN MATCHED \n                THEN \n        UPDATE      SET\n                PersonInCharge = source.PersonInCharge ,\n                StartedAt = source.StartedAt ,\n                FinishedAt = source.FinishedAt ,\n                PreviousFinishedAt = source.PreviousFinishedAt\n            WHEN NOT MATCHED \n                THEN   \nINSERT  (\n          TaskId ,\n          PersonInCharge ,\n          StartedAt ,\n          FinishedAt ,\n          PreviousFinishedAt\n        )           VALUES\n        ( source.TaskId ,\n          source.PersonInCharge ,\n          source.StartedAt ,\n          source.FinishedAt ,\n          source.PreviousFinishedAt\n        ) ;\n    END ;  \n<\/pre>\n
Let us use this stored procedure.<\/p>\n
Filling a gap in the middle of the series<\/h2>\n
The following code fills the gap on November 25th.<\/p>\n
\nDECLARE @changes ResponsibilitiesChangesList ;\nINSERT  INTO @changes\n        ( TaskId ,\n          PersonInCharge ,\n          StartedAt ,\n          FinishedAt ,\n          PreviousFinishedAt ,\n          DeleteThisRow\n        )\n        SELECT  1 AS TaskId ,\n                'Michelle' AS PersonInCharge ,\n                '20101125' AS StartedAt ,\n                '20101126' AS FinishedAt ,\n                '20101125' AS PreviousFinishedAt ,\n                'N' AS DeleteThisRow\n        UNION ALL\n        SELECT  1 AS TaskId ,\n                'Andrew' AS PersonInCharge ,\n                '20101126' AS StartedAt ,\n                '20101127' AS FinishedAt ,\n                '20101126' AS PreviousFinishedAt ,\n                'N' AS DeleteThisRow ;\n                    \nEXEC dbo.MergeResponsibilities @changes = @changes ;  \n<\/pre>\n
Here is the data after this modification, with a period added in the middle fo the series:<\/p>\n
\"1191-clip_image006.jpg\"<\/p>\n
Deleting a period in the middle of the series<\/h2>\n
The following code deletes the period added in the previous example.<\/p>\n
\nDECLARE @changes ResponsibilitiesChangesList ;\nINSERT  INTO @changes\n        ( TaskId ,\n          PersonInCharge ,\n          StartedAt ,\n          FinishedAt ,\n          PreviousFinishedAt ,\n          DeleteThisRow\n        )\n          SELECT    1 AS TaskId ,\n                    'Michelle' AS PersonInCharge ,\n                    '20101125' AS StartedAt ,\n                    '20101126' AS FinishedAt ,\n                    '20101125' AS PreviousFinishedAt,\n                    'Y' AS DeleteThisRow\n          UNION ALL\n          SELECT    1 AS TaskId ,\n                    'Andrew' AS PersonInCharge ,\n                    '20101126' AS StartedAt ,\n                    '20101127' AS FinishedAt ,\n                    '20101125' AS PreviousFinishedAt,\n                    'N' AS DeleteThisRow ;\n                    \nEXEC dbo.MergeResponsibilities @changes = @changes ;  \n\n<\/pre>\n
Here is the data after this modification:<\/p>\n
\"1191-clip_image008.jpg\"<\/p>\n
Inserting two periods in the middle, and adjusting an exaisting period to make room for them.<\/h2>\n
This is the last and most complex example involving our stored procedure:<\/p>\n
\nDECLARE @changes ResponsibilitiesChangesList ;\nINSERT  INTO @changes\n        ( TaskId ,\n          PersonInCharge ,\n          StartedAt ,\n          FinishedAt ,\n          PreviousFinishedAt ,\n          DeleteThisRow\n        )\nSELECT    1 AS TaskId ,\n                    'Alex' AS PersonInCharge ,\n                    '20101120' AS StartedAt ,\n                    '20101122' AS FinishedAt ,\n                    '20101103' AS PreviousFinishedAt,\n                    'N' AS DeleteThisRow\n          UNION ALL\n          SELECT    1 AS TaskId ,\n                    'Michelle' AS PersonInCharge ,\n                    '20101122' AS StartedAt ,\n                    '20101123' AS FinishedAt ,\n                    '20101122' AS PreviousFinishedAt,\n                    'N' AS DeleteThisRow\n          UNION ALL\n          SELECT    1 AS TaskId ,\n                    'Alex' AS PersonInCharge ,\n                    '20101123' AS StartedAt ,\n                    '20101125' AS FinishedAt ,\n                    '20101123' AS PreviousFinishedAt,\n                    'N' AS DeleteThisRow\n                    \nEXEC dbo.MergeResponsibilities @changes = @changes ;  \n<\/pre>\n
Here is the data after running this script, with modifications in red rectangles:<\/p>\n
\"1191-clip_image010.jpg\"<\/p>\n
Getting by on SQL Server 2005, without MERGE<\/h2>\n
We do not have MERGE on SQL Server 2005, so we have to use more complex ways to modify, such as delete and reinsert the whole series, or use more than one command to implement the change. The following operations are available:<\/p>\n
    \n
  • Inserting periods at the end  <\/li>\n
  • Deleting periods at the end  <\/li>\n
  • Updating periods from one valid state to another <\/li>\n<\/ul>\n
    For example, to delete the first period, we have to use an update to move it to the end, and then delete it, as follows:<\/p>\n
    \nSET XACT_ABORT ON ;\nBEGIN TRAN ;\n\nUPDATE  dbo.Responsibilities\nSET     FinishedAt = CASE WHEN StartedAt = '20090301' THEN '20990909'\n                          ELSE FinishedAt\n                     END ,\n        StartedAt = CASE WHEN StartedAt = '20090301' THEN '20990908'\n                         ELSE StartedAt\n                    END ,\n        PreviousFinishedAt = CASE WHEN StartedAt = '20090301' THEN '20101127'\n                                  ELSE NULL\n                             END\nWHERE   TaskId = 1\n        AND StartedAt IN ( '20090301', '20101002' ) ;\n        \nSELECT  *\nFROM    dbo.Responsibilities\n        \n        \nDELETE  FROM dbo.Responsibilities\nWHERE   TaskId = 1\n        AND StartedAt = '20990908' ;\n        \nSELECT  *\nFROM    dbo.Responsibilities ;\nROLLBACK ;  \n\n<\/pre>\n
    Selects were added to the script so that we can see the intermediate and final state of the data. In the intermediate state, the first row is moved to the end, and the second one is updated to become the first:<\/p>\n
    \"1191-clip_image012.jpg\"<\/p>\n
    In the final state, the row is gone:<\/p>\n
    \"1191-clip_image014.jpg\"<\/p>\n
    We shall not re-implement all the previously discussed examples – that is left as an advanced exercise. <\/p>\n
    Good luck!<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    Alex Kuznetsov is credited with a clever technique for creating a history table for SQL that is designed to store contiguous time periods and check that these time periods really are contiguous, using nothing but constraints. This is now increasingly useful with the DATE data type in SQL Server.  The modification of data in this type of table isn’t always entirely intuitive so Alex is on hand to give a brief explanation of how to do it.…<\/p>\n","protected":false},"author":6776,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4151,4252],"coauthors":[],"class_list":["post-1041","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1041","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/6776"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1041"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1041\/revisions"}],"predecessor-version":[{"id":92542,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1041\/revisions\/92542"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1041"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1041"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1041"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1041"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}