[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help with a SQL Statement

Posted on 2012-04-06
9
Medium Priority
?
357 Views
Last Modified: 2012-06-22
I need to display an average in one collumn.  The SQL executes, but the average collumn is null.

SELECT Top(12) SYM.SymbolID,SYM.SymbolName,
      100 * (MAX(HIST.ClosePrice) -  MIN(HIST.ClosePrice)) / (SELECT ClosePrice
      FROM StockHist
      WHERE  
    QuoteDate = '2012-02-25'  
      AND SymbolID=SYM.SymbolID ) as PctChange -- Problem is here
      , MAX(HIST.ClosePrice) -  MIN(HIST.ClosePrice) AS Change
      , AVG(HIST.ClosePrice) AS AvgClose  

      FROM StockHist AS HIST INNER JOIN
    Symbol AS SYM ON HIST.SymbolID = SYM.SymbolID
      WHERE
      SYM.MarketID = 1
      AND HIST.ClosePrice > HIST.ClosePricePrev
      AND HIST.ClosePrice >=5  AND HIST.ClosePrice <=45
      AND HIST.QuoteDate >= '2012-02-25'
      AND HIST.QuoteDate <= '2012-4-05'
      GROUP BY SYM.SymbolID,SYM.SymbolName,SYM.SecName  
      Having count(*) > 1
      ORDER BY (AvgClose) DESC


SymbolID  SymbolName       PctChangeChange      Avg Close
5943      DORM            NULL      0.21      44.825000
6132      FCHI            NULL      0.38      44.793333
7524      ROLL            NULL      0.44      44.720000
6598      ITIC            NULL      1.39      44.663333
5848      CVCO            NULL      0.37      44.635000
5792      CRMT            NULL      0.84      44.484000

PctChange should be Change/Avg Close ie. (0.21/44.825) * 100
What should I change in the SQL Statement?
Thanks,
0
Comment
Question by:Dovberman
9 Comments
 
LVL 10

Assisted Solution

by:plummet
plummet earned 400 total points
ID: 37816993
Hi,

What values (if any) are returned if you display instead of using the following in the calculation?

SELECT ClosePrice
      FROM StockHist
      WHERE  
    QuoteDate = '2012-02-25'  
      AND SymbolID=SYM.SymbolID

ie can you execute something like this to show all the values returned:

SELECT Top(12) 
	SYM.SymbolID,
	SYM.SymbolName,
	MAX(HIST.ClosePrice) as MAXClose,
	MIN(HIST.ClosePrice) as MINClose,
	(SELECT ClosePrice 
		FROM StockHist 
		WHERE  QuoteDate = '2012-02-25'  
		AND SymbolID=SYM.SymbolID ) as ShowClosePrice,
	100 * (MAX(HIST.ClosePrice) -  MIN(HIST.ClosePrice)) / (SELECT ClosePrice 
      			FROM StockHist 
      			WHERE  
    			QuoteDate = '2012-02-25'  
    			AND SymbolID=SYM.SymbolID ) as PctChange -- Problem is here
      , MAX(HIST.ClosePrice) -  MIN(HIST.ClosePrice) AS Change 
      , AVG(HIST.ClosePrice) AS AvgClose   

      FROM StockHist AS HIST INNER JOIN
    Symbol AS SYM ON HIST.SymbolID = SYM.SymbolID 
      WHERE 
      SYM.MarketID = 1 
      AND HIST.ClosePrice > HIST.ClosePricePrev 
      AND HIST.ClosePrice >=5  AND HIST.ClosePrice <=45
      AND HIST.QuoteDate >= '2012-02-25' 
      AND HIST.QuoteDate <= '2012-4-05' 
      GROUP BY SYM.SymbolID,SYM.SymbolName,SYM.SecName  
      Having count(*) > 1 
      ORDER BY (AvgClose) DESC 

Open in new window

0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 400 total points
ID: 37817034
have you tried :

 , AVG(isnull(HIST.ClosePrice, 0)) AS AvgClose
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1200 total points
ID: 37817044
Feb 25 was a Saturday.

Instead of this:

(SELECT ClosePrice
      FROM StockHist
      WHERE  
    QuoteDate = '2012-02-25'  
      AND SymbolID=SYM.SymbolID )

Should it be this:

(SELECT ClosePrice
      FROM StockHist
      WHERE  
    QuoteDate = HIST.QuoteDate
      AND SymbolID=SYM.SymbolID )
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:Dovberman
ID: 37817202
To Plummet.

SymbolID SymbolName Max and MinClose ShowClosePrice PctChange     Change    AvgClose
5943      DORM            44.93      44.72          NULL                     NULL         0.21         44.825000
6132      FCHI            44.97      44.59          NULL                     NULL         0.38         44.793333
7524      ROLL            44.94      44.50          NULL                     NULL         0.44         44.720000
6598      ITIC            45.00      43.61          NULL                     NULL         1.39         44.663333
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37817209
Close is null, so AVG(Close) = Null
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37817215
forget that I was misaligned
0
 

Author Comment

by:Dovberman
ID: 37817216
To emereau

<<AvgClose>>

AvgClose is already correct.

I need the PctChange.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37817231
I would do it with a sub-select. what is your last version of this query?
0
 

Author Comment

by:Dovberman
ID: 37817276
To ScottPletcher

Yes, 02/25/2012 is a Saturday.  The market is closed.
My database correclty reflects this by having no row for that date.

Changed to Monday '2012-02-27' .  
All is well now.
                                    PctChange                AvgChange  AvgClosePrice
5943      DORM      0.45132172791      0.21      44.825000
6132      FCHI      0.79514542791      0.38      44.793333
7524      ROLL      0.93597106998      0.44      44.720000
6598      ITIC      3.09576837416      1.39      44.663333

Thanks,
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question