select record when based on previous records

[didamus]

i need to do the next thing:

For every record id need to return the FIRST date in which the record value has been increased/decreased by over 10% from it's last value (to that id).
If no increase/decrease by over 10% to that record id has happened i should ignore that record id.

for example for the next table

id record_date record_value
1 01/03/2008 100
1 23/04/2008 105
1 25/04/2008 130
2 10/04/2008 250
1 30/05/2008 200
2 30/05/2008 100
3 01/04/2008 150

i should return:
id record_date record value
1 25/04/2008 130
2 30/05/2008 100

this requires me to somehow use sub queries in away im not yet experienced with.

any help will be appreciated

 

[Gixonita]

Here is an idea of how to do it, every query could be run as a sub query of the next one, but I kind of wanted you to see what is going on on the process.

Hope this helps

SELECT id                    AS Last_ID,
       MAX (record_date)     AS Last_Date

INTO #TEMPO

FROM   Your_Table

GROUP BY id

----------------------------------------------

SELECT id                  AS Pre_Last_ID,
       Last_Date,
       MAX (record_date)   AS Pre_Last_Date

INTO #TEMPO2

FROM   Your_Table

INNER JOIN #TEMPO
  ON  Last_ID = id AND
      Last_Date > record_date 

GROUP BY id 

----------------------------------------------

SELECT id,
       record_date,
       record_value

FROM   #TEMPO2

INNER JOIN Your_Table AS A
  ON  A.id = Pre_Last_ID AND
      A.record_Date = Last_Date 

INNER JOIN Your_Table AS B
  ON  B.id = Pre_Last_ID AND
      B.record_Date = Pre_Last_Date

WHERE A.record_value > (B.record_value * 1.10) OR
      A.record_value < (B.record_value * .90)

 

[RyanEK]

Hi,

This is my approach.

The first sql will give you all records with a -+ difference.

select *
into #temp
from (
	select *,
	(select top 1 record_value from [table] t2 
		where t2.id = t1.id and t2.record_date < t1.record_date 
		order by t2.record_date desc
	) as prev_value
	from [table] t1 
) as t3
where (record_value > (prev_value * 1.1)) or 
	  (record_value < (prev_value * 0.9))

The next will give you the minimum record for each id

select id, record_date, record_value 
from #temp t1
where record_date = (
	select top 1 record_date id from #temp t2 
	where t2.id = t1.id order by record_date asc )

Ryan

 

[haroldholt]

...and if you have SQL 2005 then you can use a CTE (untested code)...

;with recordCTE (id, record_date, record_value, row_num)
as
(
select id,
record_date,
record_value, row_number() over (partition by id order by record_date)
from mytable
)
select id, record_date, record_value
from
(
select
next_row.id,
next_row.record_date,
next_row.record_value,
row_number() over (partition by current_row.id order by current_row.record_date) as row_num
from recordCTE current_row
join recordCTE next_row
ON current_row.id = next_row.id
AND current_row.row_num+1 = next_row.row_num
where abs(next_row.record_value - current_row.record_value)>(current_row.record_value * 0.1)
) a
where a.row_num = 1