CODEWITHSUNDEEP
sql-server
Rajkumar Gor
Rajkumar Gor

Reputation: 57

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

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

Upvotes: 1

Views: 136

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

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

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

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

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

Upvotes: 1

Related Questions