select fsl_choId from tbl_OPR_ClientOperators with (nolock) where ownerId = and fsltablecode = and operatorId = declare @currentPeriodStart datetime = ; declare @currentPeriodEnd datetime = ; declare @cho_id int = ; declare @priorPeriodStart datetime = dateadd( year, -1, @currentPeriodStart ); declare @priorPeriodEnd datetime = dateadd( year, -1, @currentPeriodEnd ); declare @periodTable table ( periodIndex int, periodDate datetime ); declare @periodDate datetime = @currentPeriodStart; while ( @periodDate <= @currentPeriodEnd ) begin insert into @periodTable ( periodIndex, periodDate ) values ( dateDiff( month, @currentPeriodStart, @periodDate ) + 1, @periodDate ); set @periodDate = dateadd( month, 1, @periodDate ); end; with operatorAnalyzer as ( select purchasingMonth, sum( forecast_qty ) forecast_qty, sum( forecast_amt ) forecast_amt from tbl_DW_OperatorAnalyzer#_tableSuffix# oa with (nolock) where oa.cho_Id = @cho_id and oa.skuId = and oa.sku = group by purchasingMonth ), currentPeriod as ( select forecast_qty as current_qty, forecast_amt as current_dollars, datediff( month, @currentPeriodStart, purchasingMonth ) + 1 as periodIndex, purchasingMonth from operatorAnalyzer where purchasingMonth between @currentPeriodStart and @currentPeriodEnd ), priorPeriod as ( select forecast_qty as prior_qty, forecast_amt as prior_dollars, datediff( month, @priorPeriodStart, purchasingMonth ) + 1 as periodIndex, purchasingMonth from operatorAnalyzer where purchasingMonth between @priorPeriodStart and @priorPeriodEnd ), sideBySide as ( select t.periodIndex, t.periodDate as purchasingMonth, c.current_qty, c.current_dollars, p.prior_qty, p.prior_dollars from @periodTable t left outer join currentPeriod c on c.periodIndex = t.periodIndex left outer join priorPeriod p on p.periodIndex = t.periodIndex ), data as ( select periodIndex, cast( year( purchasingMonth ) as varchar )as forecastyear, cast( month( purchasingMonth ) as varchar ) as foreastmonth, cast( month( purchasingMonth ) as varchar ) + '/' + cast( year( purchasingMonth ) as varchar ) as period, coalesce( current_qty, 0.0 ) as #_uom#_current, coalesce( current_dollars, 0.0 ) as dollars_current, coalesce( prior_qty, 0.0 ) as #_uom#_prior, coalesce( current_dollars, 0.0 ) as dollars_prior from sideBySide ) #forJSON( 'data', attributes.datastore, 'periodIndex' )#