Joel you are on the right track; you'll need to join against another table, but it doesn't necessarily need to be a real calendar table. you can use a Tally or Numbers table;
here is an example of an inline Tally table tat is being used to generate days with some data function.
then the code is generating balances for all the days that were not featured in the data...similar to what you are planning;
example results:
TranDateTransactionAmountBalance
2008-12-01 00:00:00.000100.00100.00
2008-12-02 00:00:00.0000.00100.00
2008-12-03 00:00:00.000-60.0040.00
2008-12-04 00:00:00.00010.0050.00
2008-12-05 00:00:00.0000.0050.00
2008-12-06 00:00:00.0000.0050.00
the code example:
SET DATEFORMAT MDY
DROP TABLE #myBalances
CREATE TABLE #myBalances (
[TranDate] DATETIME,
[TransactionAmount] money,
[Balance] money)
INSERT INTO #myBalances VALUES ('12/1/2008',100,100)
INSERT INTO #myBalances VALUES ('12/3/2008',-60,40)
INSERT INTO #myBalances VALUES ('12/4/2008',10,50)
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '12/1/2008'
SET @EndDate = '12/6/2008'
;with TallyCalendar as (
--5 years prior to today plus 5 years after
SELECT dateadd( dd, -3650 + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N
FROM (
SELECT TOP 7300 -- ~10 years of days
row_number() OVER (ORDER BY sc1.id) AS RW
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
) X
)
SELECT DATEADD(DD, 0, Tally.n) AS TranDate, ISNULL(b.TransactionAmount, 0) AS TransactionAmount, x.Balance
FROM TallyCalendar Tally
LEFT JOIN #myBalances b ON b.TranDate = DATEADD(DD, 0, Tally.n)
LEFT JOIN #myBalances x ON x.TranDate = (SELECT MAX(TranDate) AS TranDate FROM #myBalances WHERE TranDate <= DATEADD(DD, 0, Tally.n))
WHERE n BETWEEN DATEDIFF(DD, 0, @StartDate) AND DATEDIFF(DD, 0, @EndDate)
Lowell