# 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.

-----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
###### Who is Participating?

Commented:
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

Author 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

Commented:
If you haven't already, try creating an index on the field "stock" in the table "data", this could speed up your query.
0

Author 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

Author Commented:
If you want i can start a new question and ask the same
0

Commented:
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

Author 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.