select top 1 purchasingMonth as lastPurchasingMonth from tbl_DW_PurchasingMonths#_tableSuffix# with (nolock) order by purchasingMonth desc with dst as ( select distinct dstId from tbl_DW_1fsDistributors#_tableSuffix# with (nolock) where ownerId = and ownerType = and dstId = and parentDstId = ), dst as ( select distinct dstId from tbl_DW_1fsDistributors#_tableSuffix# with (nolock) where ownerId = and ownerType = and territoryId in ( ) and dstType = 'BRANCH' ), 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#_tableSuffix# 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.dstId in ( select dstId from dst ) and a.skuId in ( ) and a.prodId in ( ) and a.categoryId in ( ) and a.prodLine_id in ( ) ), 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( #_mc#, 0 ) as #_mc# , coalesce( #_mc#, 0 ) as #_mc# from crosstab ) select *, coalesce( #_mc#, 0 ) + 0 as y1, coalesce( #_mc#, 0 ) + 0 as y2 from interim order by y2 desc with members as ( select ownerId, ownerType, choId, operatorName, datefromParts( 1966, 6, 1 ) firstDate, datefromparts(3000, 12, 31 ) lastDate, 1 as activeLocation from tbl_DW_1fsoperators#_tableSuffix# with (nolock) where parentChoId = and ownerId = and ownerType= select ownerId, ownerType, memberCHOId as choId, memberName operatorName, datefromparts( Year( affiliationDate ), month( affiliationDate ), 1 ) firstDate, dateadd( day, -1, dateadd( month, 1, datefromParts( year(coalesce( deaffiliationDate, datefromparts( 3000, 12, 31 ))), month(coalesce( deaffiliationDate, datefromparts( 3000, 12, 31 ))), 1 ) )) lastDate, case when deaffiliationDate is not null then 0 else 1 end activeLocation from tbl_DW_1fsoperatorMemberships#_tableSuffix# with (nolock) where ownerId = and ownerType= and memberGroupChoId = select ownerId, ownerType, choId, operatorName, datefromParts( 1966, 6, 1 ) firstDate, datefromparts(3000, 12, 31 ) lastDate, 1 as activeLocation from tbl_DW_1fsoperators#_tableSuffix# with (nolock) where choId = and ownerId = and ownerType= ), 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, convert( datetime, null ) as firstActive, convert( datetime, null ) as lastActive, a.cho_id dataId, a.operatorName as text, case when m.firstDate = '06/01/1966' then '' else format( m.firstDate, 'MM/dd/yyyy' ) end as firstActive, case when m.lastDate = '12/31/3000' then '' else format( m.lastDate, 'MM/dd/yyyy' ) end as lastActive, 'M' + convert( varchar, datediff( month, dateadd( month, -23, '#_lastPurchasingMonth#' ), a.purchasingMonth) + 1 ) as purchasingMonth, a.forecast_qty purchasingVolume from tbl_DW_OperatorAnalyzer#_tableSuffix# a with (nolock) inner join tbl_DW_1fsOperators#_tableSuffix# 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.purchasingMonth >= m.firstDate and a.purchasingMonth <= m.lastDate 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, firstActive, lastActive , 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