Urgent deliverable

[Kalyan]

Hi

I have a query as follows

select * from
( select CompanyID , TickerSymbol , CompanyName,
dbo.FormatNumbe r(LatestClosing Price,2) as 'CurrentPrice' from
backscreeningda ta3
where dailydate= '12/31/04' and (LatestClosingP rice>100) ) as m0,
( select CompanyID , TickerSymbol , CompanyName,
dbo.FormatNumbe r(LatestClosing Price,2) as 'CurrentPrice' from
backscreeningda ta3 where dailydate= '12/31/04' and
(LatestClosingP rice>150) ) as m1
where 1=1 and m0.CompanyId=m1 .CompanyId
Order By m0.Tickersymbol asc

And the resultset is follows

ALX ALEXANDER'S INC 215 215
BRK.A BERKSHIRE HATHAWAY -CL A 87900 87900
CME CHICAGO MERC EXCH HLDGS 228.7 228.7
FFH FAIRFAX FINANCIAL HLDG 168.5 168.5
GOOG GOOGLE INC 192.79 192.79
GREY GREY GLOBAL GROUP INC 1099.99 1099.99
MKL MARKEL CORP 364 364
NVR NVR INC 769.4 769.4
NWLIA NATIONAL WESTERN LIFE 166.5 166.5
SEB SEABOARD CORP 998 998
STU STUDENT LOAN CORP 184 184
WPO WASHINGTON POST -CL B 983.02 983.02
WSC WESCO FINANCIAL CORP 393 393
Y ALLEGHANY CORP 285.25 285.25

ie i get the common stocks that exits in both the criteria

but my requirement is i want all the stocks that pass each criteria

[Madhivanan]

Re: Urgent deliverable


Try to use outer joins. See more on BOL

Madhivanan

[Hugo Kornelis]

Re: Urgent deliverable

On 2 Mar 2005 23:20:07 -0800, Kalyan wrote:
[color=blue]
>I have a query as follows[/color]
(snip)[color=blue]
>but my requirement is i want all the stocks that pass each criteria[/color]

Hi Kalyan,

I fail to see how your query can produce the posted results. As a result
of SELECT * and the two joined derived tables, you should have a result
set of 8 columns: CompanyID, TickerSymbol, CompanyName, CurrentPrice,
CompanyID, TickerSymbol, CompanyName, CurrentPrice. And the last four
columns should be equal to the first four on each row.

I also don't understand your requirements. The first derived table
selects rows based on dailydate 12/31/04 and LatestClosingPr ice > 100;
the second derived table selects the same dailydate and increases the
threshold for LatestClosingPr ice to 150. If you want stocks that pass
both criteria, you can simply run the second derived table only, as
having a LatestClosingPr ice > 150 ensures that it will also be > 100.

SELECT CompanyID, TickerSymbol, CompanyName,
dbo.FormatNumbe r(LatestClosing Price,2) AS CurrentPrice
FROM backscreeningda ta3
WHERE dailydate= '20041231' -- Changed to unambiguous YYYYMMDD format
AND LatestClosingPr ice > 150

And if you want stocks that pass one or both of the criteria, run the
first derived table as a standalone query:

SELECT CompanyID, TickerSymbol, CompanyName,
dbo.FormatNumbe r(LatestClosing Price,2) AS CurrentPrice
FROM backscreeningda ta3
WHERE dailydate= '20041231' -- Changed to unambiguous YYYYMMDD format
AND LatestClosingPr ice > 100

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)