Urgent deliverable

[Kalyan]

Hi

I have a query as follows

select * from
( select CompanyID , TickerSymbol , CompanyName,
dbo.FormatNumber(LatestClosingPrice,2) as 'CurrentPrice' from
backscreeningdata3
where dailydate= '12/31/04' and (LatestClosingPrice>100) ) as m0,
( select CompanyID , TickerSymbol , CompanyName,
dbo.FormatNumber(LatestClosingPrice,2) as 'CurrentPrice' from
backscreeningdata3 where dailydate= '12/31/04' and
(LatestClosingPrice>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]

Try to use outer joins. See more on BOL

Madhivanan

[Hugo Kornelis]

On 2 Mar 2005 23:20:07 -0800, Kalyan wrote:

I have a query as follows (snip)but my requirement is i want all the stocks that pass each criteria

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 LatestClosingPrice > 100;
the second derived table selects the same dailydate and increases the
threshold for LatestClosingPrice to 150. If you want stocks that pass
both criteria, you can simply run the second derived table only, as
having a LatestClosingPrice > 150 ensures that it will also be > 100.

SELECT CompanyID, TickerSymbol, CompanyName,
dbo.FormatNumber(LatestClosingPrice,2) AS CurrentPrice
FROM backscreeningdata3
WHERE dailydate= '20041231' -- Changed to unambiguous YYYYMMDD format
AND LatestClosingPrice > 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.FormatNumber(LatestClosingPrice,2) AS CurrentPrice
FROM backscreeningdata3
WHERE dailydate= '20041231' -- Changed to unambiguous YYYYMMDD format
AND LatestClosingPrice > 100

Best, Hugo
--

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