title: SQL Cut Stretch Date into various stretchs
body: <p>I have a large stretch of dates in one table and some dates to cut in other table.</p>
  
  <p>With this I need to get another table with all the stretchs inside the large one cut by the others dates.</p>
  
  <p><a href="https://i.stack.imgur.com/eKhi0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eKhi0.png" alt="enter image description here"></a></p>
  
  <p>I have this for the moment:</p>
  
  <pre><code>DECLARE @UTCSTARTDATE DATETIME='2020-01-20 00:00:00.00'
  DECLARE @UTCENDDATE DATETIME='2020-01-20 04:00:00.00'
  
  DECLARE @T1 TABLE
  (
      StartTime DATETIME,
      EndTime DATETIME,
      table_id int
  )
  
  DECLARE @T2 TABLE
  (
      CutTime DATETIME,
      table2_id int
  )
  
  
  INSERT INTO @T1 SELECT @UTCSTARTDATE,@UTCENDDATE,1
  
  INSERT INTO @T2 SELECT myCut,myID FROM (VALUES('2020-01-20 01:00:00.00',1),('2020-01-20 02:30:00.00',1),('2020-01-20 03:00:00.00',2))t2(myCut,myID)
  
  
  SELECT * FROM @T1
  
  SELECT * FROM @T2 order by CutTime ASC
  
  
  
  DECLARE @STRETCHHOURSTABLE TABLE
      (
          startDate               DATETIME,
          endDate                 DATETIME
  
      );
  
      ;WITH DATESPLITTER AS
      (
              SELECT 
                  StartTime AS stretchStartDate, 
                  (SELECT TOP(1) CutTime FROM @T2 where CutTime &lt; EndTime AND table_id = table2_id order by CutTime ASC) AS stretchEndDate,
                  EndTime AS RealEndTime,
                  table_id AS RealID
              FROM @T1
          UNION ALL 
              SELECT 
                  stretchEndDate,
                  (SELECT TOP(1) CutTime FROM @T2 where CutTime &gt; stretchEndDate AND CutTime &lt; RealEndTime AND table2_id = RealID order by CutTime ASC) AS stretchEndDate,
                  RealEndTime,
                  RealID
              FROM DATESPLITTER
              WHERE stretchEndDate &lt; @UTCENDDATE
      )
          INSERT INTO @STRETCHHOURSTABLE (startDate, endDate)
          SELECT 
              stretchStartDate,
              CASE    
                  WHEN @UTCENDDATE &lt; stretchEndDate THEN @UTCENDDATE
                  ELSE stretchEndDate
              END 
          FROM DATESPLITTER
  
  SELECT * FROM @STRETCHHOURSTABLE
  </code></pre>