MIN or MAX Value for date ranges — Determines the lowest price for a specific date range, based on ranges of product identifiers, prices, and dates.

Question

I really hope some of you enjoy the problems. I have a table of product identifiers, prices, and date ranges when these prices are active.

+----+-------+---------------------+---------------------+ | Id | Price | StartDate | EndDate | +----+-------+---------------------+---------------------+ | 1 | 19 | 2016-12-01 00:00:00 | 2017-12-01 23:59:59 | | 1 | 18 | 2017-01-01 00:00:00 | 2018-01-12 23:59:59 | | 1 | 17 | 2017-02-03 00:00:00 | 2017-03-03 23:59:59 | | 1 | 16 | 2018-01-01 00:00:00 | 2018-03-02 23:59:59 | | 2 | 15 | 2017-01-01 00:00:00 | 2017-03-05 23:59:59 | | 2 | 15 | 2017-03-06 00:00:00 | 2017-03-31 23:59:59 | | 2 | 30 | 2017-04-01 00:00:00 | 2017-05-03 23:59:59 | | 3 | 12 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 | | 3 | 12 | 2017-02-01 00:00:00 | 2017-02-28 23:59:59 | | 4 | 14 | 2017-01-01 00:00:00 | 2017-04-05 23:59:59 | | 4 | 14 | 2017-04-01 00:00:00 | 2017-04-30 23:59:59 | | 4 | 12 | 2017-04-15 00:00:00 | 2017-05-30 23:59:59 | | 5 | 20 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 | | 5 | 20 | 2017-03-01 00:00:00 | 2017-03-31 23:59:59 | | 6 | 15 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 | | 6 | 15 | 2017-02-01 00:00:00 | 2017-02-28 23:59:59 | | 6 | 15 | 2017-04-01 00:00:00 | 2017-04-30 23:59:59 | +----+-------+---------------------+---------------------+ 

SQLFiddle: http://sqlfiddle.com/#!6/39288/1

I need to get it in a format where:

• Date periods have the same identifier and price, which “concern” (ie identifier No. 3) are combined in one period.

• Periods of dates that overlap (for example, identifier No. 4) are combined into one period.

• The lowest price is shown for each product and in what range.

• Ranges of dates that have gaps and the same price do not merge and are separate lines (i.e. identifier No. 5).

The result should be:

 +----+-------+---------------------+---------------------+ | Id | Price | StartDate | EndDate | +----+-------+---------------------+---------------------+ | 1 | 19 | 2016-12-01 00:00:00 | 2016-12-31 23:59:59 | | 1 | 18 | 2017-01-01 00:00:00 | 2017-02-02 23:59:59 | | 1 | 17 | 2017-02-03 00:00:00 | 2017-03-03 23:59:59 | | 1 | 19 | 2017-03-04 00:00:00 | 2017-12-01 23:59:59 | | 1 | 18 | 2017-12-02 00:00:00 | 2017-12-31 23:59:59 | | 1 | 16 | 2018-01-01 00:00:00 | 2018-03-02 23:59:59 | | 2 | 15 | 2017-01-01 00:00:00 | 2017-03-31 23:59:59 | | 2 | 30 | 2017-04-01 00:00:00 | 2017-05-03 23:59:59 | | 3 | 12 | 2017-01-01 00:00:00 | 2017-02-28 23:59:59 | | 4 | 14 | 2017-01-01 00:00:00 | 2017-04-14 23:59:59 | | 4 | 12 | 2017-04-15 00:00:00 | 2017-05-30 23:59:59 | | 5 | 20 | 2017-01-01 00:00:00 | 2017-01-31 23:59:59 | | 5 | 20 | 2017-03-01 00:00:00 | 2017-03-31 23:59:59 | | 6 | 15 | 2017-01-01 00:00:00 | 2017-02-28 23:59:59 | | 6 | 15 | 2017-04-01 00:00:00 | 2017-04-30 23:59:59 | +----+-------+---------------------+---------------------+ 

In general, in essence, it determines the best price between two dates.

I worked with this table in the past and was able to solve it in C #, but this time I need a clean TSQL approach.

I already went down with a deeply embedded CTE and lost my mind, not getting results close to what they should be. Thanks in advance to everyone who can help.

Edit: I even mixed up the desired results because it is so confusing. Fixed (I think).

