Running sum simplified in SQL 2012

Running sum is a regular requirement in query for developer and many times developer has to run number  of complex  queries or self joins to get running sum
But now since SQL  2012 its very easy and funny and simplified.

Lets see this exciting but simple task
 

In 2012 we can use order by also while using aggregate window function. 

In 2008 we can use only partitioned by clause but now in SQL 2012 has simplified our task 

Lets see this 

  

 CREATE TABLE test

     (

          id    INT,

          value INT,

          statsdate date

     )

 DECLARE @i INT

 SET @i = 1

 WHILE @i <= 10

     BEGIN

         INSERTINTO test

         SELECT@i,

                @i *10,

                getdate()+ @i/3

         SET @i = @I + 1

     END

 SELECT *

 FROM   test

 

Running sum for entire table
 

We want to get running sum for all data in the table
 

 Here is the query 

WITH cte

     AS (SELECT *,

                Sum(value) OVER (ORDER BY id) AS runningsum

         FROM   test)

SELECT *

FROM   cte

Running sum reset on date

Now we want running sum but datewise
So when date changes we want to reset running sum
We have to use partition by clause using statsdate column
 

Here is the query
 

WITH cte

      AS (SELECT *,

                 Sum(value) OVER (ORDER BY id)  AS runningsum,

                 Sum(value) OVER (partition BYstatsdate ORDER BYid) ASrunningsum_partitioned

          FROM   test)

 SELECT *

 FROM   cte