Sub Query in SQL Server 2000 - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Sub Query in SQL Server 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-27 : 11:24:46

Bhivsen writes "A table named as SCRIP_RATES of my database contains 4 fields as
scrip_code,scrip_name,trade_date and closing_rate
This table is having huge records (700000+) of the rates of number of companies.My querry is to get the scrip_code,scrip_name,max(closing_rate) and the trade_date for
max(closing_rate).

I have tried many times to get all the fields but failed to get the date field for max(closing_rate).

The procedure written by me for get the data of last 1 year is as below

ALTER PROCEDURE HIGH_RATE
@CODE INT

AS
BEGIN

SELECT SCRIP_CODE,SCRIP_NAME,"HIGH_CLOSE" = MAX(CLOSING_RATE FROM SCRIP_RATES WHERE
@CODE = SCRIP_CODE AND
DATE >= DATEADD (MM,-12,GETDATE())

END

So humble request to please help me to get my querry resolved. If possible help me to get of many companies at a time.

Thanks and Regards,
Bhivsen."

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-27 : 17:46:36

select scrip_code, scrip_name, trade_date, max(closing_rate) as high_close
from
scrip_rates
where
scrip_code=@code and trade_date>=dateadd(mm,-12,getdate())
group by scrip_code, scrip_name, trade_date

- Vit

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-07-28 : 05:07:06

I think this sort of thing is more what Bhivsen wanted:


SELECT scrip_code, scrip_name, MAX(trade_date) AS trade_date, closing_rate
FROM (
    SELECT scrip_code, scrip_name, trade_date, closing_rate
    FROM scrip_rates AS S1
    WHERE closing_rate = (
        SELECT MAX(closing_rate)
        FROM scrip_rates AS S2
        WHERE S1.scrip_code = S2.scrip_code
          AND S1.scrip_name = S2.scrip_name /* See note */
          AND trade_date >= DATEADD(month, -12, GETDATE())
      )
      AND trade_date >= DATEADD(month, -12, GETDATE())
  ) AS A
GROUP BY scrip_code, scrip_name, closing_rate

Note: the line with the note may not be necessary: it's not clear to me what scrip_name is doing in this table -- if it's uniquely determined by scrip_code then the table is denormalized.

In-joke: "All Scrips have Floors"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-28 : 07:47:38

Arnold, your code seems longer than it needs to be:

SELECT a.scrip_code, a.scrip_name, a.closing_rate, a.trade_date
FROM SCRIP_Rates A
INNER JOIN
(SELECT scrip_code, max(closing_Date)
FROM SCRIP_RATES
GROUP BY scrip_code) B
ON
A.Scrip_Code = B.Scrip_Code

Same deal as Arnold mentioned witht the scrip_code/name thing -- the table appears to not be normalized ....

edit:
actually, there's many things wrong about my code!

1) i didn't give max(scrip_Date) an alias!
2) i didn't join the subquery correctly (probably because i forgot the alias!)
3) i misread it as scrip_DATE instead of scrip_RATE !!!

yikes .. quite a few errors there

never mind !! thanks arnold ...

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-07-28 : 08:35:48

Well, that returns a row for each date on which a scrip_code's closing_rate was at its maximum. The original question asks for "the trade_date", so I assumed that only one row per scrip_code was wanted.
It seems unlikely to me that (scrip_code, closing_rate) is a candidate key of SCIRP_RATES.
(I'm ignoring subquery vs join issues)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources