Farooq,

Can you please verify the below code. Looks better performance.

DECLARE @tbl TABLE (ID int identity(1,1),dt DATETIME,program VARCHAR(100),Value INT,RunningTotal INT)

INSERT INTO @tbl(dt,program,Value)

SELECT '1/1/2012','Actual', 5

UNION ALL SELECT '1/2/2012','Actual', 5

UNION ALL SELECT '1/3/2012','Actual', 10

UNION ALL SELECT '1/4/2012','Actual', 5

UNION ALL SELECT '1/5/2012','Actual', 5

UNION ALL SELECT '1/6/2012','Actual', 5

UNION ALL SELECT '1/7/2012','Actual', 5

UNION ALL SELECT '1/8/2012','Actual', 5

UNION ALL SELECT '1/9/2012','Actual', 5

UNION ALL SELECT '1/10/2012','Forcast', 2

UNION ALL SELECT '1/11/2012','Forcast', 1

UNION ALL SELECT '1/12/2012','Planned', 1

UNION ALL SELECT '1/13/2012','Planned', 1

UNION ALL SELECT '1/14/2012','Planned', 2

UNION ALL SELECT '1/15/2012','Planned', 3

UNION ALL SELECT '1/16/2012','Planned', 4

UNION ALL SELECT '1/17/2012','Planned', 5

UNION ALL SELECT '1/18/2012','Planned',6

DECLARE @RunningTotal INT

;WITH CTE AS

(

SELECT ID,dt,program,Value,RunningTotal,ROW_NUMBER() OVER(PARTITION BY Program ORDER BY dt) AS RN,

DENSE_RANK() OVER(ORDER BY Program)RankD

FROM @tbl

)

,CTE2 AS

(

select dt,program,Value,Value AS RunningTotal,RN,RankD,

MAX(RN) OVER(PARTITION BY RANKD) AS MAXRN

FROM CTE

WHERE RN = 1

UNION ALL

SELECT cte2.dt,cte2.program,cte.Value,cte2.RunningTotal+cte.value AS RunningTotal,cte2.RN,cte2.RankD,

MAX(cte2.MAXRN+1) OVER(PARTITION BY cte2.RANKD) AS MAXRN

FROM CTE INNER JOIN CTE2 ON CTE.RankD = CTE2.RankD AND CTE.RN = CTE2.MAXRN+1

)

select dt,Program,Value,RunningTotal from CTE2

order by Program,dt