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 

          id    INT,
          value INT,
          statsdate date


 SET @i = 1

 WHILE @i <= 10
         INSERT INTO test
         SELECT @i,
                @i * 10,
                getdate()+ @i/3

         SET @i = @I + 1

 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)
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 BY statsdate ORDER BY id) AS runningsum_partitioned
          FROM   test)
 FROM   cte


No comments:

Post a Comment

Popular Posts