All geek questions in one place

Get non-overlapping date ranges for price history data

Suppose I have the following table:

CREATE TABLE [dbo].[PricesHist]
(
   [Product] [varchar](6) NOT NULL,
   [Price] [float] NOT NULL,
   [StartDate] [datetime] NOT NULL,
   [EndDate] [datetime] NOT NULL
)


INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D2C00000000 AS DateTime), CAST(0x00009D2C00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D2D00000000 AS DateTime), CAST(0x00009D2D00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D2E00000000 AS DateTime), CAST(0x00009D2E00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3000000000 AS DateTime), CAST(0x00009D3000000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3100000000 AS DateTime), CAST(0x00009D3100000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3400000000 AS DateTime), CAST(0x00009D3400000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D3500000000 AS DateTime), CAST(0x00009D3500000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3600000000 AS DateTime), CAST(0x00009D3600000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3700000000 AS DateTime), CAST(0x00009D3700000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3800000000 AS DateTime), CAST(0x00009D3800000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3A00000000 AS DateTime), CAST(0x00009D3A00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3B00000000 AS DateTime), CAST(0x00009D3B00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D3C00000000 AS DateTime), CAST(0x00009D3C00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3D00000000 AS DateTime), CAST(0x00009D3D00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3E00000000 AS DateTime), CAST(0x00009D3E00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3F00000000 AS DateTime), CAST(0x00009D3F00000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4100000000 AS DateTime), CAST(0x00009D4100000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4200000000 AS DateTime), CAST(0x00009D4200000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D4300000000 AS DateTime), CAST(0x00009D4300000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4400000000 AS DateTime), CAST(0x00009D4400000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4500000000 AS DateTime), CAST(0x00009D4500000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4600000000 AS DateTime), CAST(0x00009D4600000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4800000000 AS DateTime), CAST(0x00009D4800000000 AS DateTime))
INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D4A00000000 AS DateTime), CAST(0x00009D4A00000000 AS DateTime))

As you can see, there are two prices for Apple this month. 4.90 and 2.50. To arrange this table, I need to get this information as a range of dates, not a row per day, as it is now.

I obviously can do this with the Min and Max aggregates easily, but the ranges overlap and other business codes expect non-overlapping ranges. I also tried to achieve this with self joins and row_number (), but without much success ...

Here is what I am trying to achieve as a result:

Product | StartDate   |  EndDate    | Price
-------------------------------------------
Apples  | 01 Mar 2010 | 02 Mar 2010 | 4.90
Apples  | 03 Mar 2010 | 03 Mar 2010 | 2.50
Apples  | 05 Mar 2010 | 09 Mar 2010 | 4.90
Apples  | 10 Mar 2010 | 10 Mar 2010 | 2.50
Apples  | 11 Mar 2010 | 16 Mar 2010 | 4.90
Apples  | 17 Mar 2010 | 17 Mar 2010 | 2.50
Apples  | 18 Mar 2010 | 23 Mar 2010 | 4.90
Apples  | 24 Mar 2010 | 24 Mar 2010 | 2.50
Apples  | 25 Mar 2010 | 30 Mar 2010 | 4.90
Apples  | 31 Mar 2010 | 31 Mar 2010 | 2.50

What would be the best approach to do this?

Thank you very much in advance,

+3
source share
1 answer

. , , :

SELECT
    SD.Product,
    SD.Price,
    SD.StartDate,
    MAX(ED.EndDate) AS EndDate
FROM
    dbo.PricesHist SD
LEFT OUTER JOIN dbo.PricesHist ED ON
    ED.Product = SD.Product AND
    ED.Price = SD.Price
LEFT OUTER JOIN dbo.PricesHist LD ON
    LD.Product = SD.Product AND
    LD.Price <> SD.Price AND
    LD.EndDate < SD.StartDate
LEFT OUTER JOIN dbo.PricesHist LMD ON
    LMD.Product = SD.Product AND
    LMD.Price = SD.Price AND
    LMD.StartDate > ISNULL(LD.EndDate, '1900-01-01') AND
    LMD.StartDate < SD.StartDate
WHERE
    NOT EXISTS (SELECT * FROM dbo.PricesHist MD WHERE MD.Product = SD.Product AND MD.Price <> SD.Price AND MD.StartDate BETWEEN SD.StartDate AND ED.EndDate) AND
    LMD.Product IS NULL
GROUP BY
    SD.Product,
    SD.Price,
    SD.StartDate
ORDER BY
    SD.StartDate
+2

Source: https://habr.com/ru/post/1749944/

More articles:


All Articles