I really hope some of you like challenges. I have a table of Product Ids, Prices and the Date Ranges for when those 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 Id and price that "touch" (i.e. Id #3) are merged into one period.
Date periods that overlap (i.e. Id #4) are merged into one period.
The lowest price is shown for each product and during what range.
Date ranges that have gaps and the same price don't merge and are separate rows (i.e. Id #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 |
+----+-------+---------------------+---------------------+
Overall, it's essentially determining the best price between two dates.
I've worked with this table in the past and was able to solve it in C#, but this time I need a pure TSQL approach.
I've already gone down some deep nested CTE's and lost my mind with getting results nowhere near what they should be. Thanks in advance for anyone who can assist.
Edit: I even messed up the desired results because this 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's results to:
+------+-------+-------------------------+-------------------------+
| Id | Price | StartDate | EndDate |
+------+-------+-------------------------+-------------------------+
| 8611 | 28.78 | 2017-06-06 00:00:00.000 | 2017-12-31 23:59:59.000 |
+------+-------+-------------------------+-------------------------+
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 |
+------+-------+-------------------------+-------------------------+