need urgent help for a query
 

i have two tables
table1 has the folowing fields
lastprice
symbol
time

table2 has the same fields

now table1 stores only the last data of the corrosponding symbol
but table2 goeson adding the data as it comes in
now i want to write a query get the difference of the lastprice of table1 and the lastprice of table2 which was recorder 5 minutes back something like
table1.lastprice,table2.lastprice where
table2.time<(table2.time-5 min)
now the problem is that i have to get only the first data of table2 for the correspondig symbol for that time.
pls help

Re: need urgent help for a query
 

SELECT symbol, lastprice, time
FROM table1 INNER JOIN table2
ON table1.symbol = table2.symbol
WHERE table2.time < (table2.time - 5min)

Would probably give you the result.

Hope this helps

:cool:

Re: need urgent help for a query
 

This query depends upon the format that the time is stored in. MS SQL doesn't have a TIME data type.The DATETIME format can store both dates and times (obviously). The next point is that you cannot just subtract one DATETIME from another. You have to use DATEDIFF. So, asuming that the time column is in fact a DATETIME datatype the requested quiery should look something like this.

select t1.lastprice - t2.lastprice as price_difference
from table1 t1, table2 t2
where t2.symbol = t1.symbol
and t2.time = (select top 1 t3.time
from table2 t3
where t3.symbol = t1.symbol order by time desc)

This query expects that the relevant record in table2 was the last one inserted (the current record hasn't been transfered from table1 to table2 yet.)