Is There a Better Way to Combine Timespans in SQL Server

I need to combine diferent Timespans from diferent tables into a new Result Set.The ideal output is described by the picture below:

ideal output of combined timespans

My attempt has been to use nested cursors and it works fine. But I'd like to be exposed to a better alternative. My guess is that there should be some recursive structure that will make this task more efficient.

Here's my solution with nested cursors and the related tables:


   DECLARE @COUTRIES TABLE
   (
          CNTY VARCHAR(10)
          , CG_START DATE NULL
          , CG_END DATE NULL
   )
   
   INSERT INTO @COUTRIES VALUES ('UKR' , '2021-01-01', '2021-05-15')
   INSERT INTO @COUTRIES VALUES ('US' , '2021-03-07', '2021-12-31')
   INSERT INTO @COUTRIES VALUES ('DE' , NULL, NULL)
   INSERT INTO @COUTRIES VALUES ('GB' , '2023-01-01', NULL)
   
   
   DECLARE @PRICE TABLE
   (
          PRICE INT
          , P_START DATE NULL
          , P_END DATE NULL
   )
   
   INSERT INTO @PRICE VALUES (100, NULL, '2021-04-01')
   INSERT INTO @PRICE VALUES (200, '2021-04-01', '2021-04-15')
   INSERT INTO @PRICE VALUES (300, '2021-04-15', '2021-06-01')
   INSERT INTO @PRICE VALUES (400, '2021-06-01', '2022-11-01')
   INSERT INTO @PRICE VALUES (500, '2022-11-01', NULL)
   
   DECLARE @PRICEVALUE INT
   DECLARE @PRICE_STARTDATE DATE
   DECLARE @PRICE_ENDDATE DATE
   DECLARE @MIDDATE DATE
   DECLARE @EVENTS TABLE (ID INT IDENTITY(1,1), PRICE INT, STARTDATE DATE, ENDDATE DATE)

   DECLARE DB_CURSOR1 CURSOR FOR 
   SELECT PRICE
   FROM @PRICE
   ORDER BY P_START
   
   OPEN DB_CURSOR1  
   FETCH NEXT FROM DB_CURSOR1 INTO @PRICEVALUE 

   WHILE @@FETCH_STATUS = 0  
   BEGIN  
   
       
       SELECT @PRICE_STARTDATE = P.P_START FROM @PRICE P WHERE P.PRICE = @PRICEVALUE
       
       INSERT INTO @EVENTS(PRICE,STARTDATE) VALUES (@PRICEVALUE, @PRICE_STARTDATE)

       SELECT @PRICE_ENDDATE = P.P_END FROM @PRICE P WHERE P.PRICE = @PRICEVALUE
       

       DECLARE DB_CURSOR2 CURSOR FOR
           SELECT C.CG_START MIDDATE FROM @COUTRIES C WHERE (@PRICE_STARTDATE IS NULL OR (C.CG_START >= @PRICE_STARTDATE)) AND (@PRICE_ENDDATE IS NULL OR (C.CG_START <= @PRICE_ENDDATE) )
           UNION
           SELECT C.CG_END MIDDATE FROM @COUTRIES C WHERE  (@PRICE_STARTDATE IS NULL OR (C.CG_END >= @PRICE_STARTDATE)) AND (@PRICE_ENDDATE IS NULL OR (C.CG_END <= @PRICE_ENDDATE) )
           ORDER BY MIDDATE
       
       OPEN DB_CURSOR2
       FETCH NEXT FROM DB_CURSOR2 INTO @MIDDATE

       WHILE @@FETCH_STATUS = 0  
       BEGIN 
       
           UPDATE @EVENTS SET ENDDATE = @MIDDATE WHERE ID = (SELECT TOP 1 ID FROM @EVENTS ORDER BY ID DESC)
           INSERT INTO @EVENTS(PRICE,STARTDATE) VALUES (@PRICEVALUE, @MIDDATE)
           
           FETCH NEXT FROM DB_CURSOR2 INTO @MIDDATE
       END

       UPDATE @EVENTS SET ENDDATE = @PRICE_ENDDATE WHERE ID = (SELECT TOP 1  ID FROM @EVENTS ORDER BY ID DESC)
       
       CLOSE DB_CURSOR2  
       DEALLOCATE DB_CURSOR2 

       FETCH NEXT FROM DB_CURSOR1 INTO @PRICEVALUE  

   END 

   CLOSE DB_CURSOR1  
   DEALLOCATE DB_CURSOR1 

   DELETE FROM @EVENTS WHERE STARTDATE IS NULL AND ENDDATE IS NULL
    
   SELECT * FROM @EVENTS 
How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum