How can I select first date together with last date (plus one day) every time price is changed in the same date column?

Question

Let's say I have a table with Price and Date as columns like:

Price       Date
3.20000000  2018-01-14
3.20000000  2018-01-18
3.20000000  2018-01-19
4.00000000  2018-02-10
4.00000000  2018-02-11
4.00000000  2018-02-12
5.10000000  2018-02-16
3.20000000  2018-03-11
4.00000000  2018-03-15

I wish to get the first date for every time a new price appears, and also together with the last date (plus one day) for that price in the same date column. Please note that the price can go up and down and the same price can occur several times in the entire table. So the result for the above table should be like:

Price       Date
3.20000000  2018-01-14
3.20000000  2018-01-20 -- 19 + 1
4.00000000  2018-02-10
4.00000000  2018-02-13 -- 12 + 1
5.10000000  2018-02-16
5.10000000  2018-02-17 -- 16 + 1
3.20000000  2018-03-11
3.20000000  2018-03-12 -- 11 + 1
4.00000000  2018-03-15
4.00000000  2018-03-16 -- 15 + 1

I wish to use self-join, and is it possible to do this without row_number function?

Answer 1

You can try this solution that not using row_number.

DECLARE @MyTable TABLE ([Price] DECIMAL(18,8), [Date] DATE);

INSERT INTO @MyTable ([Price], [Date])VALUES 
('3.20000000', '2018-01-14'),
('3.20000000', '2018-01-18'),
('3.20000000', '2018-01-19'),
('4.00000000', '2018-02-10'),
('4.00000000', '2018-02-11'),
('4.00000000', '2018-02-12'),
('5.10000000', '2018-02-16'),
('3.20000000', '2018-03-11'),
('4.00000000', '2018-03-15')

;WITH CTE AS 
(
    SELECT * FROM @MyTable 
    UNION ALL
    SELECT Price, DATEADD(DAY,1,[Date]) FROM @MyTable 
)
, CTE2 AS (
    SELECT T1.Price, MIN(T1.[Date]) MinDate, MAX(T1.[Date]) MaxDate
    FROM CTE T1
        OUTER APPLY( SELECT COUNT(DISTINCT Price) DCNT, MIN(Date) [Date] FROM CTE T2 WHERE T2.Date >= T1.Date ) TA1 
    GROUP BY T1.Price, TA1.DCNT
)
SELECT Price, [Date] FROM CTE2 UNPIVOT ( [Date] FOR Col IN (MinDate, MaxDate)) UNPVT
ORDER BY [Date]

Result:

Price                                   Date
--------------------------------------- ----------
3.20000000                              2018-01-14
3.20000000                              2018-01-20
4.00000000                              2018-02-10
4.00000000                              2018-02-13
5.10000000                              2018-02-16
5.10000000                              2018-02-17
3.20000000                              2018-03-11
3.20000000                              2018-03-12
4.00000000                              2018-03-15
4.00000000                              2018-03-16

Answer 2

As I wrote in my comment, this is a classic Gaps and islands problem, and the easy way to solve it is to use row_number twice.

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    Price decimal(15,5),
    [Date] Date
)

INSERT INTO @T (Price, [Date]) VALUES
(3.20000000, '2018-01-14'),
(3.20000000, '2018-01-18'),
(3.20000000, '2018-01-19'),
(4.00000000, '2018-02-10'),
(4.00000000, '2018-02-11'),
(4.00000000, '2018-02-12'),
(5.10000000, '2018-02-16'),
(3.20000000, '2018-03-11'),
(4.00000000, '2018-03-15')

Now, using a common table expression to mark the islands:

;WITH CTE AS
(
SELECT  Price, 
        [Date], 
        ROW_NUMBER() OVER(ORDER BY [Date]) - 
        ROW_NUMBER() OVER(PARTITION BY Price ORDER BY [Date]) As Island
FROM @T
)

And query that cte with a union query - one using min(date) over(partition by island) and the second using max(dateadd(day, 1, date)) over(partition by island), order the results by date:

SELECT Price, MIN(Date) OVER (PARTITION BY Island) As [Date]
FROM CTE
UNION
SELECT Price, DATEADD(DAY, 1, MAX(Date)  OVER (PARTITION BY Island))
FROM CTE
ORDER BY Date

Results:

Price       Date
3,20000     14.01.2018
3,20000     20.01.2018
4,00000     10.02.2018
4,00000     13.02.2018
5,10000     16.02.2018
5,10000     17.02.2018
3,20000     11.03.2018
3,20000     12.03.2018
4,00000     15.03.2018
4,00000     16.03.2018

You can see a live demo on rextester.

Answer 3

This is one variant. You can modified it to suit your needs.

DECLARE @DataSource TABLE
(
    [Price] SMALLMONEY
   ,[Date] DATE
);

INSERT INTO @DataSource ([Price], [Date])
VALUES ('3.20000000', '2018-01-14')
      ,('3.20000000', '2018-01-18')
      ,('3.20000000', '2018-01-19')
      ,('4.00000000', '2018-02-10')
      ,('4.00000000', '2018-02-11')
      ,('4.00000000', '2018-02-12')
      ,('5.10000000', '2018-02-16')
      ,('3.20000000', '2018-03-11')
      ,('4.00000000', '2018-03-15');

-- we need to order the data, if you do not want to use ROW_NUMBER(), use another way to order it (for exmaple record ID of each row using IDENTITY column)
WITH DataSourceOrdered AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY [Date]) AS [RowID]
         ,[Price]
         ,[Date]
    FROM @DataSource
), RecordsWhenPriceChanged AS
(
    -- records when price has changed
    SELECT DS1.[Price]
          ,DS1.[Date]
          ,DS1.[RowID]
    FROM DataSourceOrdered DS1
    INNER JOIN DataSourceOrdered DS2
        ON DS1.[RowID] -1 = DS2.[RowID]
        AND DS1.[Price] <> DS2.[Price]
    UNION ALL
    -- the initial record
    SELECT [Price]
          ,[Date]
          ,[RowID]
    FROM DataSourceOrdered
    WHERE [RowID] = 1
)
-- getting current records
SELECT [Price]
      ,[Date]
      ,[RowID]
FROM RecordsWhenPriceChanged
UNION ALL
-- getting previous records + 1 day for each
SELECT DS1.[Price]
      ,DATEADD(DAY, 1, DS1.[Date])
      ,DS1.[RowID]
FROM DataSourceOrdered DS1
INNER JOIN RecordsWhenPriceChanged DS2
    ON DS1.[RowID] + 1 = DS2.[RowID]
-- getting the last record
UNION ALL
SELECT *
FROM
(
    SELECT TOP 1 [Price]
                ,DATEADD(DAY, 1,[Date]) AS [Date]
                ,[RowID]
    FROM RecordsWhenPriceChanged
    ORDER BY [Date] DESC
) DS
ORDER BY [Date];