select top 1 fiscalYear, periodType, periodIndex, fiscalStartMonth, percentComplete from tbl_CRM_FiscalPeriods with (nolock) where ownerId = and ownerType = and percentComplete > 0 and periodType = 'M' and startDate <= order by fiscalStartMonth desc declare @periodToProrate datetime; declare @prorate float; set @periodToProrate = ; set @prorate = ; with _formattedData as ( select fiscalYear, salesPeriod, cases, lbs, dollars, alt, 'R2_P' + cast (datediff( month, , salesPeriod ) + 1 as varchar) as period from tbl_DW_DistributorSales#_tableSuffix# with (nolock) where 1 = 1 and salesPeriodType = 'MONTH' and ( salesPeriod between and ) and territoryId in ( ) and accountOwner_fspro_userId in ( ) and mfr_id in ( ) and productHierarchyPath like and ( 1 = 2 or productSetTags like ) and ( 1 = 2 or cdr_dstPath like ) and budgetCategoryId in ( ) and ( 1 = 2 or territoryPath like ) and discontinued = 'N' and coalesce( privateLabelType, '' ) != 'OPR' and accountOwner_fspro_userId in ( ) union all select fiscalYear, salesPeriod, cases * case when salesPeriod = @periodToProrate then @prorate else 1.0 end as cases, lbs * case when salesPeriod = @periodToProrate then @prorate else 1.0 end as lbs, dollars * case when salesPeriod = @periodToProrate then @prorate else 1.0 end as dollars, alt * case when salesPeriod = @periodToProrate then @prorate else 1.0 end as alt, 'R1_P' + cast (datediff( month, , salesPeriod ) + 1 as varchar) as period from tbl_DW_DistributorSales#_tableSuffix# with (nolock) where 1 = 1 and salesPeriodType = 'MONTH' and ( salesPeriod between and ) and territoryId in ( ) and accountOwner_fspro_userId in ( ) and mfr_id in ( ) and productHierarchyPath like and ( 1 = 2 or productSetTags like ) and ( 1 = 2 or cdr_dstPath like ) and budgetCategoryId in ( ) and ( 1 = 2 or territoryPath like ) and discontinued = 'N' and coalesce( privateLabelType, '' ) != 'OPR' and accountOwner_fspro_userId in ( ) and accountOwner_ownerType = ), _summary as ( select period, sum( cases ) cases, sum( dollars ) dollars, sum( lbs ) lbs, sum( alt ) as alt from _formattedData group by period ), _dataPivot as ( select ,R1_P#_px#,R2_P#_px#, +coalesce( R1_P#_px#, 0.0 ) as R1, +coalesce( R2_P#_px#, 0.0 ) as R2 from ( select * from _summary pivot( sum( #_uom# ) for period in ( ,R1_P#_px#,R2_P#_px# ) ) _pivot ) _pivotted ), _preData as ( select ,sum( R1_P#_px# ) as R1_P#_px#, sum( R2_P#_px# ) as R2_P#_px# , sum( R1 ) as R1, sum( R2 ) as R2 from _dataPivot ), _data as ( select *, '#_uom#' as uom, R2 - R1 as variance, case when R1 != 0 then ( R2 - R1 ) / R1 else null end as variancePct from _preData ) select * from _data for JSON PATH, INCLUDE_NULL_VALUES