Constructing SELECT query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Constructing SELECT query

Author

Topic

slkhlaw
Starting Member

9 Posts

Posted - 2009-06-25 : 08:13:50

I have difficulty in construction my SELECT Query.
Could the gurus please help.

Basically, I have two tables.
Table Stock
Stockcode
Stockname
Industry
MarketCap

Table Vol
Date
Stockcode
Volume

I need SQL to display me the stockcode, stockname, volume and average volume (over a period of 1 year) starting from the most abnormal volume to the least abnormal volume for the day.

How do I construct such query?

Second question which I am going to build on the similar table.
I am going to alter my table to include

Table Stock
Stockcode
Stockname
Industry
MarketCap

Table Vol
Date
Stockcode
Volume
Average Volume

And I am going to write a script to be executed every night so that the average volume column can be computed.

How do I write an INSERT SELECT query to populate my Vol table?

Thanks once again.

ancarlosmendes
Starting Member

1 Post

Posted - 2009-06-25 : 09:04:44

Hi,

I'll prefer to write your query on this way.
But, if you want to write the value of Avg(vl.volume), you must write a query update in the item 2 as a job.

1)

Select s.Stockcode
, s.Stockname
, v.volume
, (Select Avg(vl.volume) From Vol vl Where v.Stockcode = vl.Stockcode and vl.Date Between '2009-01-01' And '2009-12-31') As AvgVol
From Stock s
, Vol v
Where s.Stockcode = v.Stockcode

2)

update Vol
set Vol.AverageVolume = vl.AvgVol
From (Select StockCode, Avg(volume) As AvgVol
From Vol
Where Date Between '2009-01-01' And '2009-12-31'
Group By StockCode) as vl
Where Vol.StockCode = vl.StockCode

Best Regards,

Antônio Mendes
DBA SQL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 09:13:23

~~vl.Date Between '2009-01-01' And '2009-12-31'~~

vl.Date >= '20090101' And vl.Date < '20100101'
Date >= '20090101' And Date < '20100101'

E 12°55'05.63"
N 56°04'39.26"

slkhlaw
Starting Member

9 Posts

Posted - 2009-06-25 : 10:05:39

Superb. I tried the first query. It works brilliantly. I edited the query a bit to achieve my sort order. It works marvelously. Thanks

slkhlaw
Starting Member

9 Posts

Posted - 2009-06-25 : 13:11:17

Hello Antonio.

Forgive my newbie knowledge on SQL.

Your query string seems to work magical. However, I would really like to learn on the bit you wrote about v.Stockcode = vl.Stockcode. What do they actually mean? v is referring to vol table, vl is also referring to vol table. What is the different?

I tried to construct another sql query based on what I have learnt from you. This is what I get.

SELECT S.STOCKCODE, S.SHORTNAME, H.CLOSE, PE.EPS, (SELECT (HI.CLOSE/PE.EPS) FROM HISTORICAL AS HI, PE WHERE HI.STOCKCODE=H.STOCKCODE AND HI.STOCKCODE=PE.STOCKCODE) AS PE FROM STOCK AS S, HISTORICAL AS H, PE WHERE PE.STOCKCODE=S.STOCKCODE AND H.STOCKCODE=PE.STOCKCODE ORDER BY PE DESC

I didn't get too far and had trouble actually understanding what i was doing.

