April 14, 2009 at 2:08 pm
I have quite a bit of time stamped data which occurs at irregular intervals.
(By irregular intervals, I mean that sometimes there is 1 second between the time stamps on two pieces of data points, other times there are minutes, or hours. And other times multiple data points occur at the exact same time.)
I was wondering what the best way would be to retrieve data that corresponds to a given timestamp, allowing for the timestamp to be 'fuzzy'.
The best solution I have found so far is a query like the one below, but it seems rather inefficient. Is there a better way to communicate this kind of fuzzy matching to SQL?
Thanks!
SELECT *
FROM MyTable
WHERE MyDataType = 'A'
AND MyTimeStamp = (SELECT MAX(MyTimeStamp)
FROM MyTable
WHERE MyDataType = 'A '
AND MyTimeStamp <= '1pm' )
April 14, 2009 at 2:15 pm
something like this do what you need?
SELECT top 1 *
FROM MyTable
WHERE MyDataType = 'A'
AND MyTimeStamp <= '1pm'
order by MyTimeStamp
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2009 at 7:40 am
Thanks! I hadn't thought of that 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply