ms_sql_server_query

  • Thread starter Thread starter khin_kin
  • Start date Start date
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
 
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
 
Thanks to all the reply

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


Regards

Charl
 
Thanks to all the reply

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


Regards

Charl
 
Thanks to all the reply

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


Regards

Charl
 
Back
Top