Select rows where price didn't change

Question

Suppose you have a table like (am using SQL Server 2008, no audit log - table is HUGE):

 SecID | Date    | Price
 1       1/1/11      10
 1       1/2/11      10
 1       1/3/11      5
 1       1/4/11      10
 1       1/5/11      10

Suppose this table is HUGE (millions of rows for different secIDs and Date) - I would like to return the records when the price changed (looking for something better than using a cursor and iterating):

Am trying to figure out how to get:

 SecID | StartDate | EndDate | Price
 1        1/1/11      1/2/11    10
 1        1/3/11      1/3/11    5
 1        1/4/11      1/5/11    10

i.e. another way to look at it is that I am looking for a range of dates where the price has stayed the same.

Do you have this information stored in another table (i.e. an audit log)? — Aaron Hathaway, Jan 19 '11 at 17:35
Not clear on what you want — is it a set of records specifying the range of dates for which a price stayed the same? — Larry Lustig, Jan 19 '11 at 17:43
What if the price has changed and then was changed back to its original value in your date range? Do you want to include it? — Matthew, Jan 19 '11 at 18:16
@Matthew - Yes. (See example results where 10 is shown twice) — Martin Smith, Jan 19 '11 at 18:21

Martin Smith · Accepted Answer · 2011-01-19T18:12:31.127

8

This is an "islands" problem.

declare @Yourtable table
 (SecID int, Date Date, Price int)

 INSERT INTO @Yourtable
SELECT 1,GETDATE()-5,10 union all
SELECT 1,GETDATE()-4,10 union all
SELECT 1,GETDATE()-3,5 union all
SELECT 1,GETDATE()-2,10 union all
SELECT 1,GETDATE()-1, 10

;WITH cte AS
(
SELECT SecID,Date,Price,
       ROW_NUMBER() OVER (PARTITION BY SecID ORDER BY Date) -
       ROW_NUMBER() OVER (PARTITION BY Price, SecID ORDER BY Date) AS Grp
FROM @Yourtable
)
SELECT SecID,Price, MIN(Date) StartDate, MAX(Date) EndDate
FROM cte
GROUP BY SecID, Grp, Price
ORDER BY SecID,  MIN(Date)

edited Jan 19 '11 at 18:12

answered Jan 19 '11 at 18:06

Martin Smith

438,706
87
741
845

Actually, this answer is better than mine :) – Ken Downs Jan 19 '11 at 18:08
BTW, what is an "islands" problem? – Denis Jan 19 '11 at 18:17
1

@Denis - Finding contiguous "islands" of data as in your question. The solution in my answer is based on an approach I originally read in one of Itzik Ben Gan's books. – Martin Smith Jan 19 '11 at 18:20

score 3 · Answer 2 · answered Jan 19 '11 at 18:06

3

If the value does not change, the std deviation will be zero

select secId
  from ...
 group by secId
having count(*) = 1  
    OR stdev(price) = 0

answered Jan 19 '11 at 18:06

Ken Downs

4,707
1
22
20

@Matthew, besides having to read every row, I cannot imagine it adds much, as the disk reads will swamp any in-memory operations going on. – Ken Downs Jan 19 '11 at 18:28

score 0 · Answer 3 · answered Jan 19 '11 at 17:39

0

I think this should work

SELECT SecID, Min(Date) AS StartDate, Max(Date) AS EndDate, Price FROM BigTable GROUP BY SecID, EndDate Having Min(Date) != MAx(Date) And Date != NULL

answered Jan 19 '11 at 17:39

Kell

3,252
20
19

This won't work because in row 2 of my example Min(Date) = Max(Date) and the last row will be dropped (since price = 10 is already found in the first row). Good try though - was thinking about this one for awhile. (BTW, Date is marked as "NOT NULL") – Denis Jan 19 '11 at 17:42
you should also be grouping by Price, not EndDate. In any case, if you replace that, and take out the `HAVING`, it should work fine – Lamak Jan 19 '11 at 17:45
1

This won't work either because if you group by SecID, Price then you will have (1, 10) and (1, 5) so 2 rows only in your result set. You should have 3: (1, 10), (1, 5) and (1,10) again. – Denis Jan 19 '11 at 17:48
yeah, sorry, it's been a while :) – Kell Jan 20 '11 at 09:40

Select rows where price didn't change

3 Answers3

Linked