Dates and timespans with SQL

Question

Given the following table and data.

 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)

I need to insert records into that same table that calculates the actual price (list-discount) for each time period.

e.g. for product 1, I should have the following "ACTUAL" records

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

I kind of have it figured out for anything where a discount starts within a list price span, but I'm at a loss for anything else.

Thanks for the help

EDIT

There can be multiple discounts per ProductKey, but the discount periods won't overlap. So you could have one for 2010, and another one for 2012, but not 2 for 2010.

Also, if someone can come up with a better title, please do so. My poor brain is completely challenged at this point.

EDIT2

It's SQL server 2008R2. I'd love a beautiful set based answer (or someone that gives me a start in that direction), but will be just as happy with a cursor solution that works.

Answer 1

Clever puzzle.

You need to reconstruct all the time spans. To do this, I take out all the dates from the price ranges and reconstruct the possible date ranges.

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

I've thought about this some more. The basic idea above is correct. The basic idea is to break up the time dimension into intervals where the list and discount is constant over the entire interval. The question is how to create these intervals, which are in the datepairs alias.

These intervals have just a few rules:

• A datepair interval can begin when any time period begins.
• A datepair interval can begin one day after any time period ends.
• A datepair interval can end when any time period ends
• A datepair interval can end one day before any time period begins

Once we have the intervals, it is a simple matter to join in the appropriate list price and discounts for that period. The following query uses this logic:

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  

Answer 2

I believe this should give you what you are looking for. The key is to calculate all the unique date ranges without overlap for a single product.

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

Answer 3

To make this a little more interesting I added another segment that would have the same price as an earlier segment to make sure I was separating them:

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

Also I have a table called tblDates that is populated by the following:

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

The script I'm giving here doesn't require that. But having it couldn't hurt speed and doesn't really take up that much room. Here is my answer:

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

There are a couple other answers now, so this is just another way of doing things; there are many.