{"id":389,"date":"2008-06-16T00:00:00","date_gmt":"2008-06-16T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/missing-date-ranges-the-sequel\/"},"modified":"2021-09-29T16:22:10","modified_gmt":"2021-09-29T16:22:10","slug":"missing-date-ranges-the-sequel","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/missing-date-ranges-the-sequel\/","title":{"rendered":"Missing Date Ranges- the Sequel"},"content":{"rendered":"
\n

Some time ago, I published the article “Find Missing Date Ranges in SQL”:<\/a> . In that article, I showed how to find missing dates, using different SQL techniques. <\/p>\n

A few months later, I received a message from a Simple-Talk reader, who thought he had a similar problem, and asked me for help. Here is the reader’s problem: <\/p>\n

‘There is a table, where two of the columns are DateFrom<\/b> and DateTo<\/b>. Both columns contain date and time values. How does one find the missing date ranges or, in other words, all the date ranges that are not covered by any of the entries in the table’. <\/em><\/p>\n

At first glance, the reader’s problem and the one on my article look similar; they both involve finding missing, or unused, date ranges. However, they are not the same. In the article, a hypothetical application registers the moments (time) of events and stores them in a database (table). In the reader’s problem, some imaginary application records the beginning and the end of the processes, representing them as continuous date\/time ranges. <\/p>\n

The second task may become more complicated, if the processes can overlap each other in time. <\/p>\n

The following diagram illustrates the reader’s problem (see Fig.1):<\/p>\n

\"518-kozakPuzzleExample.gif\"
Fig.1 <\/strong>The reader’s problem <\/p>\n

As shown on the diagram, there is only one missing date range: from Jan 24 to Jan 31. <\/p>\n

Without the process that started on Jan 01 and completed on Jan 22, shown in green in the diagram, there would be three missing date ranges: <\/p>\n

    \n
  1. Jan 04 – Jan 04; <\/li>\n
  2. Jan 11 – Jan 19; <\/li>\n
  3. Jan 24 – Jan 31; <\/li>\n<\/ol>\n

    Notice, that a special case, when the process started, but did not finish yet, can be treated as a process with the end time equal to the current time. <\/p>\n

    For example, if the process started on June 10 and still running on June 14 (when you execute the query), you can consider that process as a process that started on June 10 and finished on June 14. In other words, dates in the range from June 10 to June 14 are covered (exist). <\/p>\n

    Solution with Auxiliary Table<\/h2>\n

    Before we explore the fist solution, let us create and load sample table (see Listing1): <\/p>\n

    IF EXISTS(SELECT * FROM sysobjects  \n   WHERE ID = (OBJECT_ID('dateRanges')) AND xtype = 'U')  \nDROP TABLE dateRanges;\nGO\nCREATE TABLE dateRanges(dateFrom DATETIME, dateTo DATETIME)\nGO\n\nINSERT INTO dateRanges VALUES('Jan 01, 2000','Jan 22, 2000')\nINSERT INTO dateRanges VALUES('Jan 01, 2000','Jan 03, 2000')\nINSERT INTO dateRanges VALUES('Jan 05, 2000','Jan 10, 2000')\nINSERT INTO dateRanges VALUES('Jan 20, 2000','Jan 23, 2000')\nINSERT INTO dateRanges VALUES('Feb 01, 2000','Feb 06, 2000')\nINSERT INTO dateRanges VALUES('Feb 02, 2000','Feb 05, 2000')\nINSERT INTO dateRanges VALUES('Mar 01, 2000','Mar 02, 2000')\nINSERT INTO dateRanges VALUES('Mar 20, 2000','Mar 23, 2000')\nINSERT INTO dateRanges VALUES('Mar 25, 2000','Mar 28, 2000')\nINSERT INTO dateRanges VALUES('Mar 29, 2000','Apr 03, 2000')\nINSERT INTO dateRanges VALUES('Apr 01, 2000','Apr 05, 2000')\nINSERT INTO dateRanges VALUES('Apr 20, 2000','Apr 23, 2000')\nINSERT INTO dateRanges VALUES('Apr 25, 2000','Apr 28, 2000')\nINSERT INTO dateRanges VALUES('May 19, 2000','May 23, 2000')\nINSERT INTO dateRanges VALUES('May 20, 2000','May 23, 2000')\nINSERT INTO dateRanges VALUES('May 24, 2000','May 25, 2000')\nINSERT INTO dateRanges VALUES('May 28, 2000','May 29, 2000')\nINSERT INTO dateRanges VALUES('May 28, 2000','Jun 10, 2000')\nINSERT INTO dateRanges VALUES('May 29, 2000','Jun 10, 2000')\nINSERT INTO dateRanges VALUES('Jun 05, 2000','Jun 15, 2000')\nINSERT INTO dateRanges VALUES('Jun 20, 2000','Jun 28, 2000')\nINSERT INTO dateRanges VALUES('Jul 02, 2000','Jul 28, 2000')\n<\/pre>\n
    Listing1<\/strong>. Create and load sample table <\/p>\n
    \"518-kozakPuzzle.gif\"
    Fig.2<\/strong> The Test data<\/p>\n
    Then, using an auxiliary table you can easily find missing dates (see Listing2): <\/p>\n
    SET NOCOUNT ON; \n\nDECLARE @minDateFrom DATETIME, @maxDateTo DATETIME;\nSELECT @minDateFrom = MIN(dateFrom), \n    @maxDateTo = MAX(dateTo) \n   FROM dateRanges;\n\n-- create auxiliary table dateSequence\nIF EXISTS(SELECT * FROM sysobjects  \n   WHERE ID = (OBJECT_ID('dateSequence')) AND xtype = 'U')  \nDROP TABLE dateSequence; \nCREATE TABLE dateSequence(dt DATETIME NOT NULL PRIMARY KEY); \n\n-- load table dateSequence\nWITH dates AS \n( \nSELECT @minDateFrom AS initDate \nUNION ALL \nSELECT DATEADD(dd, 1, initDate) FROM dates WHERE initDate <@maxDateTo\n) \nINSERT INTO dateSequence \nSELECT initDate FROM dates  \nOPTION (MAXRECURSION 0); \n\n-- get missing dates\nSELECT CONVERT(VARCHAR(20),dt, 107) AS missingDates \n   FROM dateSequence t1\n   WHERE NOT EXISTS \n     (SELECT * FROM dateRanges t2 \n       WHERE dt BETWEEN t2.dateFrom AND t2.dateTo)\n\nResults:\n\nmissingDates\n--------------------\nJan 24, 2000\nJan 25, 2000\nJan 26, 2000\nJan 27, 2000\nJan 28, 2000\nJan 29, 2000\nJan 30, 2000\nJan 31, 2000\nFeb 07, 2000\nFeb 08, 2000\n. . . . . . .\n\nFeb 28, 2000\nFeb 29, 2000\nMar 03, 2000\nMar 04, 2000\n. . . . . . .\n\nMar 18, 2000\nMar 19, 2000\nMar 24, 2000\nApr 06, 2000\nApr 07, 2000\n. . . . . . .\n\nApr 18, 2000\nApr 19, 2000\nApr 24, 2000\nApr 29, 2000\nApr 30, 2000\nMay 01, 2000\nMay 02, 2000\n. . . . . . .\n\nMay 17, 2000\nMay 18, 2000\nMay 26, 2000\nMay 27, 2000\nJun 16, 2000\nJun 17, 2000\nJun 18, 2000\nJun 19, 2000\nJun 29, 2000\nJun 30, 2000\nJul 01, 2000\n\n<\/pre>\n
    Listing2.<\/strong> Find missing dates, using an auxiliary table <\/p>\n
    The same approach can be used, if you need to find missing hours, minutes or seconds. For example, for missing minutes, you need to make a small change in DATEADD()<\/b> function in common table expression (see Listing3): <\/p>\n
    SET NOCOUNT ON; \n\nDECLARE @minDateFrom DATETIME, @maxDateTo DATETIME;\nSELECT @minDateFrom = MIN(dateFrom), \n       @maxDateTo = MAX(dateTo) FROM dateRanges;\n\n-- create table dateSequence\nIF EXISTS(SELECT * FROM sysobjects  \n   WHERE ID = (OBJECT_ID('dateSequence')) AND xtype = 'U')  \nDROP TABLE dateSequence; \nCREATE TABLE dateSequence(dt DATETIME NOT NULL PRIMARY KEY); \n\n-- load table dateSequence\nWITH dates AS \n( \nSELECT @minDateFrom AS initDate \nUNION ALL \nSELECT DATEADD(mi, 1, initDate) FROM dates \n       WHERE initDate <@maxDateTo\n) \nINSERT INTO dateSequence \nSELECT initDate FROM dates  \nOPTION (MAXRECURSION 0); \n\n-- get missing dates\nSELECT dt AS missingDates \n   FROM dateSequence t1\n   WHERE NOT EXISTS \n     (SELECT * FROM dateRanges t2 \n       WHERE dt BETWEEN t2.dateFrom AND t2.dateTo)<\/pre>\n
    Listing3<\/strong>. Solution for missing minutes with auxiliary table <\/p>\n
    However, the solutions, as shown in  Listing2 and Listing3, are not the answers for the reader’s problem. This will  require finding the missing, or unused, date ranges, but not missing, or unused,  dates. <\/p>\n
    One possible way to solve that problem is to transform already found missing dates into missing date ranges. <\/p>\n
    In order to test that solution, you will need to: <\/p>\n
      \n
    1. Create and load an auxiliary table dateSequence<\/b> as shown in the Listing2  <\/li>\n
    2. Run following query (see Listing 4) <\/li>\n<\/ol>\n
      WITH datesCTE(missingDates) AS  \n(SELECT missingDates FROM \n   (SELECT dt AS missingDates FROM dateSequence t1 \n     WHERE NOT EXISTS (SELECT * FROM dateRanges t2 \n   WHERE dt BETWEEN t2.dateFrom AND t2.dateTo)) tbl1\n) \nSELECT CONVERT(VARCHAR(20), t1.missingDates, 107) missingFrom, \n       CONVERT(VARCHAR(20), MIN(t2.missingDates), 107) missingTo \nFROM (SELECT missingDates FROM datesCTE tbl1 \n   WHERE NOT EXISTS(SELECT * FROM datesCTE tbl2 \nWHERE DATEDIFF(dd, tbl2.missingDates, tbl1.missingDates) = 1)) t1\nINNER JOIN \n(SELECT missingDates FROM datesCTE tbl1 \n    WHERE NOT EXISTS(SELECT * FROM datesCTE tbl2 \nWHERE DATEDIFF(dd, tbl1.missingDates, tbl2.missingDates) = 1)) t2\nON DATEDIFF(dd, t1.missingDates, t2.missingDates) >= 0\nGROUP BY t1.missingDates\nORDER BY t1.missingDates \n\nResults: \n\nmissingFrom missingTo \n------------ ------------ \nJan 24, 2000 Jan 31, 2000 \nFeb 07, 2000 Feb 29, 2000 \nMar 03, 2000 Mar 19, 2000 \nMar 24, 2000 Mar 24, 2000 \nApr 06, 2000 Apr 19, 2000 \nApr 24, 2000 Apr 24, 2000 \nApr 29, 2000 May 18, 2000 \nMay 26, 2000 May 27, 2000 \nJun 16, 2000 Jun 19, 2000 \nJun 29, 2000 Jul 01, 2000 \n<\/pre>\n
      Listing4<\/strong>. Find missing date ranges, using an auxiliary table <\/p>\n
      There is, however, at least one way to solve the reader’s problem in one query and without an auxiliary table. <\/p>\n
      The ‘Missing Date Ranges’ Puzzle <\/h2>\n
      Try to find the solution for the reader’s problem. Consider it as a puzzle with the following requirements: <\/p>\n
        \n
      • You have a sample table dateRanges<\/b> with two date\/time columns dateFrom<\/b> and dateTo <\/b>(see Listing1)  <\/li>\n
      • You need to find the missing date ranges (that were not used in any rows), where the format of result and the result should be the same as in the Listing4.  <\/li>\n
      • The solution should be one-query solution without an auxiliary table.  <\/li>\n
      • It should work in SQL Server 2005 and in SQL Server 2000. <\/li>\n<\/ul>\n
        The best solution will be awarded with a $50 Amazon voucher. <\/p>\n
        I will post my solution as comments in a couple of weeks. <\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
        Alex Kozak returns with another Date puzzle. A readers question gives Alex the inspiration to see if is possible to list unused date ranges in one Select statement. …<\/p>\n","protected":false},"author":221828,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4252,4872],"coauthors":[],"class_list":["post-389","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-t-sql-programming","tag-tsql-sql-example-puzzle-transact-sql-date-values-data-type"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/389","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\/221828"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=389"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/389\/revisions"}],"predecessor-version":[{"id":92561,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/389\/revisions\/92561"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=389"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=389"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=389"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=389"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}