case - max - 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 2005 Forums

Transact-SQL (2005)

case - max

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-12-06 : 05:19:40

Hi,
This query returns the correct results without the group by
I now would like to have max(Bid_Price), min(Ask_Price) and then use the group by Import_Date
How do I add the max and min to the first two fields in the select query please?

select
Bid_Price =
case
when dbo.isreallynumeric(ltrim(rtrim(Bid_Price))) = 1 then convert(decimal(12, 4), ltrim(rtrim(Bid_Price)))
end,
Ask_Price =
case
when dbo.isreallynumeric(ltrim(rtrim(Ask_Price))) = 1 then convert(decimal(12, 4), ltrim(rtrim(Ask_Price)))
end,
Import_Date
from
tblPrices
where
source_id in (select source_id from tblSources where Security_ID = 125)
and Import_Date >=dateadd(day, -7, dateadd(day,datediff(day,0,getdate()),0))
group by
Import_Date

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-06 : 05:31:32

[code]
select
Max(Bid_Price) as Bid_Price,
Min(Ask_Price) as Ask_Price,
Import_Date
from
(
select
Bid_Price = case
when dbo.isreallynumeric(ltrim(rtrim(Bid_Price))) = 1
then convert(decimal(12, 4), ltrim(rtrim(Bid_Price)))
end,
Ask_Price = case
when dbo.isreallynumeric(ltrim(rtrim(Ask_Price))) = 1
then convert(decimal(12, 4), ltrim(rtrim(Ask_Price)))
end,
Import_Date
from tblPrices
where source_id in (select source_id from tblSources where Security_ID = 125)
and Import_Date >=dateadd(day, -7, dateadd(day,datediff(day,0,getdate()),0))
) a
group by Import_Date
[/code]