469,106 Members | 2,313 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

SQL Agreate Functions

Hi,

I have a problem I dont really know how to solve except for writing a
function.

I have a table with prices;

SecCode| Price | PriceDate
-------+------------+-----------
A0001 | 13.10 | 2004-10-30
A0001 | 13.03 | 2004-10-29
A0001 | 13.12 | 2004-10-28
A0001 | 12.45 | 2004-10-27
A0001 | 12.65 | 2004-10-26
A0001 | 12.45 | 2004-10-25

A0002 | 10.10 | 2004-10-30
A0002 | 10.45 | 2004-10-27
A0002 | 10.65 | 2004-10-26
A0002 | 10.45 | 2004-10-25
etc.

What I would like to calculate is:

a) the difference of the past 2 days for every security of
available prices

b) a flag indicating, that the price of today-1 is yesterday's
price (true in case A0001, false for A0002)

c) the variance of the past 30 days
Is it possible to do that within one query?

Thanks for any advise
Alex

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
2 2322
Alex P wrote:
Hi,

I have a problem I dont really know how to solve except for writing a
function. [snip] What I would like to calculate is:

a) the difference of the past 2 days for every security of
available prices
Find the maximum date for a give SecCode (simple enough) and then the
maximum date that is smaller than the one you just found (assuming no
repetition of dates for a given SecCode).
b) a flag indicating, that the price of today-1 is yesterday's
price (true in case A0001, false for A0002)
SELECT ... (PriceDate = (CURRENT_DATE - 1)) AS is_yesterday, ...
c) the variance of the past 30 days
Variance aggregate function
Is it possible to do that within one query?


Three sub-queries and some joining, certainly. It'll be a big query
mind, perhaps worth wrapping in a function.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
Alex P wrote:
Hi,

I have a problem I dont really know how to solve except for writing a
function. [snip] What I would like to calculate is:

a) the difference of the past 2 days for every security of
available prices
Find the maximum date for a give SecCode (simple enough) and then the
maximum date that is smaller than the one you just found (assuming no
repetition of dates for a given SecCode).
b) a flag indicating, that the price of today-1 is yesterday's
price (true in case A0001, false for A0002)
SELECT ... (PriceDate = (CURRENT_DATE - 1)) AS is_yesterday, ...
c) the variance of the past 30 days
Variance aggregate function
Is it possible to do that within one query?


Three sub-queries and some joining, certainly. It'll be a big query
mind, perhaps worth wrapping in a function.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by hokiegal99 | last post: by
99 posts views Thread by David MacQuigg | last post: by
47 posts views Thread by Richard Hayden | last post: by
21 posts views Thread by Rubén Campos | last post: by
25 posts views Thread by Stijn Oude Brunink | last post: by
2 posts views Thread by Bryan Olson | last post: by
23 posts views Thread by Timothy Madden | last post: by
7 posts views Thread by Immortal Nephi | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.