CODEWITHSUNDEEP
sqlsql-server
kaboom
kaboom

Reputation: 833

sql server calculate cumulative number per month for different year

I have a table with "date" column. Each row represents a survey. 

  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

I want to count the number of survey per month cumulatively. As you see from the above table, there are 3 surveys for Nov 2013, 2 surveys for Dec 2013, 1 survey for Jan 2014. The cumulative number of survey per month would be:

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

I have this query which shows correct number of surveys for 2013, and number of survey for 2014 is not cumulative. 

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;

This query returns:

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

How can I calculate cumulative number of surveys per month for 2014 as well?

Upvotes: 3

Views: 2052

Answers (2)

VJ Hil
VJ Hil

Reputation: 904

'--This should work.I have added a new column 'monthyear'

   with surveypermonth as -- no of survey per month
     ( 
        select datepart(month, s.date) as month, 
        datepart(year, s.date) as year, 
        datepart(year, s.date) *100 + datepart(month, s.date) as monthyear,
        count(*) as no_of_surveys
        from test s
        group by datepart(year, s.date), datepart(month, s.date),datepart(year, s.date)*100 + datepart(month, s.date) 
    )

        select a.month,substring(cast(monthyear as varchar(6)),1,4) as year,surveys from 
        (
        select p1.month, p1.monthyear as monthyear, sum(p2.no_of_surveys) as surveys 
        from surveypermonth p1
        inner join surveypermonth p2 on p1.monthyear>=p2.monthyear 
        group by p1.month, p1.monthyear
        --order by p1.monthyear, p1.month
        )a

Upvotes: 0

deroby
deroby

Reputation: 6002

Something like this ?

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]

Edit: seems I missed the ball with < and >, fixed it and added small example

Upvotes: 2

Related Questions