with purchasing as ( select a.skuId dataId, a.sku + ' - ' + a.skuDesc as text, 'M' + convert( varchar, datediff( month, dateadd( month, -23, '#_lastPurchasingMonth#' ), a.purchasingMonth) + 1 ) as purchasingMonth, a.forecast_qty purchasingVolume from tbl_DW_OperatorAnalyzer_#client.ownertype##client.ownerid# a with (nolock) where a.ownerId = and a.ownerType = and a.cho_id = and a.purchasingMonth <= dateadd( month, 0, '#_lastPurchasingMonth#' ) and a.purchasingMonth >= dateadd( month, -23, '#_lastPurchasingMonth#' ) and a.skuId = and a.prodId = and a.categoryId = and a.prodLine_id = ), crosstab as ( select * from purchasing pivot( sum( purchasingVolume ) for purchasingMonth in ( [M1],[M2],[M3],[M4],[M5],[M6],[M7],[M8],[M9],[M10],[M11],[M12], [M13],[M14],[M15],[M16],[M17],[M18],[M19],[M20],[M21],[M22],[M23],[M24] ) ) p ), interim as ( select dataId, text , coalesce( M1, 0 ) as M1 , coalesce( M2, 0 ) as M2 , coalesce( M3, 0 ) as M3 , coalesce( M4, 0 ) as M4 , coalesce( M5, 0 ) as M5 , coalesce( M6, 0 ) as M6 , coalesce( M7, 0 ) as M7 , coalesce( M8, 0 ) as M8 , coalesce( M9, 0 ) as M9 , coalesce( M10, 0 ) as M10 , coalesce( M11, 0 ) as M11 , coalesce( M12, 0 ) as M12 , coalesce( M13, 0 ) as M13 , coalesce( M14, 0 ) as M14 , coalesce( M15, 0 ) as M15 , coalesce( M16, 0 ) as M16 , coalesce( M17, 0 ) as M17 , coalesce( M18, 0 ) as M18 , coalesce( M19, 0 ) as M19 , coalesce( M20, 0 ) as M20 , coalesce( M21, 0 ) as M21 , coalesce( M22, 0 ) as M22 , coalesce( M23, 0 ) as M23 , coalesce( M24, 0 ) as M24 from crosstab ) select *, m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11 + m12 as y1, m13 + m14 + m15 + m16 + m17 + m18 + m19 + m20 + m21 + m22 + m23 + m24 as y2 from interim order by y2 desc with members as ( select m.ownerId, m.ownerType, m.choId, m.operatorName from tbl_DW_1FSOperators_#client.ownertype##client.ownerid# 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 case when m.operatorType = 'UNIT' then coalesce( m.parentChoId, 1 ) else m.choId end as dataId, case when m.operatorType = 'UNIT' then coalesce( m.parentName, 'Unknown Chain HQ' ) else m.operatorName end as text, a.cho_id dataId, a.operatorName as text, 'M' + convert( varchar, datediff( month, dateadd( month, -23, '#_lastPurchasingMonth#' ), a.purchasingMonth) + 1 ) as purchasingMonth, a.forecast_qty purchasingVolume from tbl_DW_OperatorAnalyzer_#client.ownertype##client.ownerid# a with (nolock) inner join tbl_DW_1fsOperators_#client.ownertype##client.ownerid# m with (nolock) on m.ownerId = a.ownerId and m.ownerType = a.ownertype and m.choId = a.cho_id 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.purchasingMonth <= dateadd( month, 0, '#_lastPurchasingMonth#' ) and a.purchasingMonth >= dateadd( month, -23, '#_lastPurchasingMonth#' ) and a.agreementOwnerChoId = and a.cho_id <> a.agreementOwnerChoId 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 ( [M1],[M2],[M3],[M4],[M5],[M6],[M7],[M8],[M9],[M10],[M11],[M12], [M13],[M14],[M15],[M16],[M17],[M18],[M19],[M20],[M21],[M22],[M23],[M24] ) ) p ), interim as ( select dataId, text , coalesce( M1, 0 ) as M1 , coalesce( M2, 0 ) as M2 , coalesce( M3, 0 ) as M3 , coalesce( M4, 0 ) as M4 , coalesce( M5, 0 ) as M5 , coalesce( M6, 0 ) as M6 , coalesce( M7, 0 ) as M7 , coalesce( M8, 0 ) as M8 , coalesce( M9, 0 ) as M9 , coalesce( M10, 0 ) as M10 , coalesce( M11, 0 ) as M11 , coalesce( M12, 0 ) as M12 , coalesce( M13, 0 ) as M13 , coalesce( M14, 0 ) as M14 , coalesce( M15, 0 ) as M15 , coalesce( M16, 0 ) as M16 , coalesce( M17, 0 ) as M17 , coalesce( M18, 0 ) as M18 , coalesce( M19, 0 ) as M19 , coalesce( M20, 0 ) as M20 , coalesce( M21, 0 ) as M21 , coalesce( M22, 0 ) as M22 , coalesce( M23, 0 ) as M23 , coalesce( M24, 0 ) as M24 from crosstab ) select *, m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11 + m12 as y1, m13 + m14 + m15 + m16 + m17 + m18 + m19 + m20 + m21 + m22 + m23 + m24 as y2 from interim order by y2 desc with purchasing as ( select a.skuId dataId, a.sku + ' - ' + a.skuDesc as text, 'M' + convert( varchar, datediff( month, dateadd( month, -23, '#_lastPurchasingMonth#' ), a.purchasingMonth) + 1 ) as purchasingMonth, a.forecast_qty purchasingVolume from tbl_DW_OperatorAnalyzer_#client.ownertype##client.ownerid# a with (nolock) where a.ownerId = and a.ownerType = and a.cho_id = and a.purchasingMonth <= dateadd( month, 0, '#_lastPurchasingMonth#' ) and a.purchasingMonth >= dateadd( month, -23, '#_lastPurchasingMonth#' ) and a.skuId = and a.prodId = and a.categoryId = and a.prodLine_id = ), crosstab as ( select * from purchasing pivot( sum( purchasingVolume ) for purchasingMonth in ( [M1],[M2],[M3],[M4],[M5],[M6],[M7],[M8],[M9],[M10],[M11],[M12], [M13],[M14],[M15],[M16],[M17],[M18],[M19],[M20],[M21],[M22],[M23],[M24] ) ) p ), interim as ( select dataId, text , coalesce( M1, 0 ) as M1 , coalesce( M2, 0 ) as M2 , coalesce( M3, 0 ) as M3 , coalesce( M4, 0 ) as M4 , coalesce( M5, 0 ) as M5 , coalesce( M6, 0 ) as M6 , coalesce( M7, 0 ) as M7 , coalesce( M8, 0 ) as M8 , coalesce( M9, 0 ) as M9 , coalesce( M10, 0 ) as M10 , coalesce( M11, 0 ) as M11 , coalesce( M12, 0 ) as M12 , coalesce( M13, 0 ) as M13 , coalesce( M14, 0 ) as M14 , coalesce( M15, 0 ) as M15 , coalesce( M16, 0 ) as M16 , coalesce( M17, 0 ) as M17 , coalesce( M18, 0 ) as M18 , coalesce( M19, 0 ) as M19 , coalesce( M20, 0 ) as M20 , coalesce( M21, 0 ) as M21 , coalesce( M22, 0 ) as M22 , coalesce( M23, 0 ) as M23 , coalesce( M24, 0 ) as M24 from crosstab ) select *, m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9 + m10 + m11 + m12 as y1, m13 + m14 + m15 + m16 + m17 + m18 + m19 + m20 + m21 + m22 + m23 + m24 as y2 from interim order by y2 desc with members as ( select m.ownerId, m.ownerType, m.choId, m.operatorName from tbl_DW_1FSOperators_#client.ownertype##client.ownerid# 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 case when m.operatorType = 'UNIT' then coalesce( m.parentChoId, 1 ) else m.choId end as dataId, case when m.operatorType = 'UNIT' then coalesce( m.parentName, 'Unknown Chain HQ' ) else m.operatorName end as text, 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_#client.ownertype##client.ownerid# a with (nolock) inner join tbl_DW_1fsOperators_#client.ownertype##client.ownerid# m with (nolock) on m.ownerId = a.ownerId and m.ownerType = a.ownertype and m.choId = a.cho_id 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.agreementOwnerChoId = and a.cho_id <> a.agreementOwnerChoId 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