December 10, 2004 at 11:39 am
I have a need to search a vary large table of data for multiple stock securities and types and return just the records for each security that are closest to 10:00 am, but not past this time.
The data in the large table was imported via DTS and includes an ID field (smallint) for each security, a datetime DATE field, a datetime TIME field and about a dozen other fields.
The DATE field has dates, with the time part set to zeros. The TIME field has dates 1899-12-30 plus the time.
I would like to select just records from this table for each security and each day (1 record per day) where the time is closest to 10:00 am, but not past 10:00 am.
I am fairly new to SQL Server and I'm not sure how to do this. Any help will be appreciated. I did find that the following expression will return the number of minutes +- from 10:00 am for each record.
datediff(minute, (CONVERT(datetime, '1899-12-30 10am')),[TIME])
Thanks!
Bill Patterson
UW-Madison School of Business
December 10, 2004 at 12:58 pm
Try this:
SELECT a.date, a.fromid, a.toid, a.body
from Stocks a
where a.date IN (select max(b.date) from Stocks b
where datepart(hh, b.date) < 10
group by convert(varchar(10), b.date, 112))
December 10, 2004 at 3:36 pm
Try:
select tbl.*
from
tbl join
(select [ID],[DATE],max([TIME]) as [TIME] from tbl
where [TIME] < '18991230 10:00:00.000'
group by [ID],[DATE] ) as closest_to_ten
on tbl.[ID] = closest_to_ten.[ID]
and tbl.[DATE] = closest_to_ten.[DATE]
and tbl.[TIME] = closest_to_ten.[TIME]
/rockmoose
You must unlearn what You have learnt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply