Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

select record when based on previous records

Status
Not open for further replies.

didamus

Programmer
Aug 27, 2008
1
IL
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
 
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

Code:
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)
 
Hi,

This is my approach.

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

Code:
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

Code:
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
 
...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

:)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top