ms_sql_server_query

K

khin_kin

Dear all,

I have 3 column: date, time, stocks name, price

I have 2 questions:

1. what is the command (or query languange) to get the

equivalent results of the first and/or last button
in
aggregate query, which is available in Ms acces?
e.g. I want to get the first and last price of the
day for any particular stocks

2. how to calculate return with the following formula:
return=log P(t)-log P(t-1), where P(t) is price at
time t say 10 am and P(t-1) is price at one period
previous t say 9 am?


Regards


Charl
 
M

Michel Walsh

HI,


Q1.

SELECT a.StockID,
LAST(a.DateTime), LAST(a.price),
LAST(b.DateTime), LAST(b.price)

FROM (( SELECT * FROM myTable WHERE dateValue(DateTime)=Date( ) ) As a
INNER JOIN ( SELECT * FROM myTable WHERE
dateValue(DateTime)=Date( ) ) As b
ON a.StockID = b.StockID )
INNER JOIN ( SELECT * FROM myTable WHERE
dateValue(DateTime)=Date( ) ) As c
ON a.StockID=c.StockID

GROUP BY a.StockID, a.DateTime, b.DateTime

HAVING a.DateTime= MIN(c.DateTime) AND b.DateTime=MAX(c.DateTime)



should do ( I just typed it without any intensive check). I assume your date
time field is just ONE field, not two.


Q2.


SELECT a.StockID, log(LAST(a.Price)) - log(LAST(b.Price))
FROM (myTable As a INNER JOIN myTable As b
ON a.StockID=b.StockID AND a.DateTime>b.DateTime)
INNER JOIN myTable As c ON a.StockID=c.StockID AND
a.DateTime>c.DateTime


GROUP BY a.StockID, b.dateTime

HAVING b.DateTime = MAX(c.DateTime)


should also do.


Hoping it may help,
Vanderghast, Access MVP
 
K

khin_kin

Thanks to all the reply

Do you know how to transalate the same query for Microsoft SQL server'
query?


Regards

Charl
 
K

khin_kin

Thanks to all the reply

Do you know how to transalate the same query for Microsoft SQL server'
query?


Regards

Charl
 
K

khin_kin

Thanks to all the reply

Do you know how to transalate the same query for Microsoft SQL server'
query?


Regards

Charl
 
Top