Click here to Skip to main content
12,299,109 members (50,492 online)
Rate this:
 
Please Sign up or sign in to vote.
Dear Coders,

I have a following query:

I want to take Stock price avg for last 3 days,10 days etc.There are thousand of stocks.

The dates are not in order i.e. Monday to Friday only as market not open on saturday & Sunday but sometime there is a holiday in between market days i.e. can say Wednesday is a holiday.Sometime market open on Saturday also.


This is the data for dates:

Tradedate:
2012-12-17
2012-12-14
2012-12-13
2012-12-12
2012-12-11
2012-12-10
2012-12-07
2012-12-06
2012-12-05
2012-12-04
2012-12-03
2012-11-30
2012-11-29
2012-11-27
2012-11-26
2012-11-23
2012-11-22
2012-11-21
2012-11-20
2012-11-19
2012-11-16

These are the trade date & each tradedate has thousands of stock names along with their price & traded volumes.

I want to take avg volumes of last three trading days but something is missing when i tried that so requesting all of you to help me.

here i want the avg volumes for last 3 trade date.The dates mentioned here are in descending order.

Hope i will get some query to do this.

Thanks & Regards.
Posted 17-Dec-12 3:51am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

HI,

For getting the last N number of trading date you can view the following link.

sql query to get the last N trading days

This may show you the right way.

Thanks
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Here is the sql for it.

select pricesymbol,avg(volume) as averagevolume,avg(price) as averageprice from YOURTABLE group by pricesymbol;
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Dear All,

Thanks For the response especially from Armando Talex. I tested the solution suggested by him; it's working fine.

But i found my own solution to solve this as follows:

--First drop the table.As i have created a procedure to do this calculation i am droping the table which is crated for temp purpose

drop table #date

--The below query will give me top 8 trading dates which are in the database & these dates will get store in a temp table #date

select top 8 tradedate into #date from EODNSE group by TradeDate order by TradeDate desc

--The below query will give me the enddate i.e. 8th trade date
select top 1 tradedate from #date order by tradedate

Following is the full queries to do this:

drop table #date
select top 8 tradedate into #date from EODNSE group by TradeDate order by TradeDate desc
declare @StartDate date --@Startdate is the current date of today
declare @Enddate date --@Enddate is the current date less 2 day's back date
set @StartDate = (select top 1 TradeDate from EODNSE order by TradeDate desc) --This will get the current date
set @Enddate = (select top 1 tradedate from #date order by tradedate)--This will take the last date from #date temp table
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 17 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100