Filter overlapping date ranges with latest "Valid from" date

Question

I have a table with price lists that specifies the price of an item in a country for a specific time period. The issue with my table is, that there are overlapping price lists for the same item in the same country. What I wish, is to select the overlapping price list with the latest valid from date. Looking at the picture below the highlighted rows would be the desired end-result out of the 7 example rows:

Data sample:

CREATE TABLE #PriceList
(
    [Country] VARCHAR(15),
    [Price list] VARCHAR(25),
    [Item number] INTEGER,
    [Price] DECIMAL(10, 2),
    [Valid from] INTEGER,
    [Valid to] INTEGER
);

INSERT INTO #PriceList
(
    Country,
    [Price list],
    [Item number],
    Price,
    [Valid from],
    [Valid to]
)
VALUES
('United Kingdom', 'Price list Tech', 33333, 20.50, 20200101, 20201231),
('United Kingdom', 'Price list Tech', 33333, 20.50, 20200102, 20201231),
('Germany', 'Price list stuff', 44444, 15.50, 20200102, 20201231),
('Germany', 'Price list stuff', 44444, 15.50, 20200101, 20200630),
('USA', 'Price list textile new', 55555, 32.25, 20200102, 20201231),
('USA', 'Price list textile', 55555, 32.50, 20200101, 20200630),
('Germany', 'Price list stuff', 44444, 15.50, 20210101, 20210630);

What I have tried:

SELECT  Country,
       [Price list],
       [Item number],
       [Price],
       [Valid from],
       [Valid to]
       FROM(
SELECT Country,
       [Price list],
       [Item number],
       [Price],
       [Valid from],
       [Valid to],
       ROW_NUMBER() OVER (PARTITION BY a.Country,
                                       a.[Item number],
                                       a.[Valid to]
                          ORDER BY a.[Valid from] DESC
                         ) AS rn
FROM #PriceList a
) b
WHERE b.rn = 1

OUTPUT:

As you can see there are still overlapping price lists.

If I remove "Valid to" in the partition by clause then row_number will eliminate too many price lists. As you can see it eliminated two of Germany's price lists when it should only have eliminated the overlapping one. Without "Valid to", it strictly just checks for the latest "Valid from" date.

SELECT  Country,
       [Price list],
       [Item number],
       [Price],
       [Valid from],
       [Valid to]
       FROM(
SELECT Country,
       [Price list],
       [Item number],
       [Price],
       [Valid from],
       [Valid to],
       ROW_NUMBER() OVER (PARTITION BY a.Country,
                                       a.[Item number]
                          ORDER BY a.[Valid from] DESC
                         ) AS rn
FROM #PriceList a
) b
WHERE b.rn = 1

All help, tips and ideas are highly appreciated!

The ``Valid from`` is different in all rows (e.g germany) so the output 1 is correct. or post your expected result — sa-es-ir, Mar 10 '22 at 16:29
The rows highlighted in green (beginning of the post) is the expected/desired result. — Rubrix, Mar 10 '22 at 17:44
germany appears twice in green(expected results), so you want the last price value/[valid from] **and** per _year(valid from)_ ? — jjdesign, Mar 10 '22 at 21:03
Hey @jjdesign, yes since in 2021 there is only one price list row for Germany so I want to keep that one but in 2020 there are two (20200101-20201231) and (20200102-20201231) and since I can't have overlapping date ranges I only want to keep (20200102-20201231) since "20200102" is later than "20200101" — Rubrix, Mar 11 '22 at 07:50

score 0 · Answer 1 · 2022-03-11T08:01:09.210

