{"id":18671,"date":"2022-11-01T14:45:11","date_gmt":"2022-11-01T09:15:11","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/"},"modified":"2022-11-01T14:45:11","modified_gmt":"2022-11-01T09:15:11","slug":"solved-grouping-ranges-of-data","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/","title":{"rendered":"[Solved] Grouping ranges of data"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-72266739\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"72266739\" data-parentid=\"72221836\" data-score=\"0\" data-position-on-page=\"3\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>Since we&#8217;re sorting by StartDate and EndDate, we should be able to use the minimum of this and all future start dates and the maximum of this and all past end dates.  This is just one more step beyond <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/bertwagner.com\/posts\/gaps-and-islands\/\">what Bert Wagner published<\/a>.<\/p>\n<pre><code>DROP TABLE IF EXISTS #OverlappingDateRanges;\nCREATE TABLE #OverlappingDateRanges (StartDate date, EndDate date);\n\nINSERT INTO #OverlappingDateRanges\nSELECT '8\/24\/2017', '9\/23\/2017'  UNION ALL\nSELECT '8\/24\/2017', '9\/20\/2017'  UNION ALL \nSELECT '9\/23\/2017', '9\/27\/2017'  UNION ALL \nSELECT '9\/25\/2017', '10\/10\/2017' UNION ALL\nSELECT '10\/17\/2017', '10\/18\/2017' UNION ALL \nSELECT '10\/25\/2017', '11\/3\/2017'  UNION ALL\nSELECT '11\/2\/2017', '11\/4\/2017' UNION ALL\nSELECT '10\/27\/2017', '11\/15\/2017' UNION ALL\nSELECT '11\/5\/2017', '11\/10\/2017'\n\n\n;\nWITH\nRanges as (\n    SELECT MIN(StartDate) OVER (ORDER BY StartDate, EndDate ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING) as StartDate\n    , MAX(EndDate) OVER (ORDER BY StartDate, EndDate ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) as EndDate\n    FROM #OverlappingDateRanges\n),\nGroups as (\n    SELECT StartDate\n    , EndDate\n    , LAG(StartDate,1) OVER (ORDER BY StartDate, EndDate) AS PreviousStartDate\n    , LAG(EndDate,1) OVER (ORDER BY StartDate, EndDate) AS PreviousEndDate\n    FROM Ranges\n),\nIslands as (\n    SELECT StartDate\n    , EndDate\n    , CASE WHEN PreviousEndDate &gt;= StartDate THEN 0 ELSE 1 END AS IslandStartInd\n    , SUM(CASE WHEN PreviousEndDate &gt;= StartDate THEN 0 ELSE 1 END) OVER (ORDER BY StartDate, EndDate) AS IslandId\n    FROM Groups\n)\n\nSELECT MIN(StartDate) AS IslandStartDate\n, MAX(EndDate) AS IslandEndDate\nFROM Islands\nGROUP BY IslandId\nORDER BY IslandStartDate;\n<\/code><\/pre>\n<pre><code>IslandStartDate IslandEndDate\n--------------- -------------\n2017-08-24      2017-10-10\n2017-10-17      2017-10-18\n2017-10-25      2017-11-15\n<\/code><\/pre>\n<p>I&#8217;m not finding any start\/end combinations that are causing problems.  I&#8217;ll let this marinate for a few days before marking it as correct in case somebody can poke some holes in it.<\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\"><\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Grouping ranges of data <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Since we&#8217;re sorting by StartDate and EndDate, we should be able to use the minimum of this and all future start dates and the maximum of this and all past end dates. This is just one more step beyond what Bert Wagner published. DROP TABLE IF EXISTS #OverlappingDateRanges; CREATE TABLE #OverlappingDateRanges (StartDate date, EndDate &#8230; <a title=\"[Solved] Grouping ranges of data\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/\" aria-label=\"More on [Solved] Grouping ranges of data\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[341],"class_list":["post-18671","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>[Solved] Grouping ranges of data - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Grouping ranges of data - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Since we&#8217;re sorting by StartDate and EndDate, we should be able to use the minimum of this and all future start dates and the maximum of this and all past end dates. This is just one more step beyond what Bert Wagner published. DROP TABLE IF EXISTS #OverlappingDateRanges; CREATE TABLE #OverlappingDateRanges (StartDate date, EndDate ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-01T09:15:11+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-grouping-ranges-of-data\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-grouping-ranges-of-data\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Grouping ranges of data\",\"datePublished\":\"2022-11-01T09:15:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-grouping-ranges-of-data\\\/\"},\"wordCount\":91,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"sql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-grouping-ranges-of-data\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-grouping-ranges-of-data\\\/\",\"name\":\"[Solved] Grouping ranges of data - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-11-01T09:15:11+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-grouping-ranges-of-data\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-grouping-ranges-of-data\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-grouping-ranges-of-data\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Grouping ranges of data\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/wp-content\\\/uploads\\\/2021\\\/02\\\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Grouping ranges of data - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Grouping ranges of data - JassWeb","og_description":"[ad_1] Since we&#8217;re sorting by StartDate and EndDate, we should be able to use the minimum of this and all future start dates and the maximum of this and all past end dates. This is just one more step beyond what Bert Wagner published. DROP TABLE IF EXISTS #OverlappingDateRanges; CREATE TABLE #OverlappingDateRanges (StartDate date, EndDate ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/","og_site_name":"JassWeb","article_published_time":"2022-11-01T09:15:11+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Grouping ranges of data","datePublished":"2022-11-01T09:15:11+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/"},"wordCount":91,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/","url":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/","name":"[Solved] Grouping ranges of data - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-11-01T09:15:11+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-grouping-ranges-of-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Grouping ranges of data"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1777008400","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/18671","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=18671"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/18671\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=18671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=18671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=18671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}