Wiki MaCa

This code is not yet thoroughly tested.

1. Create a unique list of start dates per key.
2. Join the lowest end date that is more recent than the start date per key.
3. Order the list by by start date from new to older and get the value of start date and end date of the previous row.
4. If the previous row is not empty and the end date is more recent then the start of the previous row, then replace the end date with the start date of the previous row.

Example in SQL Server:

 
BEGIN 
  -- Drop temporary tables 
  IF OBJECT_ID('tempdb.dbo.#prodgroep', 'U') IS NOT NULL 
  DROP TABLE #prodgroep; 

  IF OBJECT_ID('tempdb.dbo.#tarief', 'U') IS NOT NULL 
  DROP TABLE #tarief; 

  IF OBJECT_ID('tempdb.dbo.#dat_van', 'U') IS NOT NULL 
  DROP TABLE #dat_van; 

  IF OBJECT_ID('tempdb.dbo.#dat_tot', 'U') IS NOT NULL 
  DROP TABLE #dat_tot; 

  IF OBJECT_ID('tempdb.dbo.#dat', 'U') IS NOT NULL 
  DROP TABLE #dat; 

  -- Create temporary tables 
  SELECT 
    1						AS ProdID 
   , 'Productgroep A'		AS Naam 
   , '2006-01-01'			AS DatVan 
   , '2012-12-31'			AS DatTot 
   , 1						AS TariefID 
  INTO #prodgroep 

  SELECT 
    1						AS TariefID 
   , 'hoog'					AS Naam 
   , '2006-01-01'			AS DatVan 
   , '2008-12-31'			AS DatTot 
  INTO #tarief 
  UNION ALL 
  SELECT 
    1						AS TariefID 
   , 'laag'					AS Naam 
   , '2009-01-01'			AS DatVan 
   , '2010-12-31'			AS DatTot 
  UNION ALL 
  SELECT 
    1						AS TariefID 
   , 'hoog'					AS Naam 
   , '2011-01-01'			AS DatVan 
   , '2012-12-31'			AS DatTot 
   
  INSERT INTO #prodgroep 
  SELECT 
    2						AS ProdID 
   , 'Productgroep b'		AS Naam 
   , '2006-01-01'			AS DatVan 
   , '2007-12-31'			AS DatTot 
   , 2						AS TariefID 
  UNION ALL 
  SELECT 
     2						AS ProdID 
   , 'Productgroep b'		AS Naam 
   , '2008-01-01'			AS DatVan 
   , '2009-12-31'			AS DatTot 
   , 2						AS TariefID 
   
  INSERT INTO #tarief 
  SELECT 
    2						AS TariefID 
   , 'hoog'					AS Naam 
   , '2007-01-01'			AS DatVan 
   , '2008-12-31'			AS DatTot 
  UNION ALL 
  SELECT 
    2						AS TariefID 
   , 'laag'					AS Naam 
   , '2009-01-01'			AS DatVan 
   , '2010-12-31'			AS DatTot 

  SELECT 
  p.ProdID 
  , p.DatVan 
  INTO #dat_van 
  FROM #prodgroep p 
  UNION 
  SELECT 
  p.ProdID 
  , t.DatVan 
  FROM #prodgroep p 
  LEFT OUTER JOIN #tarief t 
  ON p.TariefID = t.TariefID 
    
  SELECT 
  p.ProdID 
  , p.DatTot 
  INTO #dat_tot 
  FROM #prodgroep p 
  UNION 
  SELECT 
  p.ProdID 
  , t.DatTot 
  FROM #prodgroep p 
  LEFT OUTER JOIN #tarief t 
  ON p.TariefID = t.TariefID 
   
  SELECT 
     ROW_NUMBER() OVER (PARTITION BY dv.ProdID ORDER BY dv.DatVan DESC) AS ROWNUM 
   , dv.ProdID 
   , dv.DatVan 
   , MIN(dt.DatTot) AS DatTot 
   , LAG(dv.DatVan, 1, NULL) OVER (PARTITION BY dv.ProdID ORDER BY dv.DatVan DESC) AS DatVanVorig 
   , LAG(MIN(dt.DatTot), 1, NULL) OVER (PARTITION BY dv.ProdID ORDER BY dv.DatVan DESC) AS DatTotVorig 
  INTO #dat 
  FROM #dat_van dv 
  LEFT OUTER JOIN #dat_tot dt 
    ON dv.ProdID = dt.ProdID 
    AND dv.DatVan <= dt.DatTot 
  GROUP BY dv.ProdID, dv.DatVan 
  ORDER  BY dv.ProdID, dv.DatVan 
   
  SELECT 
     d.ProdID 
   , d.DatVan 
   , d.DatTot 
   , d.DatVanVorig 
   , d.DatTotVorig 
   , d.DatVan AS DatVanDef 
   , CASE 
       WHEN d.DatVanVorig IS NOT NULL AND d.DatTot > d.DatVanVorig 
  	   THEN d.DatVanVorig 
  	 ELSE d.DatTot 
     END  AS DatTotDef 
  FROM #dat d 
  ORDER BY d.ProdID, d.DatVan 

END