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 select c.name from sys.columns c inner join sys.tables t on t.object_id = c.object_Id where t.name = 'tbl_DW_DistributorSales#_tableSuffix#' and c.is_identity = 0 and c.name not in ( 'cases', 'lbs', 'dollars', 'alt', 'commission' ) and c.name not in ( 'active_pipeline_cases','active_pipeline_lbs','active_pipeline_dollars','sold_pipeline_cases','sold_pipeline_lbs','sold_pipeline_dollars') order by c.column_id /* -- #_currentSalesPeriodStart# - #_currentSalesPeriodEnd# -- #_priorSalesPeriodStart# - #_priorSalesPeriodEnd# */ declare @periodToProrate datetime; declare @prorate float; declare @currentSalesPeriodStart datetime; declare @currentSalesPeriodEnd datetime; declare @priorSalesPeriodStart datetime; declare @priorSalesPeriodEnd datetime; set @periodToProrate = ; set @prorate = ; set @currentSalesPeriodStart = ; set @currentSalesPeriodEnd = ; set @priorSalesPeriodStart = ; set @priorSalesPeriodEnd = ; with _formattedData as ( select #_analyzerColumns#, cases, lbs, dollars, alt, commission, active_pipeline_cases, active_pipeline_lbs, active_pipeline_dollars, sold_pipeline_cases, sold_pipeline_lbs, sold_pipeline_dollars, sku + ' - ' + skuDesc as skuFull, product + ' - ' + shortDesc as productFull, catnum + ' - ' + category as categoryFull, productLineCode + ' - ' + productLine as productLineFull, 'R2_P' + cast (datediff( month, @currentSalesPeriodStart, salesPeriod ) + 1 as varchar) as period, floor( 10191817 * sqrt( cdr_recordId * log10( cdr_recordId ))) as cdr_recordId_tk, case when parent_cdr_recordId is not null then floor( 10191817 * sqrt( parent_cdr_recordId * log10( parent_cdr_recordId ))) else null end as parent_cdr_recordId_tk, accountOwner_firstName + ' ' + accountOwner_lastName as accountOwner_fullName, case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName, concat_ws( '; ', etl.buyingGroup, etl.buyingGroup2, etl.buyingGroup3 ) as allBuyingGroups from tbl_DW_DistributorSales#_tableSuffix# etl with (nolock) where salesPeriodType = 'MONTH' and ( salesPeriod between @currentSalesPeriodStart and @currentSalesPeriodEnd ) and territoryId in ( ) union all select #_analyzerColumns#, cases * case when salesPeriod = @periodToProrate then @prorate when salesPeriod between @periodToProRate and @priorSalesPeriodEnd then 0.0 else 1.0 end as cases, lbs * case when salesPeriod = @periodToProrate then @prorate when salesPeriod between @periodToProRate and @priorSalesPeriodEnd then 0.0 else 1.0 end as lbs, dollars * case when salesPeriod = @periodToProrate then @prorate when salesPeriod between @periodToProRate and @priorSalesPeriodEnd then 0.0 else 1.0 end as dollars, alt * case when salesPeriod = @periodToProrate then @prorate when salesPeriod between @periodToProRate and @priorSalesPeriodEnd then 0.0 else 1.0 end as alt, commission * case when salesPeriod = @periodToProrate then @prorate when salesPeriod between @periodToProRate and @priorSalesPeriodEnd then 0.0 else 1.0 end as commission, active_pipeline_cases, active_pipeline_lbs, active_pipeline_dollars, sold_pipeline_cases, sold_pipeline_lbs, sold_pipeline_dollars, sku + ' - ' + skuDesc as skuFull, product + ' - ' + shortDesc as productFull, catnum + ' - ' + category as categoryFull, productLineCode + ' - ' + productLine as productLineFull, 'R1_P' + cast (datediff( month, @priorSalesPeriodStart, salesPeriod ) + 1 as varchar) as period, floor( 10191817 * sqrt( cdr_recordId * log10( cdr_recordId ))) as cdr_recordId_tk, case when parent_cdr_recordId is not null then floor( 10191817 * sqrt( parent_cdr_recordId * log10( parent_cdr_recordId ))) else null end as parent_cdr_recordId_tk, accountOwner_firstName + ' ' + accountOwner_lastName as accountOwner_fullName, case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName, concat_ws( '; ', etl.buyingGroup, etl.buyingGroup2, etl.buyingGroup3 ) as allBuyingGroups from tbl_DW_DistributorSales#_tableSuffix# etl with (nolock) where salesPeriodType = 'MONTH' and ( salesPeriod between @priorSalesPeriodStart and @priorSalesPeriodEnd ) and territoryId in ( ) ) , _filtered as ( select * from _formattedData where #applyFilter( attributes.datastore.filter )# ) , _summary as ( select #_groupColumns#, sum( cases ) as cases, sum( lbs) as lbs, sum( dollars ) as dollars, sum( alt ) as alt, sum( commission ) as commission, sum( active_pipeline_cases ) as active_pipeline_cases, sum( active_pipeline_lbs ) as active_pipeline_lbs, sum( active_pipeline_dollars ) as active_pipeline_dollars, sum( sold_pipeline_cases ) as sold_pipeline_cases, sum( sold_pipeline_lbs ) as sold_pipeline_lbs, sum( sold_pipeline_dollars ) as sold_pipeline_dollars from _filtered group by #_groupColumns# ) , _dataPivot1 as ( select #_selectColumns# ,sum( case when period = 'R1_P#_px#' then #_uom# else null end ) as R1_P#_px# ,sum( case when period = 'R2_P#_px#' then #_uom# else null end ) as R2_P#_px# ,sum( case when period = 'R1_P#_px#' then active_pipeline_#_uom# else null end ) as A1_P#_px# ,sum( case when period = 'R2_P#_px#' then active_pipeline_#_uom# else null end ) as A2_P#_px# ,sum( case when period = 'R1_P#_px#' then sold_pipeline_#_uom# else null end ) as S1_P#_px# ,sum( case when period = 'R2_P#_px#' then sold_pipeline_#_uom# else null end ) as S2_P#_px# from [_summary] with (nolock) group by #_selectColumns# ) , _dataPivot as ( select *, +coalesce( R1_P#_px#, 0.0 ) as R1, +coalesce( R2_P#_px#, 0.0 ) as R2, +coalesce( A1_P#_px#, 0.0 ) as A1, +coalesce( A2_P#_px#, 0.0 ) as A2, +coalesce( S1_P#_px#, 0.0 ) as S1, +coalesce( S2_P#_px#, 0.0 ) as S2 from _dataPivot1 ) sum( #_uom# ) for period in ( ,R1_P#_px#,R2_P#_px# ) ) _pivot ) _pivotted ) ---> , _preData as ( select #_selectColumns# ,sum( R1_P#_px# ) as R1_P#_px# ,sum( R2_P#_px# ) as R2_P#_px# ,sum( A1_P#_px# ) as A1_P#_px# ,sum( A2_P#_px# ) as A2_P#_px# ,sum( S1_P#_px# ) as S1_P#_px# ,sum( S2_P#_px# ) as S2_P#_px# , sum( R1 ) as R1, sum( R2 ) as R2, sum( A1 ) as A1, sum( A2 ) as A2, sum( S1 ) as S1, sum( S2 ) as S2 from _dataPivot group by #_selectColumns# ) , _data as ( select *, R2 - R1 as variance, case when R1 != 0 then ( R2 - R1 ) / R1 else null end as variancePct, case when sum( R1 ) over() != 0 then R1 / sum( R1 ) over() else null end as pctOfTotal_R1, case when sum( R2 ) over() != 0 then R2 / sum( R2 ) over() else null end as pctOfTotal_R2 from _preData ) , _return as ( select * from _data where #applyFilter( attributes.datastore.resultFilter )# #attributes.datastore.sqlOrderBy# ) , _result as ( select ( select count(*) from _data ) as totalCount, ( select sum( #_ts.selector# ) from _data ) ( null ) as [totalSummary_#_tsx-1#], ( select * from _return #attributes.datastore.sqlOrderBy# for json auto, include_null_values ) as data ) select * from _result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER