Wiki MaCa

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

This code is not yet thoroughly tested.

  - 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:

<code>
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
</code>
Wiki MaCa

User Tools

Site Tools

Page Tools
