Need to do these three steps in one

I need to find correlation of two stocks.  I have there price data  in a table in the form.

Date                                  stock                              Last_price
1/1/2005                           nifty                                123.23
1/1/2005                           kmb                                  8.5
1/2/2005                          nifty                                  132.32
1/2/2005                           kmb                                  8.6

I can get what i need from these three steps, But i need to do it in one.
PLease help.  Need it Urgently


-----step one -------------
create view list1
as
select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'nifty index'
and datepart(weekday,date) = 2
union all
select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'kmb in equity'
and datepart(weekday,date) = 2


------step two -----------
create view list2
as
select x.stock "stock", x.date "date",  log(x.Price / y.price) "Price" from list1 x, list1 y
where x.rank = y.rank + 1 and x.stock = y.stock


------ step three ----------
SELECT
    (COUNT(*)*SUM(x.price*y.price)-SUM(x.price)*SUM(y.price))/(
   SQRT(COUNT(*)*SUM(SQUARE(x.price))-SQUARE(SUM(x.price)))*
   SQRT(COUNT(*)*SUM(SQUARE(y.price))-SQUARE(SUM(y.price))))
   correlation
FROM list2 x JOIN list2 y ON x.date=y.date
WHERE x.stock='kmb in equity' AND y.stock='nifty index'
LVL 5
mandeliaAsked:
Who is Participating?
 
OtanaCommented:
This should work, though it may not be the best solution:

SELECT
    (COUNT(*)*SUM(x.price*y.price)-SUM(x.price)*SUM(y.price))/(
   SQRT(COUNT(*)*SUM(SQUARE(x.price))-SQUARE(SUM(x.price)))*
   SQRT(COUNT(*)*SUM(SQUARE(y.price))-SQUARE(SUM(y.price))))
   correlation
FROM (select x.stock "stock", x.date "date",  log(x.Price / y.price) "Price" from (select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'nifty index'
and datepart(weekday,date) = 2
union all
select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'kmb in equity'
and datepart(weekday,date) = 2) x, (select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'nifty index'
and datepart(weekday,date) = 2
union all
select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'kmb in equity'
and datepart(weekday,date) = 2) y
where x.rank = y.rank + 1 and x.stock = y.stock ) x JOIN (select x.stock "stock", x.date "date",  log(x.Price / y.price) "Price" from (select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'nifty index'
and datepart(weekday,date) = 2
union all
select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'kmb in equity'
and datepart(weekday,date) = 2) x, (select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'nifty index'
and datepart(weekday,date) = 2
union all
select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'kmb in equity'
and datepart(weekday,date) = 2) y
where x.rank = y.rank + 1 and x.stock = y.stock ) y ON x.date=y.date
WHERE x.stock='kmb in equity' AND y.stock='nifty index'

0
 
mandeliaAuthor Commented:
It works Fine, But there is a new problem now.
It has become a bit slow.
Can we somehow optimize it. It takes nearly 6-7 secs for four years of data.
Can we somehow optimize and bring it within 3-4 secs. Lesser than that will be a bonus.

But this solution has worked in a great way
0
 
OtanaCommented:
If you haven't already, try creating an index on the field "stock" in the table "data", this could speed up your query.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mandeliaAuthor Commented:
Yes i did that right after my post and it did help.
But as you said and i feel the same way: I think this can be optimized.

To give you a bigger picture, Every second we can save in this query can save me 3 Hrs of time in total
0
 
mandeliaAuthor Commented:
If you want i can start a new question and ask the same
0
 
OtanaCommented:
Opening a new question may invite new people to take a look. Maybe you could explain a bit more in detail what this query is supposed to do, and how the tables are structured, it will help people in understanding the end goal.
0
 
mandeliaAuthor Commented:
OKey i will open up a new question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.