Here we use RANK() OVER (PARTITION BY in a CTE to identify the rows that we want and then filter in the WHERE.
I have also modified the column names to replace spaces with an underscore which makes queries much easier to write.
I've added valid_From to the order by in the Rank() to eliminate duplicate Valid_To value for United Kingdom.

CREATE TABLE PriceList
(
    [Country] VARCHAR(15),
    [Price_list] VARCHAR(25),
    [Item_number] INTEGER,
    [Price] DECIMAL(10, 2),
    [Valid_from] INTEGER,
    [Valid_to] INTEGER
);

INSERT INTO PriceList
(
    Country,
    [Price_list],
    [Item_number],
    Price,
    [Valid_from],
    [Valid_to]
)
VALUES
('United Kingdom', 'Price list Tech', 33333, 20.50, 20200101, 20201231),
('United Kingdom', 'Price list Tech', 33333, 20.50, 20200102, 20201231),
('Germany', 'Price list stuff', 44444, 15.50, 20200102, 20201231),
('Germany', 'Price list stuff', 44444, 15.50, 20200101, 20200630),
('USA', 'Price list textile new', 55555, 32.25, 20200102, 20201231),
('USA', 'Price list textile', 55555, 32.50, 20200101, 20200630),
('Germany', 'Price list stuff', 44444, 15.50, 20210101, 20210630);
GO

7 rows affected

select * from pricelist;
GO

Country        | Price_list             | Item_number | Price | Valid_from | Valid_to
:------------- | :--------------------- | ----------: | ----: | ---------: | -------:
United Kingdom | Price list Tech        |       33333 | 20.50 |   20200101 | 20201231
United Kingdom | Price list Tech        |       33333 | 20.50 |   20200102 | 20201231
Germany        | Price list stuff       |       44444 | 15.50 |   20200102 | 20201231
Germany        | Price list stuff       |       44444 | 15.50 |   20200101 | 20200630
USA            | Price list textile new |       55555 | 32.25 |   20200102 | 20201231
USA            | Price list textile     |       55555 | 32.50 |   20200101 | 20200630
Germany        | Price list stuff       |       44444 | 15.50 |   20210101 | 20210630

WITH prices AS
(SELECT
  Country,
  Item_Number,
  Price,
  Valid_From,
  Valid_To,
  RANK() OVER ( 
     PARTITION BY Country,Item_Number
     ORDER BY Valid_To DESC,
      Valid_From )ranking
FROM
  PriceList
)
SELECT
  Country,
  Item_Number,
  Price,
  Valid_From,
  Valid_To
FROM
  prices
WHERE
  ranking = 1;
GO

Country        | Item_Number | Price | Valid_From | Valid_To
:------------- | ----------: | ----: | ---------: | -------:
Germany        |       44444 | 15.50 |   20210101 | 20210630
United Kingdom |       33333 | 20.50 |   20200101 | 20201231
USA            |       55555 | 32.25 |   20200102 | 20201231

db<>fiddle here

Hey @Kendle, your suggested solution still returns overlapping pirce lists e.g. United Kingdom. It should only give you the United Kingdom row with the Valid_From 20200102 and Valid_To 20201231 and also your code is eliminating the Germany row with Valid_From 20200102 and Valid_To 20201231. The green rows in the beginning of the post is the desired result. — Rubrix, Mar 11 '22 at 07:40
There should be two rows for Germany: 20200102-20201231 20210101-20210630 — Rubrix, Mar 11 '22 at 08:05
If you have overlapping tarifs which do you want to keep? For example if a is from Jan to June, b from Mars to November and c from October to December? — , Mar 11 '22 at 08:11
Overlapping tarifs cannot occur but very interesting observation, I didnt think about that. There can only be a maximum of two price lists for within a year and even that is very rare. 95% of the cases there are only one price list for an Item in a Country within one year. — Rubrix, Mar 11 '22 at 09:22
So in my example we would reject a because b exists and reject b because c exists and the query will only return c. Is that ok for you? — , Mar 11 '22 at 09:37
Germany 20200102 - 20201231 still needs to exist. I am getting the same result with my old code as you get. So we are still at the same point. So the issue is still the same — Rubrix, Mar 11 '22 at 10:04
You need to define the rule, maybe we should truncate the validity of tarifs when a new one is issued, maybe with a condition that the new one must be valid longer then the old one. For example if a is from Jan to June, b from March to November and c from October to December we want a from Jan-Feb, b from March to September and c from October to December? — , Mar 11 '22 at 10:30

jjdesign · Answer 2 · 2022-03-11T11:11:35.040

try this simplified query with [price list] / Country / Item / year(valid from)
the windowed function FIRST_VALUE() can do the trick along with distinct

select distinct Country,[Item number]
       ,[year]       = [Valid from]/10000
       ,[Price List] = FIRST_VALUE([Price list])OVER(partition by Country,[Item number],[Valid from]/10000 order by [Valid from] desc)
       ,[price]      = FIRST_VALUE(Price)       OVER(partition by Country,[Item number],[Valid from]/10000 order by [Valid from] desc)
from  #PriceList

results:

Country        | Item number | year | Price List             | price 
-------        | ----------- | -----| ---------------------- | ----- 
Germany        | 44444       | 2020 | Price list stuff       | 15.50
Germany        | 44444       | 2021 | Price list stuff       | 15.50
United Kingdom | 33333       | 2020 | Price list Tech        | 20.50
USA            | 55555       | 2020 | Price list textile new | 32.25

Filter overlapping date ranges with latest "Valid from" date

2 Answers2