sql server计算不同年份每月的累计数

Question

我有一个包含“日期”列的表格。每行代表一项调查。

  date
    11/19/2013 5:51:41 PM
    11/22/2013 1:30:38 PM
    11/23/2013 3:09:17 PM
    12/2/2014 5:24:17 PM
    12/25/2014 11:42:56 AM
    1/6/2014 2:24:49 PM

我想累计统计每个月的调查次数。从上表中可以看出，2013 年 11 月有 3 次调查，2013 年 12 月有 2 次调查，2014 年 1 月有 1 次调查。每月累计调查次数为:

month | year | number_of_survey
11    | 2013 | 3
12    | 2013 | 5
1     | 2014 | 6

我有这个查询，它显示了 2013 年的正确调查数量，而 2014 年的调查数量不是累积的。

with SurveyPerMonth as -- no of Survey per month
       ( 
         select datepart(month, s.date) as month, 
            datepart(year, s.date) as year, 
            count(*) as no_of_surveys
         from myTable s

         group by datepart(year, s.date), datepart(month, s.date)
       )

  select p1.month, p1.year, sum(p2.no_of_surveys) as surveys -- cumulatively
  from SurveyPerMonth p1
  inner join SurveyPerMonth p2 on p1.month >= p2.month and p1.year>=p2.year **-- the problem is probably comes from this line of code**

  group by p1.month, p1.year
  order by p1.year, p1.month;

此查询返回:

month | year | surveys
11    | 2013 | 3
12    | 2013 | 5
1     | 2014 | 1     // 2014 is not cumulative

如何计算 2014 年每月的累计调查数量？

Answer 1

是这样的吗？

SELECT date = create_date INTO #myTable FROM master.sys.objects

;WITH perMonth ( [year], [month], [no_of_surveys])
   AS (SELECT DatePart(year, s.date) , 
              DatePart(month, s.date), 
              COUNT(*)
         FROM #myTable s
        GROUP BY datepart(year, s.date), 
                 datepart(month, s.date))
SELECT [year],
       [month],
       [no_of_surveys] = ( SELECT SUM([no_of_surveys])
                             FROM perMonth agg
                            WHERE (agg.[year] < pm.[year])
                               OR (agg.[year] = pm.[year] AND agg.[month] <= pm.[month]))
  FROM perMonth pm
 ORDER BY [year], [month]

编辑:我似乎错过了 < 的球和 > , 修复它并添加了小例子