How to get monthly summation with sum of previous months in sql server 2008

Question

I have written a sql query to get Total Active Customer as

SELECT 
    Cast((Datepart(year,[p].Transdate)) as varchar(50)) + '-' + 
    Cast((Datepart(Month,[p].Transdate)) as varchar(50)) AS [Month/Year] ,
    Count(Distinct([c].CustomerID)) as [Active Customers] 
FROM 
    CustomerPoints as [p] 
INNER JOIN
    Customers AS [c] ON [c].[CustomerID] = [p].[CustomerID] 
WHERE
    [p].Transdate BETWEEN '2013-01-20' AND '2015-03-05' 
    AND [c].DistributorID = '1' 
    AND [p].[TransType] = 'D' 
    AND [p].[Litres] > '0'
GROUP BY  
    Cast((Datepart(Year,[p].Transdate)) AS varchar(50)) + '-' + 
    Cast((Datepart(Month,[p].Transdate)) AS varchar(50)) 
ORDER BY 
    Cast((Datepart(Year,[p].Transdate)) AS varchar(50)) + '-' + 
    Cast((Datepart(Month,[p].Transdate)) AS varchar(50)) ASC

and I got the output as

Month/year    ActiveCustomer
----------------------------------
2013-1             1
2014-3             1
2015-2             1

but I want output as summation active members of previous month + active members of current month

Month/year    Active Customer
-------------------------------    
2013-1             1
2014-3             2
2015-2             3

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

Use SUM([your column]) OVER (ORDER BY [your set of columns]).

More here: How can I use SUM() OVER()

edited May 23 '17 at 11:50

Community

1
1

answered Mar 23 '15 at 12:46

Evaldas Buinauskas

13,739
11
55
107

1

this cannot be used in sql-server 2008. Running sum was introduced with 2012 – t-clausen.dk Mar 23 '15 at 12:51

t-clausen.dk · Accepted Answer · 2015-03-23T13:46:25.763

Try this, it joins the table CustomerPoints against it self to get the running sum:

;WITH CTE AS
(
    SELECT 
        dateadd(month, datediff(month, 0, [p].Transdate), 0) monthstart,
        count(distinct [p].CustomerID) cnt
    FROM 
        CustomerPoints as [p] 
    INNER JOIN
        Customers AS [c] ON [c].[CustomerID] = [p].[CustomerID] 
    LEFT JOIN
        CustomerPoints as [p2] ON [p2].[Transdate] <= [p].[Transdate]
        and [p2].[Transdate] >= '2013-01-20'
    WHERE
        [p].Transdate BETWEEN '2013-01-20' AND '2015-03-05' 
    GROUP BY  
        dateadd(month, datediff(month, 0, [p].Transdate), 0)
)
SELECT convert(char(7), CTE.monthstart, 126) [Month/Year], 
sum(CTE2.cnt) ActiveCustomer 
FROM CTE
LEFT JOIN
CTE as [CTE2] ON [CTE2].monthstart <= [CTE].monthstart
GROUP BY CTE.monthstart

so if a customer exist one month, you want it included in next month count as well ? — t-clausen.dk, Mar 23 '15 at 13:27

How to get monthly summation with sum of previous months in sql server 2008

2 Answers2