The COALESCE method for running totals is terribly slow. Working with the link that Sean posted, here is an example of how to accomplish this using the "Quirky Update" method.

Note that I added a running total column because it is easier to test 😉

--Sample Data

---------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#TranType1') IS NOT NULL

DROP TABLE #TranType1;

IF OBJECT_ID('tempdb..#TranType2') IS NOT NULL

DROP TABLE #TranType2;

IF OBJECT_ID('tempdb..#TranType3') IS NOT NULL

DROP TABLE #TranType3;

IF OBJECT_ID('tempdb..#Tally') IS NOT NULL

DROP TABLE #Tally;

IF OBJECT_ID('tempdb..#Results') IS NOT NULL

DROP TABLE #Results;

CREATE TABLE #TranType1(

value int,

TranDate datetime);

CREATE TABLE #TranType2(

value int,

TranDate datetime);

CREATE TABLE #TranType3(

value int,

TranDate datetime);

CREATE TABLE #Tally(n int primary key);

INSERT INTO #TranType1

SELECT 10,'2013-06-14' UNION SELECT 0,'2013-06-19' UNION SELECT 0,'2013-06-24';

INSERT INTO #TranType2

SELECT 0,'2013-06-26' UNION SELECT 5208,'2013-06-28' UNION

SELECT 10,'2013-06-29' UNION SELECT 16005,'2013-07-01';

INSERT INTO #TranType3

SELECT 23135,'2013-07-02' UNION SELECT 35070,'2013-07-03' UNION

SELECT 26509,'2013-07-04' UNION SELECT 7000,'2013-07-05';

--Original Solution

---------------------------------------------------------------------------------

DECLARE @DtBegin datetime,@DtEnd date,@dtDif int

set @DtBegin = '5/1/2013'

SET @DtEnd = GETDATE()

set @dtDif = DATEDIFF(day,@DtBegin,@DtEnd)

;WITH

ctePingInfo(sumValue,TranDate) AS (

SELECT SUM(SumValue),Trandate

FROM(

SELECT COALESCE(value,0),CAST(Trandate AS date)

FROM #TranType1

UNION ALL

SELECT COALESCE(value,0),CAST(Trandate AS date)

FROM #TranType2

UNION ALL

SELECT COALESCE(value,0),CAST(Trandate AS date)

FROM #TranType3) ValInfo(SumValue,TranDate)

WHERE Trandate BETWEEN @DtBegin AND @DtEnd

GROUP BY TranDate

),

E1(N) AS ( --=== 1x10^1

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 --10

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --1x10^2

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --1x10^4

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM E4),

cteDateRange(dateRange) AS (SELECT TOP (@dtDif+1) dateadd(day,n,@DtBegin) FROM cteTally),

cteResult(totValue,TranDate) AS (

SELECT COALESCE(cpi.Sumvalue,0),cdr.daterange

FROM cteDateRange cdr LEFT JOIN ctePingInfo cpi ON cdr.daterange = cpi.TranDate)

SELECT DISTINCT SUM(res2.totvalue) OVER (PARTITION BY res.TranDate),res.TranDate

FROM cteResult res

JOIN cteResult res2 ON res.TranDate >= res2.TranDate

ORDER BY res.TranDate;

GO

--Faster Running Total Action

---------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#results') IS NOT NULL

DROP TABLE #results;

CREATE TABLE #results (id int primary key, value int not null, runningTotal int not null, TranDate datetime not null);

DECLARE @DtBegin datetime, @DtEnd date, @dtDif int, @runningTotal int=0;

SET @DtBegin = '5/1/2013'

SET @DtEnd = GETDATE()

SET @dtDif = DATEDIFF(day,@DtBegin,@DtEnd);

WITH

TranTypes AS

(SELECT value, TranDate

FROM #TranType1

UNION ALL

SELECT value, TranDate

FROM #TranType2

UNION ALL

SELECT value, TranDate

FROM #TranType3),

E1(N) AS

(SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1),

E2(N) AS (SELECT 1 FROM E1 a, E1 b),

E4(N) AS (SELECT 1 FROM E2 a, E2 b),

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM E4),

dates AS

(SELECTn, DATEADD(DAY,n,@DtBegin) AS TranDate

FROM cteTally ta

WHERE n<=@dtDif)

INSERT INTO #results

SELECTn,

ISNULL(t.value,0) AS value,

0 AS RunningTotal,

d.TranDate

FROM dates d

LEFT JOIN TranTypes t ON d.TranDate=t.TranDate

ORDER BY TranDate;

;WITH ResultsOrdered AS

(SELECT TOP 2147483648

value,

runningTotal,

TranDate

FROM #results

ORDER BY id)

UPDATE ResultsOrdered

SET @runningTotal=RunningTotal=@runningTotal+value

FROM ResultsOrdered WITH (TABLOCKX)

OPTION (MAXDOP 1)

SELECTvalue,

runningTotal,

TranDate

FROM #results

--cleanup

DROP TABLE #TranType1;

DROP TABLE #TranType2;

DROP TABLE #TranType3;

DROP TABLE #Tally;

DROP TABLE #Results;