select operatorType, choId, locationLevelPurchasing, hasSubGroups from tbl_DW_1fsOperators#_tableSuffix# with (nolock) where ownerId = and ownerType = and choid = ---> with allAgreements as ( select min( startDate ) startDate, max( endDate ) endDate from tbl_DW_OperatorAgreements#_tableSuffix# with (nolock) where ownerId = and ownerType = and cho_id = ) select * from allAgreements where between startDate and endDate with blank as ( select ownerId, ownerType, cho_id as choId, operatorName, min( purchasingMonth ) firstPurchasingMonth, max( purchasingMonth ) lastPurchasingMonth, 1 as activeLocation, 0 as rollup_cho_id, 'I' as rollup_cho_type, '' as rollup_operatorName, '' as rollup_operatorAddress, '' as rollup_operatorCity, '' as rollup_operatorState, '' as rollup_operatorZipCode from tbl_DW_OperatorAnalyzer#_tableSuffix# with (nolock) where ownerId = and ownerType= and agreementOwnerChoId = and cho_id = 0 and sourceType = group by ownerId, ownerType, cho_id, operatorName ), subGroups as ( select sg.subGroupChoId from tbl_DW_1fsSubGroups#_tablesuffix# sg with (nolock) where sg.ownerId = and sg.ownerType = and sg.memberGroupChoId = and sg.parentGroupChoId = and ( nullif( sg.parentGroupChoId, 0 ) is null ) and sg.memberGroupChoId = ---> and ( nullif( sg.parentGroupChoId, 0 ) is null ) and ( nullif( sg.parentGroupChoId, 0 ) is null or sg.parentGroupChoId = ) ---> ), members as ( select m.choId, m.ownerId, m.ownerType, m.operatorName, datefromParts( 1966, 6, 1 ) firstDate, dateFromParts( 3000, 12, 31 ) lastDate, 1 activeLocation, p.choId as rollup_cho_id, 'O' as rollup_cho_type, p.operatorName as rollup_operatorName, p.operatorAddress as rollup_operatorAddress, p.operatorCity as rollup_operatorCity, p.operatorState as rollup_operatorState, p.operatorZipCode as rollup_operatorZipCode from tbl_DW_1fsOperators#_tableSuffix# m with(nolock) inner join tbl_DW_1FSOperators#_tableSuffix# p with (nolock) on p.ownerId= m.ownerId and p.ownerType= m.ownerType and p.choId = m.parentChoId where m.ownerId = and m.ownerType = and m.operatorType = 'UNIT' and m.parentChoId = select m.ownerId, m.ownerType, m.memberCHOId as choId, m.memberName operatorName, datefromparts( Year( m.affiliationDate ), month( m.affiliationDate ), 1 ) firstDate, dateadd( day, -1, dateadd( month, 1, datefromParts( year(coalesce( m.deaffiliationDate, datefromparts( 3000, 12, 31 ))), month(coalesce( m.deaffiliationDate, datefromparts( 3000, 12, 31 ))), 1 ) )) lastDate, case when m.deaffiliationDate is not null then 0 else 1 end activeLocation, mg.choId as rollup_cho_id, case when mg.operatorType = 'CHAINHQ' then 'O' else left( mg.operatorType, 1 ) end as rollup_cho_type, mg.operatorName as rollup_operatorName, mg.operatorAddress as rollup_operatorAddress, mg.operatorCity as rollup_operatorCity, mg.operatorState as rollup_operatorState, mg.operatorZipCode as rollup_operatorZipCode from tbl_DW_1fsoperatorMemberships#_tableSuffix# m with (nolock) inner join tbl_DW_1fsOperators#_tableSuffix# mm with (nolock) on mm.ownerId = m.ownerId and mm.ownerType = m.ownerType and mm.choId = m.memberChoId inner join tbl_DW_1fsOperators#_tableSuffix# mg with (nolock) on mg.ownerId = m.ownerId and mg.ownerType = m.ownerType and mg.choId = m.memberGroupChoId and mg.choId not in ( select cho_id from tbl_DW_OperatorAgreements#_tableSuffix# where ownerId = and ownerType = ) and mg.operatorType in ( ) where m.ownerId = and m.ownerType= and m.memberGroupChoId = and mm.operatorType = 'UNIT' and mm.operatorType = 'INDEPENDENT' and mm.operatorType in ( 'UNIT', 'INDEPENDENT' ) and 1 = 2 and m.memberChoId in ( select memberChoId from tbl_DW_1fsOperatorMemberships#_tablesuffix# m with (nolock) where m.ownerId = and m.ownertype = and m.memberGroupChoId = ) and m.memberGroupChoId in ( select subGroupChoId from subGroups ) union all select ownerId, ownerType, choId, operatorName, firstPurchasingMonth as firstDate, dateadd( day, -1, dateadd( month, 1, lastPurchasingMonth )) as lastDate, activeLocation, rollup_cho_id, rollup_cho_type, rollup_operatorName, rollup_operatorAddress, rollup_operatorCity, rollup_operatorState, rollup_operatorState from blank select m.choId, m.ownerId, m.ownerType, operatorName, datefromParts( 1966, 6, 1 ) firstDate, dateFromParts( 3000, 12, 31 ) lastDate, 1 activeLocation, m.choId rollup_cho_id, m.operatorType rollup_cho_type, m.operatorName rollup_operatorName, m.operatorAddress rollup_operatorAddress, m.operatorCity rollup_operatorCity, m.operatorState rollup_operatorState from tbl_DW_1fsOperators#_tableSuffix# m with(nolock) where m.ownerId = and m.ownerType = and m.choId = ---> ), x as ( select 'M' + convert( varchar, datediff( month, dateadd( month, -23, '#_lastPurchasingMonth#' ), a.purchasingMonth) + 1 ) mnth, sum( a.#_qtyCol# ) qty, ( select count ( distinct aa.agreementOwnerName ) from tbl_DW_OperatorAnalyzer#_tableSuffix# aa with (nolock) where aa.ownerId = #attributes.ownerId# and aa.ownerType = '#attributes.ownerType#' and aa.cho_id = ma.rollup_cho_id and aa.cho_id = a.cho_id and aa.purchasingMonth <= dateadd( month, 0, '#_lastPurchasingMonth#' ) and aa.purchasingMonth >= dateadd( month, -23, '#_lastPurchasingMonth#' ) ) 0 as agreementOwnerCount , #PreserveSingleQuotes( _selectColumns )# from tbl_DW_OperatorAnalyzer#_tableSuffix# a with (nolock) innerleft outer join tbl_DW_1fsOperators#_tableSuffix# ao with (nolock) on ao.ownerId= a.ownerId and ao.ownerType = a.ownerType and ao.choId = a.agreementOwnerChoId and a.agreementOwnerChoId in ( ) inner join members m on m.ownerId = a.ownerId and m.ownerType = a.ownerType and m.choId = a.cho_id where a.ownerId = #attributes.ownerId# and a.ownerType = '#attributes.ownerType#' and a.territoryId in ( ) and a.sourceType = and a.businessOwnerUserId in ( ) and a.businessOwnerUserId in ( ) and a.cho_id = and a.rollup_cho_type in ( 'I', 'O' ) and a.cho_type = 'O' and a.cho_type = 'I' and a.cho_type = 'G' and a.cho_type = 'C' and a.cho_type in ( 'I', 'U' ) and ( 1 = 2 or ( not ( coalesce( a.agreementOwnerChoId, 0 ) = nullif( a.cho_id, 0 ))) or ( coalesce( a.agreementOwnerChoId, 0 ) = nullif( a.cho_id, 0 )) or 1 = 1 ) and ( not coalesce( a.requestId, 0 ) = 0 ) and ( a.cho_type = 'G' ) and ( a.cho_type = 'C' ) and ( a.cho_type = 'I' ) and ( a.cho_type = 'O' ) and ( a.cho_type in ( 'O', 'I' ) ) and ( a.cho_type in ( 'C', 'G' ) ) and ( a.cho_type in ( 'U', 'I' ) ) and ( a.cho_type = 'U' ) and a.cho_id in ( 15, 251, 610, 292, 612, 88, 155 ) and ( a.cho_type = 'G' and a.segmentId = 54 ) and ( a.cho_type = 'G' and a.segmentId = 55 ) and ( a.cho_type = 'G' and a.segmentId = 56 ) and a.agreementOwnerChoId = and a.territoryId in ( ) and ( 1 = 2 or ( a.dstId = a.parentDstId = 1 = 2 ) ) and ( 1 = 2 or ( a.mainSegment = a.operatorSegment = ) ) and a.classificationId in ( ) and a.brandId in ( ) and a.skuId in ( ) and a.prodId in ( ) and a.categoryId in ( ) and a.prodLine_id in ( ) and a.budgetCategoryId in ( ) and a.cho_id = and a.operatorName like and ( a.operatorState in ( ) ) and a.purchasingMonth <= dateadd( month, 0, '#_lastPurchasingMonth#' ) and a.purchasingMonth >= dateadd( month, -(#url.windowMonths-1#), '#_lastPurchasingMonth#' ) and a.purchasingMonth >= dateadd( month, -23, '#_lastPurchasingMonth#' ) and 1 = 2 and a.purchasingMonth >= m.firstDate and a.purchasingMonth <= m.lastDate and a.requestId > 0 and ( coalesce( ao.locationLevelPurchasing, 'N' ) = 'N' or a.agreementOwnerChoId <> a.cho_id ) group by a.purchasingMonth , #_groupByColumns# ) , result as ( select agreementOwnerCount, #_includeColumns#, coalesce( #_mc#, 0 ) as #_mc#, coalesce( #_mc#, 0 ) + 0 as Y1, coalesce( #_mc#, 0 ) as #_mc#, coalesce( #_mc#, 0 ) + 0 as Y2 from ( select * from x pivot( sum( qty ) for mnth 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 ) xx ) select #_includeColumns# ,#lcase( _mc )# ,#lcase( _mc )# ,y2, y1 ,y2 - y1 as difference, round( case when y1 > 0 then ( y2 - y1 ) / convert( float, y1 ) else 0 end, 2 ) variance from result order by y2 desc -- #qmd_cho.locationLevelPurchasing# #qmd_cho.operatorType# , um as ( select distinct fsl_choId as memberChoId from tbl_OPR_CLientOperators with (nolock) where fsltablecode = 'CHO' and ownerId = and crmActive = 'Y' and fsl_choId > 0 ) , um as ( select distinct operatorChoId as memberChoId from tbl_DW_1fsUniverse#_tableSuffix# with (nolock) where ownerType = and ownerId = and parentChoId = and operatorType = 'UNIT' ) select u.operatorChoId as ws_cho_id, u.operatorChoId as cho_id, u.operatorName ws_operatorName, u.operatorAddress ws_operatorAddress, u.operatorCity ws_operatorCity, u.operatorState ws_operatorState, u.operatorZipCode ws_operatorZipCode, u.operatorTerritory ws_territoryName, u.averageClientVolume ws_averageClientVolume, u.mainSegment as ws_mainSegment, u.subSegment as ws_subSegment, u.operatorCounty as ws_operatorCounty, u.operatorType as ws_operatorType from tbl_DW_1fsUniverse#_tableSuffix# u with (nolock) and m.deaffiliationDate is null and m.ownerId = u.ownerId and m.ownerType = u.ownertype ---> inner join um as m on m.memberChoId = u.operatorChoId where u.averageClientVolume > 0 and ( 1 = 2 or ( u.mainSegment = u.operatorSegment = ) ) and u.operatorTerritoryId in ( ) and u.lastMonth >= dateadd( month, -(#url.windowMonths - 1#), '#_lastPurchasingMonth#' ) and u.ownerId = and u.ownerType = and not exists ( select * from tbl_DW_OperatorAnalyzer#_tableSuffix# o with (nolock) where o.cho_Id = u.operatorChoId and o.purchasingMonth >= u.lastMonth and o.ownerId = u.ownerId and o.ownerType = u.ownerType ) and 1 = 2 order by ws_averageClientVolume desc for JSON AUTO, INCLUDE_NULL_VALUES