当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - SQL cumulative previous 12 months - breakdown per month

问题描述:

Can anyone assist in a sql statement I am trying to write? I'm using SSRS r1 (either sql or ssrs solution is fine)

How do I:

  • show count measure split by month and year
  • for each of those months, I want to count the previous cumulative 12 months

e.g.

2012 jan: counts feb 2011 - jan 2012

2012 feb: counts mar 2011 - feb 2012

2012 mar: counts apr 2011 - mar 2012

I have started this code but it's incorrect, however it gives you an idea of what I am trying to achieve (this issue is I have to calc month and year from a date)

select

count(a.measure) count

,month(a.StartDate)

,year(a.StartDate)

from

a

where

a.StartDate >= DATEADD(mm,DATEDIFF(mm,0,@datepromt)-12,0) as startdateYrAgo --1st month 1 year ago 01/01/2012

and a.StartDate <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@datepromt)+1,0)) as startdateEOM --last day of month 31/01/2013

group by

month(a.StartDate)

,year(a.StartDate)

网友答案:

Here you have an idea for the query, it have to look something like below;

SELECT 
 periods.year
,periods.month
,measures.cnt
FROM (
    SELECT DISTINCT
      year = YEAR(StartDate)
    , month = MONTH(StartDate)
    , month_running = DATEDIFF(mm, 0, StartDate) 
    FROM a
    GROUP BY YEAR(StartDate), MONTH(StartDate), DATEDIFF(mm, 0, StartDate) 
) periods
JOIN (
    SELECT month_running = DATEDIFF(mm, 0, StartDate), cnt = COUNT(measure) 
    FROM a
    GROUP BY DATEDIFF(mm, 0, StartDate) 
) measures
ON measures.month_running BETWEEN periods.month_running - 12 AND periods.month_running - 1
分享给朋友:
您可能感兴趣的文章:
随机阅读: