Fechas y timespans con SQL

Dada la siguiente tabla y datos.

create table prices (productKey int ,PriceType char(10) ,BeginDate date ,EndDate date ,price decimal(18,2)) insert into prices(productKey, PriceType,BeginDate,EndDate, price) values (1,'LIST','1-1-2010','1-15-2010',10), (1,'LIST','1-16-2010','10-15-2010',20), (1,'DISCOUNT','1-10-2010','1-15-2010',-5), (2,'LIST','2-1-2010','10-15-2010',30), (2,'LIST','10-16-2010','1-1-9999',35), (2,'DISCOUNT','2-10-2010','10-25-2010',-10), (2,'LIST','1-1-2010','1-15-2010',10), (3,'DISCOUNT','1-12-2010','1-1-9999',-5), (3,'LIST','1-16-2010','1-1-9999',10) 

Necesito insert loggings en la misma tabla que calcula el precio real (descuento de list) para cada período de time.

por ejemplo, para el producto 1, debería tener los siguientes loggings "ACTUALES"

 Begin End Price 1-1-2010 1-9-2010 10 1-10-2010 1-15-2010 5 1-16-2010 10-15-2010 20 

De alguna manera lo tengo resuelto para cualquier cosa en la que comience un descuento dentro de un range de precio de list, pero estoy perdido para cualquier otra cosa.

Gracias por la ayuda

EDITAR

Puede haber múltiples descuentos por ProductKey, pero los períodos de descuento no se superpondrán. Entonces podría tener uno para 2010 y otro para 2012, pero no 2 para 2010.

Además, si alguien puede get un título mejor, hágalo. Mi pobre cerebro está completamente desafiado en este punto.

EDIT2

Es el server SQL 2008R2. Me encantaría una hermosa respuesta basada en un set (o alguien que me sirva de inicio en esa dirección), pero me encantará la solución de cursor que funciona.

Ingenioso rompecabezas.

Necesitas rebuild todos los intervalos de time. Para hacer esto, saco todas las dates de los ranges de precios y reconstruyo los posibles intervalos de dates.

 with alldates as (select d.*, ROW_NUMBER() over (partition by productkey order by thedate) as seqnum from ((select productkey, BeginDate as thedate from prices) union all (select productkey, enddate as thedate from prices) ) d ), datepair as (select d1.productkey, d1.thedate as BeginDate, d2.thedate as EndDate from alldates d1 left outer join alldates d2 on d1.seqnum = d2.seqnum - 1 and d1.productKey = d2.productKey ) select dp.productkey, dp.BeginDate, dp.EndDate, SUM(p.price) from datepair dp join prices p on dp.productkey = p.productkey and dp.BeginDate >= p.BeginDate and dp.EndDate <= p.EndDate group by dp.productkey, dp.BeginDate, dp.EndDate order by 1, 2, 3 

He pensado en esto un poco más. La idea básica anterior es correcta. La idea básica es dividir la dimensión de time en intervalos donde la list y el descuento son constantes durante todo el intervalo. La pregunta es cómo crear estos intervalos, que están en el alias de los pares de date.

Estos intervalos tienen solo unas pocas reglas:

  • Un intervalo de datedor puede comenzar cuando comienza cualquier período de time.
  • Un intervalo de date puede comenzar un día después de que finalice cualquier período de time.
  • Un intervalo de date puede finalizar cuando finaliza cualquier período de time
  • Un intervalo de date puede finalizar un día antes de que comience cualquier período de time

Una vez que tenemos los intervalos, es una simple cuestión join al precio de list apropiado y los descuentos para ese período. La siguiente consulta usa esta lógica:

 with begindates as (select distinct productKey, thedate from ((select productkey, BeginDate as thedate from prices) union all (select productkey, dateadd(d, 1, enddate) as thedate from prices) ) d ), enddates as (select distinct productKey, thedate from ((select productkey, DATEADD(d, -1, begindate) as thedate from prices) union all (select productkey, enddate as thedate from prices) ) d ), datepair as (select * from (select d1.productkey, d1.thedate as BeginDate, MIN(d2.thedate) as EndDate from begindates d1 left outer join enddates d2 on d1.productKey = d2.productKey and d1.thedate < d2.thedate group by d1.productkey, d1.thedate ) t where BeginDate <> EndDate ) select dp.productkey, dp.BeginDate, dp.EndDate, SUM(p.price) from datepair dp join prices p on dp.productkey = p.productkey and dp.BeginDate >= p.BeginDate and dp.EndDate <= p.EndDate group by dp.productkey, dp.BeginDate, dp.EndDate order by 1, 2, 3 

Creo que esto debería darte lo que estás buscando. La key es calcular todos los intervalos de dates únicos sin solapamiento para un solo producto.

 -- Get all end dates SELECT ROW_NUMBER() OVER (PARTITION BY ProductKey ORDER BY EndDate) RowNum, ProductKey, EndDate INTO #EndDates FROM (SELECT ProductKey, EndDate FROM prices UNION SELECT ProductKey, DATEADD(d, -1, BeginDate) AS EndDate FROM prices) endDates ORDER BY EndDate -- Get all unique date ranges with no overlap SELECT a.ProductKey, DATEADD(d, 1, a.EndDate) BeginDate, b.EndDate INTO #DateRange FROM #EndDates a INNER JOIN #EndDates b ON a.RowNum = b.RowNum - 1 AND a.ProductKey = b.ProductKey ORDER BY productkey, enddate -- Get actual price SELECT d.ProductKey, d.BeginDate, d.EndDate, SUM(Price) ActualPrice FROM prices p INNER JOIN #DateRange d ON p.ProductKey = d.ProductKey AND p.BeginDate <= d.EndDate AND p.EndDate >= d.BeginDate GROUP BY d.ProductKey, d.BeginDate, d.EndDate ORDER BY d.ProductKey, d.BeginDate, d.EndDate -- Clean up DROP TABLE #EndDates DROP TABLE #DateRange 

Para hacer esto un poco más interesante, agregué otro segmento que tendría el mismo precio que un segmento anterior para asegurarme de que los estaba separando:

 INSERT INTO prices(productKey, PriceType,BeginDate,EndDate, price) VALUES (1, 'DISCOUNT', '5-2-2010', '5-8-2010', -15) 

También tengo una tabla llamada tblDates que está poblada por lo siguiente:

 INSERT dbo.tblDates (date1) SELECT TOP(65536) ROW_NUMBER()OVER(ORDER BY v1.number)-1 FROM master.dbo.spt_values v1 CROSS APPLY master.dbo.spt_values v2 WHERE v1.type='p' and v2.type='p' GO 

El script que estoy dando aquí no requiere eso. Pero tenerlo no puede dañar la velocidad y realmente no ocupa tanto espacio. Aquí está mi respuesta:

 DECLARE @InitialBeginDate DATE , @FinalEndDate DATE; SELECT @InitialBeginDate = MIN(BeginDate) , @FinalEndDate = MAX(EndDate) FROM prices WHERE productKey = @ProductKey CREATE TABLE #Dates ( DateValue DATE NOT NULL ) INSERT #Dates (DateValue) SELECT DATEADD(DAY, V.number, @InitialBeginDate) FROM master..spt_values V WHERE V.[type] = 'P' AND V.number BETWEEN 0 AND DATEDIFF(DAY, @InitialBeginDate, @FinalEndDate) ;WITH MergedDays AS ( SELECT ListedDates.DateValue , SUM(P.price) PriceOnDate , DATEDIFF(DAY, @InitialBeginDate, ListedDates.DateValue) - DENSE_RANK() OVER(PARTITION BY SUM(P.price) ORDER BY ListedDates.DateValue, SUM(P.price)) DateGroup FROM #Dates ListedDates INNER JOIN prices P ON P.BeginDate <= ListedDates.DateValue AND P.EndDate >= ListedDates.DateValue AND P.productKey = @ProductKey GROUP BY ListedDates.DateValue ) SELECT MIN(DateValue) AS SegmentBeginDate , MAX(DateValue) AS SegmentEndDate , MAX(PriceOnDate) AS SegmentPrice -- This is just to collapse it, it'll be the same for all records. FROM MergedDays GROUP BY DateGroup ORDER BY SegmentBeginDate DROP TABLE #Dates 

Hay otras dos respuestas ahora, así que esta es solo otra forma de hacer las cosas; Hay muchos.