Date Range for set of same data

Question

I am trying to build a SQL query which will give me the date range for the dates with same prices. If there is a break in the prices, I expect to see it in a new line. Even if sometime during the month there are same prices, if there is change in the prices sometime in between I want to see it as two separate rows with the specific date range.

Sample Data:

Date    Price
1-Jan   3.2
2-Jan   3.2
3-Jan   3.2
4-Jan   3.2
5-Jan   3.2
6-Jan   3.2
7-Jan   3.2
8-Jan   3.2
9-Jan   3.5
10-Jan  3.5
11-Jan  3.5
12-Jan  3.5
13-Jan  3.5
14-Jan  4.2
15-Jan  4.2
16-Jan  4.2
17-Jan  3.2
18-Jan  3.2
19-Jan  3.2
20-Jan  3.2
21-Jan  3.2
22-Jan  3
23-Jan  3
24-Jan  3
25-Jan  3
26-Jan  3
27-Jan  3
28-Jan  3
29-Jan  3.5
30-Jan  3.5
31-Jan  3.5

Desired Result :

Price   Date Range
3.2     1-8
3.5     9-13
4.2     14-16
3.2     17-22
3       22-28
3.5     29-31

Answer 1

Non-relational Solution

I don't think any of other answers are correct.

• GROUP BY won't work

• Using ROW_NUMBER() forces the data into a Record Filing System structure, which is physical, and then processes it as physical records. At a massive performance cost. Of course, in order to write such code, it forces you to think in terms of RFS instead of thinking in Relational terms.

• Using CTEs is the same. Iterating through the data, especially data that does not change. At a slightly different massive cost.

• Cursors are definitely the wrong thing for a different set of reasons. (a) Cursors require code, and you have requested a View (b) Cursors abandon the set-processing engine, and revert to row-by-row processing. Again, not required. If a developer on any of my teams uses cursors or temp tables on a Relational Database (ie. not a Record Filing System), I shoot them.

Relational Solution

1. Your data is Relational, logical, the two given data columns are all that is necessary.

2. Sure, we have to form a View (derived Relation), to obtain the desired report, but that consists of pure SELECTs, which is quite different to processing (converting it to a file, which is physical, and then processing the file; or temp tables; or worktables; or CTEs; or ROW_Number(); etc).

3. Contrary to the lamentations of "theoreticians", who have an agenda, SQL handles Relational data perfectly well. And you data is Relational.

Therefore, maintain a Relational mindset, a Relational view of the data, and a set-processing mentality. Every report requirement over a Relational Database can be fulfilled using a single SELECT. There is no need to regress to pre-1970 ISAM File handling methods.

I will assume the Primary Key (the set of columns that give a Relational row uniqueness) is Date, and based on the example data given, the Datatype is DATE.

Try this:

    CREATE VIEW MyTable_Base_V          -- Foundation View
    AS
        SELECT  Date,
                Date_Next,
                Price
            FROM (
            -- Derived Table: project rows with what we need
            SELECT  Date,
                    [Date_Next] = DATEADD( DD, 1, O.Date ),
                    Price,
                    [Price_Next] = (

                SELECT Price            -- NULL if not exists
                    FROM MyTable
                    WHERE Date = DATEADD( DD, 1, O.Date )
                    )

                FROM MyTable MT

                ) AS X
            WHERE Price != Price_Next   -- exclude unchanging rows
    GO

    CREATE VIEW MyTable_V               -- Requested View
    AS
        SELECT  [Date_From] = (
            --  Date of the previous row
            SELECT MAX( Date_Next )     -- previous row
                FROM MyTable_V
                WHERE Date_Next < MT.Date
                ),

                [Date_To] = Date,       -- this row
                Price
            FROM MyTable_Base_V MT
    GO

    SELECT  *
        FROM MyTable_V
    GO

Method, Generic

Of course this is a method, therefore it is generic, it can be used to determine the From_ and To_ of any data range (here, a Date range), based on any data change (here, a change in Price).

Here, your Dates are consecutive, so the determination of Date_Next is simple: increment the Date by 1 day. If the PK is increasing but not consecutive (eg. DateTime or TimeStamp or some other Key), change the Derived Table X to:

    -- Derived Table: project rows with what we need
    SELECT  DateTime,
            [DateTime_Next] = (
            -- first row > this row
        SELECT  TOP 1
                DateTime                -- NULL if not exists
            FROM MyTable
            WHERE DateTime > MT.DateTime
            ),

            Price,
            [Price_Next] = (
            -- first row > this row
        SELECT  TOP 1
                Price                   -- NULL if not exists
            FROM MyTable
            WHERE DateTime > MT.DateTime
            )

        FROM MyTable MT

Enjoy.

Please feel free to comment, ask questions, etc.

Answer 2

You can do this by adding a grouping column. A neat trick for this is the difference of two sequences of numbers -- when the difference is constant, then the price is the same.

select price, min(date), max(date)
from (select s.*,
             (row_number() over (order by date) - 
              row_number() over (partition by price order by date)
             ) as grp
      from sample s
     ) grp
group by grp, price;

Note: be careful that price is stored as a fixed decimal rather than a floating decimal. Otherwise, values that look the same might not actually be the same.

Answer 3

This is what you are looking for

declare @temptbl table (price decimal(18,2), mindate date, maxdate date)

declare @price as decimal(18,2), @date as date

declare tempcur cursor for 
select price, date
from YourTable

open tempcur

fetch next from tempcur
into @price, @date

while (@@fetch_status = 0)
begin
    if (isnull((select price from @temptbl where maxdate = (select max(maxdate)from @temptbl)),0) <> @price)
        insert into @temptbl (price,mindate,maxdate) values (@price,@date,@date)
    else
        update @temptbl
        set maxdate = @date
        where maxdate = (select max(maxdate)from @temptbl)


    fetch next from tempcur
    into @price, @date
end

deallocate tempcur

select price, convert(nvarchar(50), mindate) + ' to ' + convert(nvarchar(50), maxdate) as [date range] from @temptbl

Answer 4

Use CTE, below is working code.

WITH grouped AS (
SELECT 
Pricedate, price,
grp1= ROW_NUMBER() OVER (ORDER BY Pricedate) -
ROW_NUMBER() OVER (Partition by price ORDER BY Pricedate)
FROM yourTablewithDateAndPrice 
)
SELECT
  DtFrom = MIN(Pricedate),
  DtTo = MAX(Pricedate),
  Price = price

FROM grouped
GROUP BY Price,grp1 
order by DtFrom;

The internal query will created same group till the time it find same price, else group will be incremented by one. in Final group by you will have required result.