Geek Answers Handbook

Select rows where the price has not changed

Suppose you have a type table (I am using SQL Server 2008, the audit log table is not 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 secID and Date). I would like to return the entries when the price changes (searching for something better than using the cursor and iteration):

I 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 this is that I am looking for a date range where the price has remained the same.

+5
source share
3 answers

This is a problem of the islands.

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)
+8
source

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

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

,

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
0

More articles:


All Articles