如何在server 2008中用前几个月的和求月和

Question

我编写了一个sql查询，以获得“总活动客户”，如

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

我得到的输出是

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

但我希望输出为前一个月的活动成员之和+当前月份的活动成员。

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

Answer 1

尝试这样做，它将表CustomerPoints与self连接起来，以获得正在运行的和：

;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

Answer 2

使用SUM([your column]) OVER (ORDER BY [your set of columns])。

这里有更多信息：How can I use SUM() OVER()