sql server - How to group by month and year for entire calender year until this month in SQL -

sql server - How to group by month and year for entire calender year until this month in SQL -

i have query

declare @date datetime select @date = '2014-04-01' select @date, count(*) claim c inner bring together prop_vehicles pv on pv.prop = c.prop pv.vehicle in (1,2) , c.datecreate >= @date , claimcodeid =5

i want grouping month wise calnder year. example

april 2014 - 200 may 2014 - 300 june 2014 - 500 . . oct 2014 - 100

something this. how accomplish it? help me how split @date 2 fields , grouping month year wise until current month mentioned above?

i reckon datepart function do? allow me check that.

thank in advance.

in case months don't have info skip months.

if want months info if value zero, need build months table , bring together it

select dateadd(month, datediff(month,0,c.datecreate), 0), count(*) claim c inner bring together prop_vehicles pv on pv.prop = c.prop , pv.vehicle in (1,2) , and c.datecreate >= @date , and claimcodeid =5 grouping dateadd(month, datediff(month,0,c.datecreate), 0)

as per latest comment

here way months info , display year , month

declare @date datetime select @date = '2014-04-01' ;with cte ( select dateadd(month, datediff(month,0,@date), 0) monthval,1 n union select dateadd(month, datediff(month,0,@date)+n, 0) monthval, n+1 cte n <=5 ) select datename(year, monthval) year, datename(month,monthval) month, count(*) cte left bring together claim c on dateadd(month, datediff(month,0,c.datecreate)= cte.monthval inner bring together prop_vehicles pv on pv.prop = c.prop , pv.vehicle in (1,2) , and c.datecreate >= @date , and claimcodeid =5 grouping datename(year, monthval) , datename(month,monthval)

sql sql-server sql-server-2008 group-by subquery