sql server stock quote every second

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 ha开发者_开发知识库s 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

---

Here's one way. A triangular join could also be used. Wonder if there are any other options?

DECLARE @startTime DATETIME = '2010-09-16 14:59:00.000';

WITH Times AS
(
SELECT @startTime AS T
UNION ALL
SELECT DATEADD(SECOND,1, T) FROM Times
WHERE T < DATEADD(MINUTE,1,@startTime)
),
Stocks AS
(
SELECT 'GOOG' AS Symbol
UNION ALL
SELECT 'MSFT'
),
Prices AS
(
SELECT 'GOOG' AS Symbol, 1 AS Ask, 1 AS Bid, 
        CAST('2010-09-16 14:59:02.000' AS DATETIME) AS QuoteDateTime 
UNION ALL
SELECT 'GOOG' AS Symbol, 1 AS Ask, 1 AS Bid, 
        CAST('2010-09-16 14:59:02.000' AS DATETIME) AS QuoteDateTime 
UNION ALL
SELECT 'GOOG' AS Symbol, 1 AS Ask, 1 AS Bid, 
        CAST('2010-09-16 14:59:02.000' AS DATETIME) AS QuoteDateTime 
UNION ALL
SELECT 'MSFT' AS Symbol, 1 AS Ask, 1 AS Bid, 
        CAST('2010-09-01 12:00:00.000' AS DATETIME) AS QuoteDateTime
)
SELECT p.Symbol, p.Ask, p.Bid, p.QuoteDateTime 
FROM Times t CROSS JOIN Stocks s
CROSS APPLY
(SELECT TOP 1 p.Symbol, p.Ask, p.Bid, p.QuoteDateTime 
 FROM Prices p 
 WHERE s.Symbol = p.Symbol AND p.QuoteDateTime <= t.T) p