sql - Using Multiple CTEs -

sql - Using Multiple CTEs -

i getting duplicate results each cte using grouping months data. part of query problem. there calculation on basis columns created on ctes. please help this...

declare @startdate datetime set @startdate='12/01/2012' while @startdate<'12/02/2012' --set getdate() begin cte3 (parameter,month3,count3,count2,count1,daterange,orgid) ( select cast(col1 varchar(50)) parameter ,sum(case when daterange=cast(left(datename(month,dateadd(month,datediff(m,0,@startdate),0)),3) + '-' + datename(yyyy,@startdate) varchar(50)) col2 else 0 end) month3 ,(select count(col1) [table_name] col1='ins charges' , daterange = cast(left(datename(month,dateadd(month,datediff(m,0,@startdate),0)),3) + '-' + datename(yyyy,@startdate) varchar(50))) count3 ,(select count(col1) [table_name] daterange = cast(left(datename(month,dateadd(month,datediff(m,0,@startdate)-1,0)),3) + '-' + datename(yyyy,@startdate) varchar(50))) count2 ,(select count(col1) [table_name] daterange = cast(left(datename(month,dateadd(month,datediff(m,0,@startdate)-2,0)),3) + '-' + datename(yyyy,@startdate) varchar(50))) count1 ,daterange ,orgid [table_name] daterange=cast(left(datename(month,dateadd(month,datediff(m,0,@startdate),0)),3) + '-' + datename(yyyy,@startdate) varchar(50)) grouping col1,orgid,daterange ) ,cte2 (parameter,month2,daterange,orgid) ( select cast(col1 varchar(50)) parameter ,sum(col2) month2 ,daterange ,orgid [table_name] daterange= cast(left(datename(month,dateadd(month,datediff(m,0,@startdate)-1,0)),3) + '-' + datename(yyyy,@startdate) varchar(50)) grouping col1,orgid,daterange ) ,cte1 (parameter,month1,daterange,orgid) ( select cast(col1 varchar(50)) parameter ,sum(col2) month1 ,daterange ,orgid [table_name] daterange= cast(left(datename(month,dateadd(month,datediff(m,0,@startdate)-2,0)),3) + '-' + datename(yyyy,@startdate) varchar(50)) grouping col1,daterange,orgid ) select cte3.parameter ,cte1.month1 ,cte2.month2 ,cte3.month3 ,cte1.month1+cte2.month2+cte3.month3 ytd ,(cte1.month1+cte2.month2+cte3.month3)/3 [3 month avg] ,cte3.orgid ,cte3.daterange cte3 bring together cte1 on cte1.parameter=cte3.parameter bring together cte2 on cte2.parameter=cte3.parameter set @startdate=(select dateadd(m,1,@startdate)) end

sample data

table_name

col1 | col2 | orgid | daterange| abc1 |100 |1 |jan-2014 | abc2 |200 |2 |feb-2014 | abc1 |300 |3 |jan-2014 | abc2 |400 |1 |feb-2014 | abc1 |100 |2 |mar-2014 | abc2 |200 |3 |mar-2014 | --------------------------------------

this study lastly 3 months contains complex calculations. if can notice table_name contain daterange on basis of have create lastly 3 months info in columns , according info above query fetches (i.e. month1, month2 etc), have perform mathematical operations. why have choosen cte.

i able generate results problem getting duplicate rows per column each cte has grouping by.

if alternative please allow me know!!!!

thanks in advance!!!

you should bring together cte-s on each of columns in grouping clauses, such as:

from cte3 bring together cte1 on cte1.parameter=cte3.parameter , cte1.orgid=cte3.orgid , cte1.daterange=cte3.daterange bring together cte2 on cte2.parameter=cte3.parameter , cte2.orgid=cte3.orgid , cte2.daterange=cte3.daterange

(assuming columns not allow nulls)

sql sql-server tsql common-table-expression