I have a table with stock quotes
Symbol
Ask
Bid
QuoteDateTime
Using SQL Server 2008, lets say for any given 60 second time period I want to select quotes on all symbols so that there is a record for every second in that time period.
Problem is not every symbol has the same number of quotes - so there are some seconds that have no quote record for any given symbol. So I want to fill in the missing holes of data. So if ORCL has quotes at second 1, 2, 3, 5, 7, I want the result set that has 1,2,3,4,5,6,7...up to 60 sec (covering the whole minute). The values in row 4 come from row 3
In this scenario I would want to select the previous quote and use that for that particular second. So there is continuous records for each symbol and the same number of records are selected for each symbol.
I am not sure what this is called in sql server but any help building a query to do this would be great
For output I am expecting that for any given 60 sec time period. For those symbols that have a record in the 60 seconds, there will be 60 records for each symbol, one for each second
Symbol Ask Bid QuoteDateTime
MSFT 26.00 27.00 2010-05-20 06:28:00
MSFT 26.01 27.02 2010-05-20 06:28:01
...
ORCL 26.00 27.00 2010-05-20 06:28:00
ORCL 26.01 27.02 2010-05-20 06:28:01
etc