Wiki MaCa

This is an old revision of the document! —-

Create a unique list of start dates per key.
Join the lowest end date that is more recent than the start date per key.
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.
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

1. - 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; 

1. - 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