Having for example a table like this:
Date Cnt Val Cumulative IsLastDate
................................................
2014-01-01 1 5 0 0
2014-01-02 1 100 0 0
2014-01-03 1 300 0 0
2014-01-04 1 300 800 1
2014-01-05 1 10 0 0
2014-01-06 1 50 0 0
2014-01-07 1 50 0 0
2014-01-08 1 150 500 1
2014-01-01 2 5 0 0
2014-01-02 2 20 0 0
2014-01-03 2 50 0 0
2014-01-04 2 100 300 1
2014-01-05 2 5 0 0
2014-01-06 2 20 0 0
2014-01-07 2 5 0 0
2014-01-08 2 10 100 1
More exactly, I have symmetric chunk of data (which are independent, so the update stops at the start of a new chunk) based on Cnt column value which starts on a day and continues until IsLastDate becomes 1 and so on.
The rows with IsLastDate = 1 are fixed and don't need any updates. I want to update every chunk of data, starting from IsLastDate = 1 upwards, based on Date in such way that Cumulative = Cumulative of the next day - Val of the next Day
The result should look like this:
Date Cnt Val Cumulative IsLastDate
................................................
2014-01-01 1 5 100 0
2014-01-02 1 100 200 0
2014-01-03 1 300 500 0
2014-01-04 1 300 800 1
2014-01-05 1 10 250 0
2014-01-06 1 50 300 0
2014-01-07 1 50 350 0
2014-01-08 1 150 500 1
2014-01-01 2 5 130 0
2014-01-02 2 20 150 0
2014-01-03 2 50 200 0
2014-01-04 2 100 300 1
2014-01-05 2 5 65 0
2014-01-06 2 20 85 0
2014-01-07 2 5 90 0
2014-01-08 2 10 100 1
Doing the above will obviously only work for the first Cumulative value of the previous day in each chunk:
UPDATE T
SET T.Cumulative = TT.Cumulative - TT.Val
FROM T INNER JOIN T TT ON
TT.[Date] = DATEADD(DAY,1,T.[Date]) AND TT.Cnt = T.Cnt AND T.IsLastDate <> 1
How can I solve this in a proper way ?
This has to work on SQL Server >=2005
SQL test fiddle here
After trying @Nizam's way, I ended up implementing it another way, because the query was taking too long (1 min and 20 seconds for 16000 rows )
This one runs almost instantly (changed update with a simple select):
SELECT M.[Date],M.Cnt,M.Val,
Cumulative =
CASE (M.IsLastDate) WHEN 1 THEN M.Cumulative
ELSE ((SELECT TOP(1) Cumulative FROM T WHERE [Date] > M.[Date] AND Cnt = M.Cnt
AND IsLastDate = 1)
-
(SELECT SUM(Val) FROM T B WHERE B.[Date] > M.[Date]
AND M.Cnt = B.Cnt
AND B.[Date] <= (SELECT TOP(1) [Date] FROM T WHERE [Date] > M.[Date]
AND Cnt = M.Cnt AND IsLastDate = 1))
)
END,
M.IsLastDate
FROM T M
ORDER BY CNT,[Date] ,IsLastDate%1 --Just for viewing in an ordered way