with #_period# as ( select cho_id, operatorName, sum( forecast_qty )0 as basePeriodVolume, sum( forecast_qty )0 as priorPeriodVolume from tbl_DW_OperatorAnalyzer#_tableSuffix# with (nolock) where ownerId = and ownerType = and territoryId in ( ) and ( cho_type = 'G' and segmentId = 54 ) and ( cho_type = 'G' and segmentId = 55 ) and ( cho_type = 'G' and segmentId = 56 ) and ( cho_type = 'C' ) and ( a.cho_type = 'I' ) and ( a.cho_type = 'O' ) and ( a.cho_type in ( 'O', 'I' ) ) and territoryId = and segmentId = and skuId = and prodId = and categoryId = and prodLine_id = and purchasingMonth >= and purchasingMonth <= and 1 = 2 and 1 = 2 group by cho_id, operatorName ), allData as ( select cho_id, operatorName, priorPeriodVolume, basePeriodVolume from base union all select cho_id, operatorName, priorPeriodVolume, basePeriodVolume from prior ) select cho_id, operatorName, sum( priorPeriodVolume ) as priorPeriodVolume, sum( basePeriodVolume ) as basePeriodVolume, sum( basePeriodVolume ) - sum( priorPeriodVolume ) as volumeChange, case when sum( priorPeriodVolume ) > 0 then round( (sum( basePeriodVolume ) - sum( priorPeriodVolume )) / sum( priorPeriodVolume ), 1 ) else 0.0 end volumeVariance from allData group by cho_id, operatorName order by basePeriodVolume desc