SQL Query with multiple criteria

Question

How can I write a SQL query that would select the latest recommendation from each analyst for each stock as known on a specific date (e.g. 1/1/10). For example, if the analyst has multiple ratings, but the max(rating_date) is greater than the specified date, it should still return the rating at the max date less than the specified date. So, in the case below at a specified date of 1/1/10, I should still get the rating for John as of 6/9/09 as well as the value for Smith as of 12/5/09.

Here's a data structure:

Stock_Ticker,  Analyst_Name,  Rating_Date,  Rating_Name

G,             John,          6/9/09,       Hold

G,             John,          1/20/10,      Sell

G,             Smith,         12/5/09,      Buy

If I try something like the SQL below, I only get a value if the max date is less than the specified date.

Select Analyst_Ratings.Stock_Ticker, 
       Analyst_Ratings.Analyst_Name, 
       Analyst_Ratings.Rating_Name 
From Analyst_Ratings
Where Rating_Date In(
    Select Max(Rating_Date)
    From Analyst_Ratings
    Where Rating_date <= '2010-01-01')

specify a group by condition in your inner query.

Vamsi Prabhala
– Vamsi Prabhala

2015-07-17 16:10:24 +00:00
Commented Jul 17, 2015 at 16:10 — Vamsi Prabhala
– Vamsi Prabhala, Commented Jul 17, 2015 at 16:10

Vamsi Prabhala · Accepted Answer · 2015-07-17 16:18:45Z

2

Select Analyst_Ratings.Stock_Ticker, 
   Analyst_Ratings.Analyst_Name, 
   Analyst_Ratings.Rating_Name 
From Analyst_Ratings
Where Rating_Date In(
Select Max(Rating_Date)
From Analyst_Ratings
group by stock_ticker, analyst_name
Where Rating_date <= '2010-01-01')

Try this

answered Jul 17, 2015 at 16:18

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ann L. Over a year ago

@Michael @vkp This has a flaw: this won't get you the final rating per analyst, per stock. This will give you all ratings that have the same rating_date as the last rating for any analyst, any stock. You aren't "correlating" the subquery with the main query.

Ann L. · Accepted Answer · 2015-07-17 16:13:14Z

0

This is using T-SQL syntax, but the idea should get you started:

Select ar.Stock_Ticker, 
       ar.Analyst_Name, 
       ar.Rating_Name,
       ar.Rating_Date 
From   Analyst_Ratings ar
    Inner Join (
        select  ar2.Stock_Ticker
            ,   ar2.Analyst_Name
            ,   Max(ar2.Rating_Date) as Last_Rating_Date
        from    Analyst_Ratings ar2
        where   ar2.Rating_Date <= '2010-01-01')
        group by 
                ar2.STock_Ticker
            ,   ar2.Analyst_Name
   ) as Last_Ratings lr
   on ar.Stock_Ticker = lr.Stock_Ticker
   and ar.Analyst_Name = lr.Analyst_Name
   and ar.Rating_Date = lr.Last_Rating_Date

answered Jul 17, 2015 at 16:13

Ann L.

14k5 gold badges39 silver badges67 bronze badges

Collectives™ on Stack Overflow

SQL Query with multiple criteria

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related