Thanks Guys !
I ended up with something like this
CREATE TABLE #TranType1(
value int,
TranDate datetime
)
CREATE TABLE #TranType2(
value int,
TranDate datetime
)
CREATE TABLE #TranType3(
value int,
TranDate datetime
)
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'
DECLARE @DtBegin datetime,@DtEnd date,@dtDif int,@RunningTotal int = NULL
set @DtBegin = '5/1/2013'
SET @DtEnd = GETDATE()
set @dtDif = DATEDIFF(day,@DtBegin,@DtEnd)
CREATE TABLE #QuirkUpdate(
value int,
TranDate date PRIMARY KEY
)
;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
)
INSERT INTO #QuirkUpdate
SELECT totValue,TranDate FROM cteResult
UPDATE #QuirkUpdate
SET @RunningTotal = value =CASE
WHEN @RunningTotal IS NOT NULL
THEN @RunningTotal+value
ELSE value
END
FROM #QuirkUpdate WITH (TABLOCKX) OPTION (MAXDOP 1) -- Nao remover o MAXDOP, pode quebrar o codigo
SELECT value,tranDate FROM #QuirkUpdate
DROP TABLE #QuirkUpdate