select * from tbl_DW_1FSOperators with (nolock) where ownerId = and ownerType = and choId = select distinct a.contractId from tbl_DW_OperatorAgreements a with (nolock) where a.ownerId = and a.ownerType = and a.cho_Id = and exists ( select * from tbl_DW_OperatorAnalyzerClaimDetails cd with (nolock) where cd.ownerId = a.ownerId and cd.ownerType = a.ownertype and cd.contractId = a.contractId ) with purchasing as ( select skuId dataId, sku + ' - ' + skuDesc as text, datepart( year, purchasingMonth ) theYear, 'M_' + convert( varchar, datepart( month, purchasingMonth )) purchasingMonth, forecast_qty purchasingVolume from tbl_DW_OperatorAnalyzer with (nolock) where ownerId = and ownerType = and cho_id = and skuId = and prodId = and categoryId = and prodLine_id = ), crosstab as ( select * from purchasing pivot( sum( purchasingVolume ) for purchasingMonth in ( [M_1],[M_2],[M_3],[M_4],[M_5],[M_6],[M_7],[M_8],[M_9],[M_10],[M_11],[M_12] ) ) p ) select dataId, text, theYear , coalesce( M_1, 0 ) as M_1 , coalesce( M_2, 0 ) as M_2 , coalesce( M_3, 0 ) as M_3 , coalesce( M_4, 0 ) as M_4 , coalesce( M_5, 0 ) as M_5 , coalesce( M_6, 0 ) as M_6 , coalesce( M_7, 0 ) as M_7 , coalesce( M_8, 0 ) as M_8 , coalesce( M_9, 0 ) as M_9 , coalesce( M_10, 0 ) as M_10 , coalesce( M_11, 0 ) as M_11 , coalesce( M_12, 0 ) as M_12 from crosstab with members as ( select m.ownerId, m.ownerType, m.choId, m.operatorName from tbl_DW_1FSOperators m with (nolock) where m.ownerId = and m.ownerType = and m.parentChoId = and m.operatorType in ( 'INDEPENDENT', 'PARENT' ) and in ( m.affiliationChoId_1, m.affiliationChoId_2, m.affiliationChoId_3, m.affiliationChoId_4 ) and m.operatorType in ( 'INDEPENDENT', 'PARENT' ) and in ( m.affiliationChoId_1, m.affiliationChoId_2, m.affiliationChoId_3, m.affiliationChoId_4 ) and m.choId = ), purchasing as ( select cho_id dataId, a.operatorName as text, datepart( year, purchasingMonth ) theYear, 'M_' + convert( varchar, datepart( month, purchasingMonth )) purchasingMonth, forecast_qty purchasingVolume from tbl_DW_OperatorAnalyzer a with (nolock) inner join members m on m.choId = a.cho_id and m.ownerId = a.ownerId and m.ownerType = a.ownerType where a.ownerId = and a.ownerType = and a.cho_id <> coalesce( a.agreementOwnerChoId, 0 ) and a.skuId = and a.prodId = and a.categoryId = and a.prodLine_id = ), crosstab as ( select * from purchasing pivot( sum( purchasingVolume ) for purchasingMonth in ( [M_1],[M_2],[M_3],[M_4],[M_5],[M_6],[M_7],[M_8],[M_9],[M_10],[M_11],[M_12] ) ) p ) select dataId, text, theYear , coalesce( M_1, 0 ) as M_1 , coalesce( M_2, 0 ) as M_2 , coalesce( M_3, 0 ) as M_3 , coalesce( M_4, 0 ) as M_4 , coalesce( M_5, 0 ) as M_5 , coalesce( M_6, 0 ) as M_6 , coalesce( M_7, 0 ) as M_7 , coalesce( M_8, 0 ) as M_8 , coalesce( M_9, 0 ) as M_9 , coalesce( M_10, 0 ) as M_10 , coalesce( M_11, 0 ) as M_11 , coalesce( M_12, 0 ) as M_12 from crosstab select dataId, text, theYear as year_#_period# , M_#_m# as #_period#_#_m# , 0 + M_#_m# as #_period#_total from qmd_rawDataAllPeriods where theYear = select * from qmd_crossTabData_TY order by ty_total desc select * from qmd_data order by ty_total desc