Dear Friend,
Try this, I hope this will fulfill your requirement.
But its taken from Net.
SET DATEFORMAT MDY
DROP TABLE #myBalances
CREATE TABLE #myBalances (
[TranDate] DATETIME,
[TransactionAmount] money,
[Balance] money)
INSERT INTO #myBalances VALUES ('5/1/2012',100,100)
INSERT INTO #myBalances VALUES ('5/3/2012',-60,40)
INSERT INTO #myBalances VALUES ('5/4/2012',10,50)
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '5/21/2012'
SET @EndDate = '5/28/2012'
;with TallyCalendar as (
SELECT dateadd( dd, -3650 + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N
FROM (
SELECT TOP 7300
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
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)
Regards,
AP