Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
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
0 OriginalGriff 414
1 Maciej Los 180
2 Richard MacCutchan 140
3 DamithSL 129
4 Kornfeld Eliyahu Peter 119


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