0

Here is desired result :

enter image description here

At ID=14, I need to reset cum value add with new cum_value.

SQL : (fiddle : http://sqlfiddle.com/#!6/62f01/14)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2286756
  • 211
  • 1
  • 2
  • 11
  • I'm not sure to quite understand "At ID=14, I need to reset cum value add with new cum_value." could you give more details please ? – Rubik Mar 02 '15 at 08:28
  • At ID value equal to 14, need to *1000) and for the next value will cumulative base on new value at ID = 14. Ex Formula in excel : IF(IDx=14,P_cum_value*1000,P_cum_value+ID) – user2286756 Mar 02 '15 at 08:32

1 Answers1

0

You were not so far, but I've try with less complicated queries :

DECLARE @t table(
        id int
        )

INSERT  INTO @t (id)
VALUES  (0),(1),(2),(3),(4),(5),(6),
        (7),(8),(9),(10),(11),(12),(13),
        (14),(15),(16),(17),(18),(19),
        (20),(21),(22),(23),(24),(99);

SELECT  *
        ,SUM(id)
         OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cum_STEPID
FROM    @t
WHERE   id < 14
UNION
SELECT  *
        ,SUM(CASE WHEN id = 14 THEN 91000 ELSE id END)
         OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cum_STEPID
FROM    @t
WHERE   id >= 14
Rubik
  • 1,431
  • 1
  • 18
  • 24
  • Thanks @Rubik, you drive me to the solution. I already update my final solution at fiddle : http://sqlfiddle.com/#!6/62f01/14. – user2286756 Mar 03 '15 at 02:05