Generate a row per minute of the day from a sparsely populated database table

Question

Hi, I have a table populated with time stamped rows inserted at (essentially) random point in the day.

I need to generate running totals with 1 row per minute (so for a single day there will always be exactly 24 * 60 rows) e.g.

Date                Quantity    Running Total
2009-10-29 06:30           1                1
2009-10-29 06:31           5                6
2009-10-29 06:32           10              16  
2009-10-29 06:33           11              27   
2009-10-29 06:34           22              49

... ...

Any thoughts on the best way to do this? One SQL query would be ideal but not essential, performance is fairly important (sub 5 seconds on a table containg 500k rows of which 70k are interesting to this query)

My Final Solution (more or less).

The actual scenario was this. I have two tables one containing Orders with a 1:n relationship to a Fills table.

I needed to show the running Average Price and Cumulative Total for each minute in the trading day

DECLARE @StartDate AS DATETIME, @EndDate AS DATETIME
SET @StartDate = '2009-10-28';
SET @EndDate = '2009-10-29';

-- Generate a Temp Table containing all the dates I'm interested in
WITH DateIntervalsCTE AS
(
 SELECT 0 i, @StartDate AS Date
 UNION ALL
 SELECT i + 1, DATEADD(minute, i, @StartDate )
 FROM DateIntervalsCTE 
 WHERE DATEADD(minute, i, @StartDate ) < @EndDate
)
SELECT DISTINCT Date 
INTO #Dates
FROM DateIntervalsCTE
OPTION (MAXRECURSION 32767);

SELECT 
 d.Date
 , mo3.symbol
 , ISNULL(SUM(mf.Quantity),0) AS CumulativeTotal
 , ROUND(ISNULL(SUM(mf.Quantity * mf.Price)/SUM(mf.Quantity),0),4) AS AveragePrice
FROM 
 #Dates AS d
 CROSS JOIN (
    SELECT DISTINCT mo2.Symbol, mo2.OrderID 
    FROM 
     Orders AS mo2 
     INNER JOIN Fills AS mf2 ON mo2.OrderID = mf2.OrderID
    WHERE CONVERT(DATETIME,CONVERT(CHAR(10),mf2.FillDate,101)) = @StartDate
    ) AS mo3
 LEFT JOIN Fills AS mf ON mo3.OrderID = mf.OrderID AND CONVERT(DATETIME,CONVERT(CHAR(16),mf.FillDate,120)) < = d.Date
WHERE
 d.Date >= DATEADD(mi,390, @StartDate) -- 06:30
 AND d.Date <= DATEADD(mi,780, @StartDate) -- 13:00
GROUP BY d.Date, mo3.symbol
ORDER BY mo3.Symbol, d.Date

I still haven't completed all my testing but this looks like it does the trick, thanks for the assistance!

Answer 1

Make sure the date column has an index on it and performance should be reasonable.

SELECT t.Date,
COUNT(*) AS Quantity,
(SELECT COUNT(*) FROM Table WHERE Date < t.Date) AS RunningTotal
FROM Table t
GROUP BY t.Date

Getting a table populated with one row per minute can be done extremely quickly as follows:

DECLARE @StartDate smalldatetime
DECLARE @EndDate smalldatetime

SET @StartDate = '1 jan 2009' --MIN(TimeStamp) FROM Table
SET @EndDate = '2 jan 2009' --MAX(TimeStamp) FROM Table

SET @StartDate = DATEADD(minute,-DATEPART(minute,@StartDate),@StartDate)
SET @EndDate = DATEADD(minute,-DATEPART(minute,@EndDate),@EndDate)


; WITH DateIntervalsCTE AS
(
SELECT 0 i, @startdate AS Date
UNION ALL
SELECT i + 1, DATEADD(minute, i, @startdate )
FROM DateIntervalsCTE 
WHERE DATEADD(minute, i, @startdate ) <= @enddate
)
SELECT DISTINCT Date FROM DateIntervalsCTE
OPTION (MAXRECURSION 32767);

Provided you only need < ~22 days worth of data due to recursion restrictions.

All you need now is to merge the two, using a temp table to hold data seems to be quickest

DECLARE @StartDate smalldatetime
DECLARE @EndDate smalldatetime
DECLARE @t TABLE (Date smalldatetime,Quantity int,RunningTotal int)
SET @StartDate = '1 jan 2009' --MIN(TimeStamp) FROM Table
SET @EndDate = '2 jan 2009' --MAX(TimeStamp) FROM Table

SET @StartDate = DATEADD(minute,-DATEPART(minute,@StartDate),@StartDate)
SET @EndDate = DATEADD(minute,-DATEPART(minute,@EndDate),@EndDate)


; WITH DateIntervalsCTE AS
(
SELECT 0 i, @startdate AS Date
UNION ALL
SELECT i + 1, DATEADD(minute, i, @startdate )
FROM DateIntervalsCTE 
WHERE DATEADD(minute, i, @startdate ) <= @enddate
)
INSERT INTO @t (Date) 
SELECT DISTINCT Date FROM DateIntervalsCTE
OPTION (MAXRECURSION 32767);

UPDATE t SET Quantity = (SELECT COUNT(d.TimeStamp) FROM Table d WHERE Date = t.date)
from @t t

update t2 set runningtotal = (SELECT SUM(Quantity) FROM @t WHERE date <= t2.date)
from @t t2

select * from @t

Answer 2

You'll want to use group by for sure. The hard(ish) part is that it'll be synthetic, meaning you'll have to create it yourself. There are a bunch of ways to do that

GROUP BY year(yourdate), month(yourdate), day(yourdate) etc...

Except I can't remember if there are hours() and minutes() functions off the top of my head.

You can also use the datepart function.

Then you can put those all together in one column for a nice looking label.