Edit 2: Example:

 +------+-------+-------------------------+-------------------------+ | Id | Price | StartDate | EndDate | +------+-------+-------------------------+-------------------------+ | 8611 | 31.98 | 2017-06-06 00:00:00.000 | 2017-09-24 23:59:59.000 | | 8611 | 31.98 | 2017-09-25 00:00:00.000 | 2017-12-31 23:59:59.000 | | 8611 | 28.78 | 2017-07-31 00:00:00.000 | 2017-09-30 23:59:59.000 | | 8611 | 28.78 | 2017-10-30 00:00:00.000 | 2017-12-31 23:59:59.000 | +------+-------+-------------------------+-------------------------+ 

@GordonLinoff results:

 +------+-------+-------------------------+-------------------------+ | Id | Price | StartDate | EndDate | +------+-------+-------------------------+-------------------------+ | 8611 | 28.78 | 2017-06-06 00:00:00.000 | 2017-12-31 23:59:59.000 | +------+-------+-------------------------+-------------------------+ 

The result should be:

 +------+-------+-------------------------+-------------------------+ | Id | Price | StartDate | EndDate | +------+-------+-------------------------+-------------------------+ | 8611 | 31.98 | 2017-06-06 00:00:00.000 | 2017-07-30 23:59:59.000 | | 8611 | 28.78 | 2017-07-31 00:00:00.000 | 2017-09-30 23:59:59.000 | | 8611 | 31.98 | 2017-10-01 00:00:00.000 | 2017-10-29 23:59:59.000 | | 8611 | 28.78 | 2017-10-30 00:00:00.000 | 2017-12-31 23:59:59.000 | +------+-------+-------------------------+-------------------------+ 

Answer 1

Do you have a calendar / date table? If so, then you can use the date table to help you get the minimum price for the product for each date in periods in the table.

After that, you can get the start and end dates of each of the periods by looking at the next and previous records with the same product identifier. You can use the LAG and LEAD functions for this. This gives you the outer boundaries of each of your desired groups.

From there, it just messes around a bit to get the final result. I have given the example below, which should give you the desired results.

 --Get the best price per date for each product WITH BestPricePerDate AS ( SELECT Id, MIN(Price) Price, c.[Date] FROM [YourTable] yt INNER JOIN dbo.Calendar c ON c.[Date] BETWEEN yt.StartDate AND yt.EndDate GROUP BY Id, [Date] ), --Check whether the date is the start or the end of a period PeriodsMarkedPerId AS( SELECT Id, Price, [Date], CASE WHEN ISNULL(LAG(Price,1) OVER (PARTITION BY Id ORDER BY [Date]),-1) <> Price OR ISNULL(LAG([Date],1) OVER (PARTITION BY Id ORDER BY [Date]),'1999-01-01') <> DATEADD(DAY,-1,[Date]) THEN 1 ELSE 0 END IsStartDate, CASE WHEN ISNULL(LEAD(Price,1) OVER (PARTITION BY Id ORDER BY [Date]),-1) <> Price OR ISNULL(LEAD([Date],1) OVER (PARTITION BY Id ORDER BY [Date]),'1999-01-01') <> DATEADD(DAY,1,[Date]) THEN 1 ELSE 0 END IsEndDate FROM BestPricePerDate ), --Keep only the start and end date records PeriodStartAndEndDates AS( SELECT Id, Price, [Date], IsStartDate, IsEndDate FROM PeriodsMarkedPerId WHERE IsStartDate = 1 OR IsEndDate = 1 ), --Move StartDate and EndDate to one record StartAndEndDatesOnSameRow AS( SELECT Id, Price, [Date] AS StartDate, LEAD([Date],1) OVER (ORDER BY Id, [Date]) AS EndDate, IsStartDate FROM PeriodStartAndEndDates ) --Get the resulting periods SELECT Id, Price, StartDate, EndDate FROM StartAndEndDatesOnSameRow WHERE IsStartDate = 1 ORDER BY Id, StartDate 

If you do not have a date table, you can easily create one. There are many examples of this on the Internet.

Hope this helps!

Answer 2

You can define the beginning of a period as not matching. This is complicated, but can be done using exists or the cumulative maximum of the end date, with the exception of the current line.

Then each non-overlap is the beginning of the group. This group can be used for aggregation:

 select id, min(startDate) as startDate, max(endDate) as endDate, min(price) as price from (select t.*, sum(case when prev_endDate < dateadd(second, -1, startDate) then 1 else 0 end) over (partition by id order by startdate) as grp from (select t.*, max(endDate) over (partition by id order by startdate rows between unbounded preceding and 1 preceding ) as prev_endDate from t ) t ) t group by id, grp; 

I am not 100% sure that this works. I just thought about using a cumulative maximum end date for this. I'm sure it covers all overlapping cases, but I may have missed something.