title: SQL Data Cleanse - Overlapping Date Range
body: <p>I'm hoping someone can help me the best way to resolve this issue.</p>
  
  <p>Our organization currently uses sales cycles to judge the performance of our retailers based on their first shipment date. Here are the business rules:</p>
  
  <pre><code>Nurture Stage - 1st year
  Graduate Stage - 2nd year
  Ongoing Stage - 3rd year and on
  Inactive Stage - stop doing business
  Restart Stage - do business with us after an Inactive Stage
  Change Owner Stage - sell their business and new owner does business with us
  </code></pre>
  
  <p>To complicate this mess, no Retailer can be in the same type of program at any given time. So if they buy a finished product from us, they can't also be in a Program where they buy materials to make it themselves.</p>
  
  <pre><code>StageNo   ProgramNo   CustomerNo    ProgramType   StageDescription  StartDate   EndDate
  
  CAPS041835  CAP010611   RL023238    Packaged            Nurture     2019-04-04  2019-04-04    
  CAPS041836  CAP010611   RL023238    Packaged            Inactive    2019-04-05  2999-01-01
  CAPS041837  CAP010612   RL023238    Pre-Made in Bulk    Nurture     2019-04-04  2999-01-01
  </code></pre>
  
  <p>Above is an example of an anomaly in the data. 01/01/2999 just means it's a blank date in our ERP. </p>
  
  <p>On 04/04/2019, a user created a Packaged Program, and decided the retailer should have been set up for Pre-Made in Bulk instead of Packaged. </p>
  
  <p>The ERP ends the current stage on the last Invoice date, and if non exist, then will end it with today's date, and start the Inactive Stage as of Today + 1. </p>
  
  <p>So If I run analytics, any shipments on 04/04/19 will be applied to both the Packaged and Pre-Made Programs. </p>
  
  <p>Idealy, I would WANT to get rid of the Packaged Program entirely, but if not possible, this is how I'd like to clean it:</p>
  
  <pre><code>StageNo   ProgramNo   CustomerNo    ProgramType   StageDescription  StartDate   EndDate
  
  CAPS041835  CAP010611   RL023238    Packaged            Nurture     2019-04-04  2019-04-04    
  CAPS041836  CAP010611   RL023238    Packaged            Inactive    2019-04-04  2019-04-04
  CAPS041837  CAP010612   RL023238    Pre-Made in Bulk    Nurture     2019-04-04  2999-01-01
  </code></pre>
  
  <p>I can go through and fix it once it's like this. Even if I left it, it wouldn't be the end of the world because I can cast the shipment date as DateTime, and then +1 second, which means the sale will only fall in 1 Program.</p>
  
  <p>I started by writing a query to find the gaps between the date ranges to find any gaps where the date diff is less than 0.</p>
  
  <p>This is what I have so far...</p>
  
  <pre><code>WITH CustomerProgram AS 
  (
      SELECT
           ROW_NUMBER() OVER (ORDER BY [CustomerNo] ASC, [ProgramGroupId] ASC, [StageStartDate] ASC, [StageEndDate] ASC, [StagePrecedence] ASC, [CustomerProgramStageNo] ASC) AS [RowId]
          ,*
          ,COUNT([CustomerProgramStageNo]) OVER (PARTITION BY [ProgramGroupId]) AS [StageCount]
      FROM
      (
          SELECT
               --RANK() OVER (ORDER BY [CustomerNo] ASC, [ProgramDescription] ASC) AS [ProgramGroupId]
               RANK() OVER (ORDER BY [CustomerNo] ASC, [CustomerProgramNo] ASC) AS [ProgramGroupId]
              ,[CustomerProgramNo]
              ,[CustomerProgramStageNo]
              ,[CustomerNo]
              ,[ProgramCode]
              ,[ProgramStageCode]
              ,[ProgramStageDescription]
              ,CASE [ProgramStageDescription]
                  WHEN 'Nurture'          THEN 1
                  WHEN 'Graduate'         THEN 2
                  WHEN 'Change Ownership' THEN 3
                  WHEN 'Restart'          THEN 3
                  WHEN 'Ongoing'          THEN 4
                  WHEN 'Inactive'         THEN 5
                  ELSE NULL
              END                                 AS [StagePrecedence]
              ,CAST([StageStartDate] AS DATETIME) AS [StageStartDate]
              ,CAST([StageEndDate] AS DATETIME)   AS [StageEndDate]
          FROM
              [CustomerProgramAndStage]
      )   CustomerProgram
  )
  
  ,StagesAndGaps AS 
  (
      SELECT
           ROW_NUMBER() OVER (ORDER BY [CustomerNo] ASC, [ProgramGroupId] ASC, [StageStartDate] ASC, [StageEndDate] ASC) AS [RowId]
          ,[ProgramGroupId]
          ,[StageCount]
          ,[CustomerNo]
          ,[DateRangeType]
          ,[StageStartDate]
          ,[StageEndDate]
          ,DATEDIFF(DAY,[StageStartDate],[StageEndDate])  AS [StageDateDayDiff]
          ,DATEDIFF(YEAR,[StageStartDate],[StageEndDate]) AS [StageDateYearDiff]
          ,[StartDateRowId]
          ,[EndDateRowId]
          ,[PreviousProgramCode]
          ,[NextProgramCode]
          ,[PreviousStagePrecedence]
          ,[NextStagePrecedence]
          ,[PreviousStageNo]
          ,[NextStageNo]
      FROM
      (
          SELECT
               [ProgramGroupId]                           AS [ProgramGroupId]
              ,[StageCount]                               AS [StageCount]
              ,[CustomerNo]                               AS [CustomerNo]
              ,[DateRangeType]                            AS [DateRangeType]
              ,ISNULL([StageStartDate],'1800-01-01')      AS [StageStartDate]
              ,ISNULL([StageEndDate],'3999-01-01')        AS [StageEndDate]
              ,ISNULL([StartDateRowId],0)                 AS [StartDateRowId]
              ,ISNULL([EndDateRowId],9999999)             AS [EndDateRowId]
              ,ISNULL([PreviousProgramCode],'Start')      AS [PreviousProgramCode]
              ,ISNULL([NextProgramCode],'End')            AS [NextProgramCode]
              ,ISNULL([PreviousStagePrecedence],0)        AS [PreviousStagePrecedence]
              ,ISNULL([NextStagePrecedence],999)          AS [NextStagePrecedence]
              ,ISNULL([PreviousStageNo],'Start')          AS [PreviousStageNo]
              ,ISNULL([NextStageNo],'End')                AS [NextStageNo]
          FROM
          (
              SELECT --  Gaps include time period before the start of a Program
                   NextStage.[ProgramGroupId]                 AS [ProgramGroupId]
                  ,NextStage.[StageCount]                     AS [StageCount]
                  ,NextStage.[CustomerNo]                     AS [CustomerNo]
                  ,'Gap'                                      AS [DateRangeType]
                  ,PreviousStage.[StageEndDate]               AS [StageStartDate]
                  ,NextStage.[StageStartDate]                 AS [StageEndDate]
                  ,PreviousStage.[RowId]                      AS [StartDateRowId]
                  ,NextStage.[RowId]                          AS [EndDateRowId]
                  ,PreviousStage.[ProgramCode]                AS [PreviousProgramCode]
                  ,NextStage.[ProgramCode]                    AS [NextProgramCode]
                  ,PreviousStage.[StagePrecedence]            AS [PreviousStagePrecedence]
                  ,NextStage.[StagePrecedence]                AS [NextStagePrecedence]
                  ,PreviousStage.[CustomerProgramStageNo]     AS [PreviousStageNo]
                  ,NextStage.[CustomerProgramStageNo]         AS [NextStageNo]
              FROM
              (
                  SELECT
                       [RowId]
                      ,[ProgramGroupId]
                      ,[StageCount]
                      ,[CustomerProgramStageNo]
                      ,[CustomerNo]
                      ,[ProgramCode]
                      ,[StagePrecedence]
                      ,[StageStartDate]
                  FROM
                      CustomerProgram
              )   NextStage    
              LEFT JOIN
              (
                  SELECT
                       [RowId]
                      ,[ProgramGroupId]
                      ,[StageCount]
                      ,[CustomerProgramStageNo]
                      ,[CustomerNo]
                      ,[ProgramCode]
                      ,[StagePrecedence]
                      ,[StageEndDate]
                  FROM
                      CustomerProgram
              )   PreviousStage
                      ON NextStage.[ProgramGroupId] = PreviousStage.[ProgramGroupId]
                      AND NextStage.[RowId] - 1 = PreviousStage.[RowId]
  
              UNION
  
              SELECT --  Gaps include time period after the end of a Program (year 2999 if Stage is active)
                   PreviousStage.[ProgramGroupId]             AS [ProgramGroupId]
                  ,PreviousStage.[StageCount]                 AS [StageCount]
                  ,PreviousStage.[CustomerNo]                 AS [CustomerNo]
                  ,'Gap'                                      AS [DateRangeType]
                  ,PreviousStage.[StageEndDate]               AS [StageStartDate]
                  ,NextStage.[StageStartDate]                 AS [StageEndDate]
                  ,PreviousStage.[RowId]                      AS [StartDateRowId]
                  ,NextStage.[RowId]                          AS [EndDateRowId]
                  ,PreviousStage.[ProgramCode]                AS [PreviousProgramCode]
                  ,NextStage.[ProgramCode]                    AS [NextProgramCode]
                  ,PreviousStage.[StagePrecedence]            AS [PreviousStagePrecedence]
                  ,NextStage.[StagePrecedence]                AS [NextStagePrecedence]
                  ,PreviousStage.[CustomerProgramStageNo]     AS [PreviousStageNo]
                  ,NextStage.[CustomerProgramStageNo]         AS [NextStageNo]
              FROM
              (
                  SELECT
                       [RowId]
                      ,[ProgramGroupId]
                      ,[StageCount]
                      ,[CustomerProgramStageNo]
                      ,[CustomerNo]
                      ,[ProgramCode]
                      ,[StagePrecedence]
                      ,[StageEndDate]
                  FROM
                      CustomerProgram
              )   PreviousStage
              LEFT JOIN
              (
                  SELECT
                       [RowId]
                      ,[ProgramGroupId]
                      ,[StageCount]
                      ,[CustomerProgramStageNo]
                      ,[CustomerNo]
                      ,[ProgramCode]
                      ,[StagePrecedence]
                      ,[StageStartDate]
                  FROM
                      CustomerProgram
              )   NextStage
                      ON PreviousStage.[ProgramGroupId] = NextStage.[ProgramGroupId]
                      AND PreviousStage.[RowId] + 1 = NextStage.[RowId]
  
              UNION
  
              SELECT --  Stage data
                   [ProgramGroupId]           AS [ProgramGroupId]
                  ,[StageCount]               AS [StageCount]
                  ,[CustomerNo]               AS [CustomerNo]
                  ,'Stage'                    AS [DateRangeType]
                  ,[StageStartDate]           AS [StageStartDate]
                  ,[StageEndDate]             AS [StageEndDate]
                  ,[RowId]                    AS [StartDateRowId]
                  ,[RowId]                    AS [EndDateRowId]
                  ,[ProgramCode]              AS [PreviousProgramCode]
                  ,[ProgramCode]              AS [NextProgramCode]
                  ,[StagePrecedence]          AS [PreviousStagePrecedence]
                  ,[StagePrecedence]          AS [NextStagePrecedence]
                  ,[CustomerProgramStageNo]   AS [PreviousStageNo]
                  ,[CustomerProgramStageNo]   AS [NextStageNo]
              FROM
                  CustomerProgram
          )   StagesAndGaps
      )   StagesAndGaps
  )
  
  
  SELECT 
      *
  FROM 
      StagesAndGaps
  WHERE 
      [DateRangeType] = 'Gap'
      AND [StageStartDate] NOT IN ('1800-01-01','2999-01-01')
  ORDER BY 
      [RowId] ASC
  </code></pre>
  
  <p>I think I'm headed in the right direction, but I'm also not sure if there is an easier way. Sorry for the long post, but any help would be greatly appreciated!</p>