STOCK TABLE HAS
STOCKCODE
SHORTNAME (Don't change too often)

HISTORICAL TABLE HAS
DATE
STOCKCODE
OPEN
CLOSE (Updated daily)

PE TABLE HAS
DATE
STOCKCODE
EPS (Updated every quarter)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 13:28:01

quote:
Originally posted by slkhlaw

Hello Antonio.

Forgive my newbie knowledge on SQL.

Your query string seems to work magical. However, I would really like to learn on the bit you wrote about v.Stockcode = vl.Stockcode. What do they actually mean? v is referring to vol table, vl is also referring to vol table. What is the different?

v and vl are table aliases, short names given for tables. you dont have to repeat table names everywhere, just give aliases to refer to table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 13:30:30

quote:
Originally posted by slkhlaw

Hello Antonio.

Forgive my newbie knowledge on SQL.

Your query string seems to work magical. However, I would really like to learn on the bit you wrote about v.Stockcode = vl.Stockcode. What do they actually mean? v is referring to vol table, vl is also referring to vol table. What is the different?

I tried to construct another sql query based on what I have learnt from you. This is what I get.

SELECT S.STOCKCODE, S.SHORTNAME, H.CLOSE, PE.EPS, (SELECT (HI.CLOSE/PE.EPS) FROM HISTORICAL AS HI, PE WHERE HI.STOCKCODE=H.STOCKCODE AND HI.STOCKCODE=PE.STOCKCODE) AS PE FROM STOCK AS S, HISTORICAL AS H, PE WHERE PE.STOCKCODE=S.STOCKCODE AND H.STOCKCODE=PE.STOCKCODE ORDER BY PE DESC

I didn't get too far and had trouble actually understanding what i was doing.

STOCK TABLE HAS
STOCKCODE
SHORTNAME (Don't change too often)

HISTORICAL TABLE HAS
DATE
STOCKCODE
OPEN
CLOSE (Updated daily)

PE TABLE HAS
DATE
STOCKCODE
EPS (Updated every quarter)

what does CLOSE and EPS contain? count values?

slkhlaw
Starting Member

9 Posts

Posted - 2009-06-25 : 13:41:56

Close = float
EPS = float
Close = PE x EPS
EPS updated quarterly
Close updated daily
PE to be based on the latest EPS and Close

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 13:58:41

[code]SELECT S.STOCKCODE, S.SHORTNAME, H.CLOSE, PE.EPS,(HI.CLOSE/PE.EPS) AS PE
FROM STOCK AS S
INNER JOIN PE
ON PE.STOCKCODE=S.STOCKCODE
INNER JOIN HISTORICAL AS H
ON H.STOCKCODE=PE.STOCKCODE
ORDER BY PE DESC
[/code]

slkhlaw
Starting Member

9 Posts

Posted - 2009-06-25 : 14:14:28

I will try that when I get home. Does H.close/pe.eps knows to pick max(date) from both h and pe table? The two max(date) may not be the same.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 14:19:40

what? you told they both are floats..are they storing date values?

slkhlaw
Starting Member

9 Posts

Posted - 2009-06-25 : 15:10:40

I think there might have been some misunderstanding. Let me show you how what I mean

Stock data
Stockcode: RBS
Shortname: Royal Bank of Scotland

Stockcode: Lloyd
Shortname: Lloyd Banking Group

Historical data
RBS on 01/01/2009 close at 0.80
RBS on 02/01/2009 close at 0.78
RBS on 03/01/2009 close at 0.82
RBS on 04/01/2009 close at 0.85
...
RBS on 25/06/2009 close at 0.40
LLOY on 01/01/2009 close at 1.80
LLOY on 02/01/2009 close at 1.78
LLOY on 03/01/2009 close at 1.82
LLOY on 04/01/2009 close at 1.70
...
LLOY on 25/06/2009 close at 0.80

PE data
RBS announced EPS at the close of the financial year ending 31/12/2008 of 0.04
LLOY announced EPS at the close of the 1st quarter ending 31/03/2009 of 0.10

Presented within my table comprise Stockcode, Shortname, Close, EPS, (Hi.Close/PE.EPS) AS PE
RBS, Royal Bank of Scotland, 0.40, 0.04 , 10 (Calculated based on Close at 25/06/2009 at 0.40 divided by 0.04 announced on 31/12/2008)
LLOY, Lloyd Banking Group, 0.80, 0.10, 8 (Calculated based on Close at 25/06/2009 at 0.80 divided by 0.10 announced on 31/03/2008)
...
...

I hope you get the picture now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 13:54:20

i cant see data for 31/12/2008. so you always want fraction of last value in current to last value in previous year?

slkhlaw
Starting Member

9 Posts

Posted - 2009-06-26 : 19:40:26

I think I managed to figure it out.

SELECT S.SHORTNAME, S.LONGNAME, H.CLOSE, PE.EPS, (H.CLOSE/PE.EPS) AS PE FROM STOCK AS S INNER JOIN HISTORICAL AS H ON S.STOCKCODE=H.STOCKCODE INNER JOIN PE ON S.STOCKCODE=PE.STOCKCODE WHERE H.DATE=(SELECT MAX(DATE) FROM HISTORICAL WHERE S.STOCKCODE=H.STOCKCODE) AND PE.DATE=(SELECT MAX(DATE) FROM PE WHERE S.STOCKCODE=PE.STOCKCODE) ORDER BY PE ASC LIMIT 0,30;

I am sorry that I needed to use this platform to ask another noobies question.
Suppose nothing have changed. I have a historical data contain (stockcode, date, close)

RBS, 01/01/2009, 0.80
RBS, 02/01/2009, 0.78
RBS, 03/01/2009, 0.82
RBS, 04/01/2009, 0.85
...
RBS, 25/06/2009, 0.40
RBS, 26/06/2009, 0.42
LLOY, 01/01/2009, 1.80
LLOY, 02/01/2009, 1.78
LLOY, 03/01/2009, 1.82
LLOY, 04/01/2009, 1.70
...
LLOY, 15/06/2009, 0.80
LLOY, 16/06/2009, 0.82

I need to display date, stockcode, latest close, second latest close, swing defined as (absolute(latest close - second latest close)/(latest close)).
Based on this example, it should display

Date, Stockcode, Latest Close, Second Latest Close, Swing
26/06/2009, RBS, 0.42, 0.40, 0.0476
16/06/2009, LLOY, 0.82, 0.80, 0.0243

How do I achieve this using SQL?
Thanks again. You guys are real experts.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-27 : 01:21:31

quote:
Originally posted by slkhlaw

I think I managed to figure it out.

SELECT S.SHORTNAME, S.LONGNAME, H.CLOSE, PE.EPS, (H.CLOSE/PE.EPS) AS PE FROM STOCK AS S INNER JOIN HISTORICAL AS H ON S.STOCKCODE=H.STOCKCODE INNER JOIN PE ON S.STOCKCODE=PE.STOCKCODE WHERE H.DATE=(SELECT MAX(DATE) FROM HISTORICAL WHERE S.STOCKCODE=H.STOCKCODE) AND PE.DATE=(SELECT MAX(DATE) FROM PE WHERE S.STOCKCODE=PE.STOCKCODE) ORDER BY PE ASC LIMIT 0,30;

I am sorry that I needed to use this platform to ask another noobies question.
Suppose nothing have changed. I have a historical data contain (stockcode, date, close)

RBS, 01/01/2009, 0.80
RBS, 02/01/2009, 0.78
RBS, 03/01/2009, 0.82
RBS, 04/01/2009, 0.85
...
RBS, 25/06/2009, 0.40
RBS, 26/06/2009, 0.42
LLOY, 01/01/2009, 1.80
LLOY, 02/01/2009, 1.78
LLOY, 03/01/2009, 1.82
LLOY, 04/01/2009, 1.70
...
LLOY, 15/06/2009, 0.80
LLOY, 16/06/2009, 0.82

I need to display date, stockcode, latest close, second latest close, swing defined as (absolute(latest close - second latest close)/(latest close)).
Based on this example, it should display

Date, Stockcode, Latest Close, Second Latest Close, Swing
26/06/2009, RBS, 0.42, 0.40, 0.0476
16/06/2009, LLOY, 0.82, 0.80, 0.0243

How do I achieve this using SQL?
Thanks again. You guys are real experts.

Are you using sql server? i dont think LIMIT is sql server function

slkhlaw
Starting Member

9 Posts

Posted - 2009-06-27 : 05:22:23

i think there might be some dialect difference between sql. limit is the equivalent of top

slkhlaw
Starting Member

9 Posts

Posted - 2009-06-27 : 16:14:14

I got as far as displaying stockcode, latest close, second latest close, but I am completely lost at calculating the swing. Can anyone help?

This is as far as I have got

SELECT S.STOCKCODE, S.SHORTNAME, S.LONGNAME, H.CLOSE,
(SELECT HIG.CLOSE FROM HISTORICAL AS HIG WHERE HIG.STOCKCODE=H.STOCKCODE ORDER BY HIG.DATE LIMIT 2,1) AS LCLOSE
FROM STOCK AS S, HISTORICAL AS H WHERE S.STOCKCODE=H.STOCKCODE AND H.DATE=
(SELECT MAX(HI.DATE) FROM HISTORICAL AS HI WHERE H.STOCKCODE=HI.STOCKCODE);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 00:44:46

quote:
Originally posted by slkhlaw

i think there might be some dialect difference between sql. limit is the equivalent of top

Please understand that this is ms sql server forum. so solutions we give are sql server specific and may not work on other rdbms. thats why we keep on asking what rdbms you're using

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources