---> DECLARE @asofDate DATE = GETDATE(); SELECT 'asofDate' = COALESCE(lastInvoiceDate, GETDATE()) FROM tbl_CRM_FiscalCalendar AS cal WITH (nolock) WHERE cal.ownerId = AND cal.ownerType = AND cal.startDate <= @asofDate AND @asofDate <= cal.endDate DECLARE @asofDate DATE = ; SELECT * FROM tbl_CRM_FiscalCalendar AS cal WITH (nolock) WHERE cal.ownerId = AND cal.ownerType = AND cal.startDate <= @asofDate AND @asofDAte <= cal.endDate DECLARE @dateWithinPeriod DATE = ; SELECT top 1 fiscalYear, periodType, periodIndex, fiscalStartMonth, percentComplete FROM tbl_CRM_FiscalPeriods with (nolock) WHERE ownerId = AND ownerType = AND periodType = 'M' AND startDate <= @dateWithinPeriod AND @dateWithinPeriod <= endDate DECLARE @dateWithinPeriod DATE = ; SELECT top 1 fiscalYear, periodType, periodIndex, fiscalStartMonth, percentComplete FROM tbl_CRM_FiscalPeriods with (nolock) WHERE ownerId = AND ownerType = AND periodType = 'M' AND startDate <= @dateWithinPeriod AND @dateWithinPeriod <= endDate DECLARE @dateWithinPeriod DATE = ; SELECT top 1 fiscalYear, periodType, periodIndex, fiscalStartMonth, percentComplete FROM tbl_CRM_FiscalPeriods with (nolock) WHERE ownerId = AND ownerType = AND periodType = 'M' AND startDate <= @dateWithinPeriod AND @dateWithinPeriod <= endDate /* *** TY Month Range Calendar Indices: #_currentSalesPeriodStart# - #_currentSalesPeriodEnd# LY Month Range Calendar Incides: #_priorSalesPeriodStart# - #_priorSalesPeriodEnd# LY Period to Prorate: #_priorSalesPeriodToProrate# (TY is this + 1 year) Prorate: #_priorSalesProrate# AsOf Date: #attributes.datastore.objectParams.asofDate# *** */ declare @prevPeriodToProrate datetime = ; declare @currPeriodToProrate datetime = DATEADD(year,1,@prevPeriodToProrate); declare @prorate float = ; declare @asofDate datetime = ; WITH performance_dxFormatted AS ( SELECT 'calendarMonthStartDate' = tpf.salesPeriod ,tpf.territoryId, tpf.territoryPath, tpf.territoryName ,'regionName' = COALESCE(tpf.regionName,tpf.territoryName) ,'divisionName' = COALESCE(tpf.divisionName,tpf.regionName,tpf.territoryName) ,'regionPath' = CASE WHEN regionName IS NULL THEN territoryPath ELSE LEFT( territoryPath, LEN(territoryPath) - LEN('/') - LEN(territoryName) ) END ,'divisionPath' = CASE WHEN regionName IS NULL THEN territoryPath WHEN divisionName IS NULL THEN LEFT( territoryPath, LEN(territoryPath) - LEN('/') - LEN(territoryName) ) ELSE LEFT( territoryPath, LEN(territoryPath) - LEN('/') - LEN(territoryName) - LEN('/') - LEN(regionName) ) END ,tpf.budgetCategoryId ,'budgetCategoryName' = tpf.budgetCategory /* FILLER COLUMNS (NULL padded; just so column list logic doesn't break as often on weird vestigial columns) */ ,'regionId' = NULL ,'divisionId' = NULL /*we could add the old SKU x CDR ones here too if we want.*/ /* MEASURES (all is relative to asofActuals period, pegged from the calendarMonthStartDate of asofActuals) */ ,asofActuals = COALESCE(tpf.actual#_measure#,0) ,asofActualsLY = COALESCE(0 * (CASE WHEN salesPeriod < @currPeriodToProrate THEN 1 WHEN @currPeriodToProrate < salesPeriod THEN 0 ELSE (@prorate) END) ,0) /*Uhh, the legacy SPAZ might not be prorating this; though Invoice AZ does (same with remActualsLY)*/ ,remActualsLY = COALESCE(tpf.actual#_measure# /*reverse the prorate, but also zero out in the earlier bins that are not part of the "remaining" subwindow*/ * (CASE WHEN tpf.salesPeriod < @currPeriodToProrate THEN 0 WHEN @currPeriodToProrate < tpf.salesPeriod THEN 1 ELSE (1.0 - @prorate) END) ,0) ,asofBudgets = COALESCE(tpf.budget#_measure# * (CASE WHEN tpf.salesPeriod < @currPeriodToProrate THEN 1 WHEN @currPeriodToProrate < tpf.salesPeriod THEN 0 ELSE (@prorate) END) , 0) ,remBudgets = COALESCE(tpf.budget#_measure# /*reverse the prorate, but also zero out in the earier bins that are not part of the "remaining" subwindow*/ * (CASE WHEN tpf.salesPeriod < @currPeriodToProrate THEN 0 WHEN @currPeriodToProrate < tpf.salesPeriod THEN 1 ELSE (1.0 - @prorate) END) ,0) ,remForecastOverride = COALESCE(tpf.forecast#_measure# * (CASE WHEN tpf.salesPeriod < @currPeriodToProrate THEN 0 WHEN @currPeriodToProrate < tpf.salesPeriod THEN 1 ELSE (1.0 - @prorate) END) ,0) ,remPipelineNew = COALESCE(0 ,0) /*Not included for now*/ ,remPipelineLost = COALESCE(0 ,0) /*Not include for now*/ ,remPipelinePotential = COALESCE(tpf.pipeline#_measure#, 0) ,budgetCategoryMgr_fspro_userId ,budgetCategoryMgr_lastName ,budgetCategoryMgr_firstName ,case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName FROM tbl_DW_BudgetPerformance#_tableSuffix# AS tpf WITH (nolock) WHERE 1 = 1 and territoryId in ( ) ) /* ******************************* END Pseudo-ETL - (This can be moved to an actual data warehouse if need be; But if we did this right, we have extremely flexible and summarizable month bins that can be stacked side-by-side or just summarized accross a single year (Everything is inline in a single month-bin!)) * ********************************/ /* Start of the real boiler-plated Dx-style stuff; Above should socket into here */ ,_formattedData as ( SELECT '_' = 0 ,#_budgetCompatibleColumns# ,asofActuals ,asofActualsLY /*already prorated; easier to do above for now*/ ,remActualsLY /*already prorated; easier to do above for now*/ ,asofBudgets /*already prorated; easier to do above for now*/ ,remBudgets /*already prorated; easier to do above for now*/ ,remForecastOverride /*already prorated; easier to do above for now*/ ,remPipelineNew ,remPipelineLost ,remPipelinePotential ,'period' = 'R2_P' + cast(datediff( month, , calendarMonthStartDate ) + 1 as varchar) FROM performance_dxFormatted WHERE 1=1 and ( calendarMonthStartDate between and ) ) , _filtered as ( select * from _formattedData where #applyFilter( attributes.datastore.filter )# ) , _summaryByMonth as ( SELECT '_' = 0 ,#_groupColumns# ,period ,SUM(#_measureCol#) as #_measureCol# FROM _filtered GROUP BY #_groupColumns#, period ) , _summary as ( SELECT '_' = 0 ,#_groupColumns# ,SUM(#_measureCol#) as #_measureCol# FROM _summaryByMonth GROUP BY #_groupColumns# ) select * from _summary order by #_groupColumns# for JSON PATH, INCLUDE_NULL_VALUES , _dataPivot as ( SELECT '_' = 0 ,#_groupColumns# /*M and M/M*/ ,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 _summaryByMonth pivot( sum( asofActuals ) for period in ( ,R1_P#_px#,R2_P#_px# ) ) _pivot ) _pivotted ) ,_summaryWithPivot as ( SELECT '_' = 0 ,coalesce(_summary.#_col#, _dataPivot.#_col#) as #_col# ,_dataPivot.R1_P#_px# ,_dataPivot.R2_P#_px# ,_dataPivot.R1 ,_dataPivot.R2 ,_summary.#_col# FROM _summary INNER JOIN _dataPivot ON #_AND# _summary.#_col# = _dataPivot.#_col# ) , _data as ( select *, R2 - R1 as variance, case when R1 != 0 then ( R2 - R1 ) / R1 else null end as variancePct from _summaryWithPivot ) , _return as ( select * from _data #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 ---> select cdrRDC.cdr_recordId from tbl_CDR_Distributors cdrRDC with (nolock) inner join tbl_DST_Distributors dstRDC with (nolock) on dstRDC.dstId = cdrRDC.fsl_dstId and dstRDC.dstCompanyType = 'RDC' where cdrRDC.ownerId = and cdrRDC.fsl_tablecode = /* -- #_currentSalesPeriodStart# - #_currentSalesPeriodEnd# -- #_priorSalesPeriodStart# - #_priorSalesPeriodEnd# -- Period to Prorate: #_priorSalesPeriodToProrate# -- Prorate: #_priorSalesProrate# -- AsOf Date: #attributes.datastore.objectParams.asofDate# */ declare @prevPeriodToProrate datetime = ; declare @currPeriodToProrate datetime = DATEADD(year,1,@prevPeriodToProrate); declare @prorate float = ; declare @asofDate datetime = ; WITH /*Preformat Mapping stuff*/ /* Overhead Mapping between Systems */ OPRtoCDR AS ( /* How to map CLIENT OPERATORS to and from DIRECT SHIP OPERATOR CDR CODES */ SELECT 'oprId' = opr.operatorId ,'oprName' = opr.companyName COLLATE DATABASE_DEFAULT ,'cdrId' = cdr.cdr_recordId ,'cdrCode' = cdr.cdr_dstCode COLLATE DATABASE_DEFAULT ,'cdrName' = cdr.cdr_dstName COLLATE DATABASE_DEFAULT FROM tbl_CDR_Distributors AS cdr WITH (nolock) INNER JOIN tbl_OPR_ClientOperators AS opr WITH (nolock) ON opr.ownerId = cdr.ownerId AND opr.fsltablecode = cdr.fsl_tablecode AND opr.directCustomerNbr = cdr.cdr_dstCode AND opr.directCustomer = 'Y' AND cdr.cdr_dstCode <> '' /*some cdr's have missing codes*/ AND cdr.cdr_dstCode IS NOT NULL WHERE cdr.ownerId = AND cdr.fsl_tablecode = ) ,tbl_DW_DistributorHierarchy#_tableSuffix# AS ( /*TODO replace*/ SELECT distinct 'cdr_recordId' = originalCdrRecordId ,FSL_dstid ,distributorId,distributorName,distributorCode ,parentDistributorId,parentDistributorName ,redistributorId,redistributorName,redistributorCode ,parentRedistributorId,parentRedistributorName ,territoryId ,'territoryName' = territoryName ,'regionId' = territoryL2Id ,'regionName' = territoryL2Name ,'divisionId' = territoryL3Id ,'divisionName' = territoryL3Name ,buyingGroupId,buyingGroupName ,customerId,customerName,customerCode ,directShipOperatorId,directShipOperatorName,directShipOperatorCode FROM BI..tbl_CRM_SalesInvoiceSummary#_tableSuffix# WITH (NOLOCK) UNION SELECT distinct 'cdr_recordId' = originalCdrRecordId ,FSL_dstid ,distributorId,distributorName,distributorCode ,parentDistributorId,parentDistributorName ,redistributorId,redistributorName,redistributorCode ,parentRedistributorId,parentRedistributorName ,territoryId ,'territoryName' = territoryName ,'regionId' = territoryL2Id ,'regionName' = territoryL2Name ,'divisionId' = territoryL3Id ,'divisionName' = territoryL3Name ,buyingGroupId,buyingGroupName ,customerId,customerName,customerCode ,directShipOperatorId,directShipOperatorName,directShipOperatorCode FROM BI..tbl_CRM_SalesForecastSummary#_tableSuffix# WITH (NOLOCK) ) ,tbl_DW_BudgetCategoryHierarchy#_tableSuffix# AS ( SELECT budgetCategoryId, budgetCategory FROM tbl_DW_ProductHierarchy#_tableSuffix# WITH (nolock) GROUP BY budgetCategoryId, budgetCategory ) /*Translate table to the column names of the incoming DevExpress GROUPBY and FILTERBY fields*/ ,directRedistributorCdrs AS ( SELECT cdr_recordId FROM tbl_CDR_Distributors WITH (nolock) WHERE ownerId = AND FSL_TableCode = AND cdr_dstCompanyType = 'R' ) ,AllBucketsThrownIntoTheSamePot AS ( /*Actuals TY*/ SELECT 'calendarMonthStartDate' = DATEFROMPARTS(forecast_year,forecast_month,1) ,'originalCdrRecordId' = tpartnerId ,'skuId' = skuId ,'territoryId' = NULL ,'budgetCategoryId' = NULL ,'actuals' = forecast_qty ,'actualsLY' = NULL ,'budget' = NULL ,'forecast' = NULL FROM tbl_MFR_TPF_Detail#_tableSuffix# AS tpfA WITH (nolock) WHERE mfr_id = AND 'MFR' = AND forecast_status = 'A' AND tpartnerType = 'CDR' AND tpartnerId NOT IN (select * from directRedistributorCdrs) AND territoryId in ( ) UNION ALL /*Actuals LY*/ SELECT 'calendarMonthStartDate' = DATEFROMPARTS(cast(forecast_year as int) + 1,forecast_month,1) ,'originalCdrRecordId' = tpartnerId ,'skuId' = skuId ,'territoryId' = NULL ,'budgetCategoryId' = NULL ,'actuals' = NULL ,'actualsLY' = forecast_qty ,'budget' = NULL ,'forecast' = NULL FROM tbl_MFR_TPF_Detail#_tableSuffix# AS tpfA WITH (nolock) WHERE mfr_id = AND 'MFR' = AND forecast_status = 'A' AND tpartnerType = 'CDR' AND tpartnerId NOT IN (select * from directRedistributorCdrs) AND territoryId in ( ) UNION ALL /*Forecasts (the manually provided ones)*/ SELECT 'calendarMonthStartDate' = DATEFROMPARTS(forecast_year,forecast_month,1) ,'originalCdrRecordId' = tpartnerId ,'skuId' = skuId ,'territoryId' = NULL ,'budgetCategoryId' = NULL ,'actuals' = NULL ,'actualsLY' = NULL ,'budget' = NULL ,'forecast' = forecast_qty FROM tbl_MFR_TPF_Detail#_tableSuffix# AS tpfF WITH (nolock) WHERE mfr_id = AND 'MFR' = AND forecast_status = 'F' AND tpartnerType = 'CDR' AND tpartnerId NOT IN (select * from directRedistributorCdrs) AND territoryId in ( ) UNION ALL /*Budget-Quota*/ SELECT 'calendarMonthStartDate' = DATEFROMPARTS(forecast_year,forecast_month,1) ,'originalCdrRecordId' = NULL ,'skuId' = NULL ,'territoryId' = tpartnerId ,'budgetCategoryId' = bgtCategoryId ,'actuals' = NULL ,'actualsLY' = NULL ,'budget' = forecast_qty ,'forecast' = NULL FROM tbl_MFR_TPF_Detail#_tableSuffix# AS tpfQ WITH (nolock) WHERE mfr_id = AND 'MFR' = AND forecast_status = 'Q' AND tpartnerType = 'TER' AND productLevel = 'UOM' AND bgtCategoryId IS NOT NULL AND territoryId in ( ) AND 1 = 0 ) , tpfAll AS ( SELECT calendarMonthStartDate,originalCdrRecordId,skuId,territoryId,budgetCategoryId ,'actuals' = SUM(actuals) ,'actualsLY' = SUM(actualsLY) ,'budget' = SUM(budget) ,'forecast' = SUM(forecast) FROM AllBucketsThrownIntoTheSamePot WHERE calendarMonthStartDate >= '2020-01-01' GROUP BY calendarMonthStartDate,originalCdrRecordId,skuId,territoryId,budgetCategoryId ) ,pipelineImpactPerMonth_Prelogic AS ( SELECT 'originalCdrRecordId' = CASE WHEN distributorId > 0 THEN distributorId ELSE COALESCE(OPRtoCDR.cdrId, -5 /*CONSTANTS.DST.UNKNOWN.ID*/ ) END ,'skuId' = opp.skuId ,'qty' = #_measure# /* Native columns used recalculating Impact Factor */ ,stageStatusCode ,typeCode ,estOrderDate ,DurationUnit ,estOrderEndDate FROM BI..tbl_CRM_OpportunitiesSummary#_tableSuffix# as opp WITH (nolock) LEFT OUTER JOIN OPRtoCDR ON OPRtoCDR.oprId = opp.operatorId WHERE 1=1 /* implicit userId related scoping */ AND territoryId in ( ) ) ,pipelineImpactPerMonth_ImpactRange AS ( SELECT opp.* ,'calendarMonthStartDate' = cal.fiscalStartMonth /* Impact date range: take Last Invoice date or Opportunity start, whichever one is greater note: lastInvoiceDate is @asofDate in this context, but could also be Last Month's end date, or today */ ,'impactStartDate' = CASE WHEN @asofDate < cal.startDate and opp.estOrderDate < cal.startDate THEN cal.startDate WHEN opp.estOrderDate < @asofDate THEN @asofDate ELSE opp.estOrderDate END /* Impact date range: take Plan End date or Opportunity End, whichever one is sooner */ ,'impactEndDate' = CASE WHEN cal.endDate < opp.estOrderEndDate THEN cal.endDate ELSE opp.estOrderEndDate END ,'fiscalStartDate' = cal.startDate ,'fiscalEndDate' = cal.endDate FROM pipelineImpactPerMonth_Prelogic as opp LEFT JOIN tbl_CRM_FiscalPeriods as cal WITH (nolock) ON opp.estOrderDate <= cal.endDate AND cal.startDate <= opp.estOrderEndDate WHERE cal.ownerType = AND cal.ownerId = AND cal.periodType = 'M' AND cal.fiscalYear > 2019 ) ,pipelineImpactPerMonth_ImpactFactor AS ( SELECT opp.* /* Impact factor before accounting for catagorical business rules */ ,'generalImpactFactor' = CASE WHEN opp.impactEndDate < opp.impactStartDate THEN 0 ELSE ---> CASE WHEN DurationUnit = 'YR' THEN CAST( DATEDIFF(day, impactStartDate, impactEndDate) + 1 AS FLOAT) / 365.0 ELSE CAST( DATEDIFF(day, impactStartDate, impactEndDate) AS FLOAT) / CAST( DATEDIFF(day, estOrderDate, estOrderEndDate) AS FLOAT) END CAST( DATEDIFF(day, opp.impactStartDate, opp.impactEndDate) AS FLOAT) / CAST( DATEDIFF(day, opp.estOrderDate, opp.estOrderEndDate) AS FLOAT) END FROM pipelineImpactPerMonth_ImpactRange AS opp ) ,pipelineImpactPerMonth_CatagoricalBusinessRules AS ( SELECT opp.* /* Handle business rules */ ,'isLost' = CASE WHEN stageStatusCode = 'R' AND typeCode = 'PRO' THEN 1 ELSE 0 END ,'isNew' = CASE WHEN stageStatusCode = 'C' AND typeCode != 'PRO' THEN 1 ELSE 0 END ,'isPotential' = CASE WHEN stageStatusCode = 'A' THEN 1 ELSE 0 END ,'businessRuleModifier' = CASE /* Opportunity is active but past due */ WHEN opp.stageStatusCode = 'A' AND opp.estOrderDate < GETDATE() THEN /*changed to 'today' for SCRUM 27875*/ 0 /* Opportunity is marked for retention, so it can't count towards growth */ WHEN opp.stageStatusCode IN ('C','A') AND opp.typeCode = 'PRO' THEN 0 /* Opportunity was lost but not on the retention list, so it counts towards nothing */ WHEN opp.stageStatusCode = 'R' AND opp.typeCode <> 'PRO' THEN 0 /* Opportunity is marked for retention but lost the re-sale, so our term-end total will be short that much */ WHEN opp.stageStatusCode = 'R' AND opp.typeCode = 'PRO' THEN -1 /* Opportunity is new and can be counted towards term-end projections */ ELSE 1 END FROM pipelineImpactPerMonth_ImpactFactor AS opp ) ,pipeline_dxFormatted AS ( SELECT opp.originalCdrRecordId ,opp.skuId ,opp.calendarMonthStartDate ,'qtyLost' = SUM(opp.isLost * opp.businessRuleModifier * opp.generalImpactFactor) ,'qtyNew' = SUM(opp.isNew * opp.businessRuleModifier * opp.generalImpactFactor) ,'qtyPotential' = SUM(opp.isPotential * opp.businessRuleModifier * opp.generalImpactFactor) FROM pipelineImpactPerMonth_CatagoricalBusinessRules AS opp GROUP BY opp.originalCdrRecordId, opp.skuId, opp.calendarMonthStartDate ) ,performance_noHierarchy_byMonth_dxFormatted AS ( /* ************************************* --As of: Calendar segment thus far ----actuals [asofActuals] ----comparison baseline, uses a combination of [asofActualsLY] [asofBudgets] --Remaining Calendar segment ----actuals forecast, uses, and ----remaining baseline to compare forecast, uses a combination of [remActualsLY] [remPipelineNew] [remPipelineLost] [remPipelinePotential] [remForecastOverride] [remBudgets] ************************************* */ SELECT '_' = 0 /* BASE GRANULARITY (sku x cdr x mth [x ter x bgtCat]) */ ,'#_col#' = COALESCE( tpfAll.#_col# ,pipeline.#_col# ,-100 ) ,'#_col#' = COALESCE( tpfAll.#_col#, NULL ) ,'calendarMonthStartDate' = COALESCE(tpfAll.calendarMonthStartDate, pipeline.calendarMonthStartDate) /* HIERARCHY (for sku x cdr)*/ /* ~ done below in next step ~ */ /* MEASURES (all is relative to asofActuals period, pegged from the calendarMonthStartDate of asofActuals) */ ,asofActuals = COALESCE(tpfAll.actuals,0) ,asofActualsLY = COALESCE(tpfAll.actualsLY * (CASE WHEN tpfAll.calendarMonthStartDate < @currPeriodToProrate THEN 1 WHEN @currPeriodToProrate < tpfAll.calendarMonthStartDate THEN 0 ELSE (@prorate) END) ,0) /*Uhh, the legacy SPAZ might not be prorating this; though Invoice AZ does (same with remActualsLY)*/ ,remActualsLY = COALESCE(tpfAll.actualsLY /*reverse the prorate, but also zero out in the earlier bins that are not part of the "remaining" subwindow*/ * (CASE WHEN tpfAll.calendarMonthStartDate < @currPeriodToProrate THEN 0 WHEN @currPeriodToProrate < tpfAll.calendarMonthStartDate THEN 1 ELSE (1.0 - @prorate) END) ,0) ,asofBudgets = COALESCE(tpfAll.budget * (CASE WHEN tpfAll.calendarMonthStartDate < @currPeriodToProrate THEN 1 WHEN @currPeriodToProrate < tpfAll.calendarMonthStartDate THEN 0 ELSE (@prorate) END) , 0) ,remBudgets = COALESCE(tpfAll.budget /*reverse the prorate, but also zero out in the earier bins that are not part of the "remaining" subwindow*/ * (CASE WHEN tpfAll.calendarMonthStartDate < @currPeriodToProrate THEN 0 WHEN @currPeriodToProrate < tpfAll.calendarMonthStartDate THEN 1 ELSE (1.0 - @prorate) END) ,0) ,remForecastOverride = COALESCE(tpfAll.forecast * (CASE WHEN tpfAll.calendarMonthStartDate < @currPeriodToProrate THEN 0 WHEN @currPeriodToProrate < tpfAll.calendarMonthStartDate THEN 1 ELSE (1.0 - @prorate) END) ,0) ,remPipelineNew = COALESCE(pipeline.qtyNew,0) ,remPipelineLost = COALESCE(pipeline.qtyLost,0) ,remPipelinePotential = COALESCE(pipeline.qtyPotential,0) FROM tpfAll FULL OUTER JOIN pipeline_dxFormatted as pipeline ON #_AND# tpfAll.#_col# = pipeline.#_col# #_AND# tpfAll.calendarMonthStartDate = pipeline.calendarMonthStartDate ) ,performance_dxFormatted AS ( SELECT '_' = 0 ,base.#_col# ,'territoryId' = coalesce(base.territoryId, cdr.territoryId, 0) ,'budgetCategoryId' = coalesce(base.budgetCategoryId, sku.budgetCategoryId, 0) ,base.calendarMonthStartDate ,base.#_col# /* Group-By columns */ ,'buyingGroupName' = cdr.buyingGroupName ,'redistributorName' = cdr.redistributorName ,'parentDistributorName' = cdr.parentDistributorName ,'distributorName' = cdr.distributorName ,'directShipOperatorName' = cdr.directShipOperatorName ,'customerName' = cdr.customerName ,'divisionName' = ter.divisionName ,'regionName' = ter.regionName ,'territoryName' = ter.name ,'brandName' = sku.brand ,'budgetCategoryName' = bgtCat.budgetCategory ,'skuFull' = coalesce(sku.sku,'') + ' - ' + coalesce(sku.skuDesc,'') ,'productFull' = coalesce(sku.product,'') + ' - ' + coalesce(sku.shortDesc,'') ,'categoryFull' = coalesce(sku.catnum,'') + ' - ' + coalesce(sku.category,'') ,'productLineFull' = coalesce(sku.productLineCode,'') + ' - ' + coalesce(sku.productLine,'') /* Filter columns (additional) */ /*,sku.budgetCategoryId*/ ,sku.brandId ,sku.categoryId ,sku.prodId ,sku.prodline_id /*,sku.skuId*/ ,sku.discontinued ,sku.focusProduct ,ter.territoryPath /*,ter.territoryId*/ ,cdr.buyingGroupId ,cdrA.cdr_dstPath ,'redist_cdr_recordId' = cdr.redistributorId ,cdr.distributorId FROM performance_noHierarchy_byMonth_dxFormatted as base /* CDR - TER*/ LEFT OUTER HASH JOIN tbl_DW_ProductHierarchy#_tableSuffix# AS sku WITH (nolock) ON base.skuId = sku.skuId LEFT OUTER HASH JOIN tbl_DW_BudgetCategoryHierarchy#_tableSuffix# AS bgtCat WITH (nolock) ON coalesce(base.budgetCategoryId, sku.budgetCategoryId) = bgtCat.budgetCategoryId /* SKU - BgtCat*/ LEFT OUTER HASH JOIN tbl_CDR_Distributors AS cdrA WITH (nolock) ON cdrA.ownerId = AND cdrA.FSL_TableCode = AND cdrA.cdr_recordId = base.originalCdrRecordId LEFT OUTER HASH JOIN tbl_DW_DistributorHierarchy#_tableSuffix# AS cdr WITH (nolock) ON base.originalCdrRecordId = cdr.cdr_recordId LEFT OUTER HASH JOIN tbl_TER_Territories AS ter WITH (nolock) ON coalesce(base.territoryId, cdr.territoryId) = ter.territoryId WHERE coalesce(base.territoryId, cdr.territoryId, 0) > 0 ) --I might have blown up dev last night because I messed up the period join so it just blows up /*select count(*) as 'x' from performance_dxFormatted for JSON PATH, INCLUDE_NULL_VALUES*/ /*select * from performance_dxFormatted for JSON PATH, INCLUDE_NULL_VALUES*/ /* ******************************* END Pseudo-ETL - (This can be moved to an actual data warehouse if need be; But if we did this right, we have extremely flexible and summarizable month bins that can be stacked side-by-side or just summarized accross a single year (Everything is inline in a single month-bin!)) * ********************************/ /* Start of the real boiler-plated Dx-style stuff; Above should socket into here */ ,_formattedData as ( SELECT '_' = 0 ,#ListRemoveDuplicates(Listappend(_groupColumns,_filterColumns))# ,asofActuals ,asofActualsLY /*already prorated; easier to do above for now*/ ,remActualsLY /*already prorated; easier to do above for now*/ ,asofBudgets /*already prorated; easier to do above for now*/ ,remBudgets /*already prorated; easier to do above for now*/ ,remForecastOverride /*already prorated; easier to do above for now*/ ,remPipelineNew ,remPipelineLost ,remPipelinePotential ,'period' = 'R2_P' + cast(datediff( month, , calendarMonthStartDate ) + 1 as varchar) FROM performance_dxFormatted WHERE 1=1 and ( calendarMonthStartDate between and ) ) , _filtered as ( select * from _formattedData where #applyFilter( attributes.datastore.filter )# ) , _summaryByMonth as ( SELECT '_' = 0 ,#_groupColumns# ,period ,SUM(#_measureCol#) as #_measureCol# FROM _filtered GROUP BY #_groupColumns#, period ) , _summary as ( SELECT '_' = 0 ,#_groupColumns# ,SUM(#_measureCol#) as #_measureCol# FROM _summaryByMonth GROUP BY #_groupColumns# ) select * from _summary order by #_groupColumns# for JSON PATH, INCLUDE_NULL_VALUES , _dataPivot as ( SELECT '_' = 0 ,#_groupColumns# /*M and M/M*/ ,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 _summaryByMonth pivot( sum( asofActuals ) for period in ( ,R1_P#_px#,R2_P#_px# ) ) _pivot ) _pivotted ) ,_summaryWithPivot as ( SELECT '_' = 0 ,coalesce(_summary.#_col#, _dataPivot.#_col#) as #_col# ,_dataPivot.R1_P#_px# ,_dataPivot.R2_P#_px# ,_dataPivot.R1 ,_dataPivot.R2 ,_summary.#_col# FROM _summary INNER JOIN _dataPivot ON #_AND# _summary.#_col# = _dataPivot.#_col# ) , _data as ( select *, R2 - R1 as variance, case when R1 != 0 then ( R2 - R1 ) / R1 else null end as variancePct from _summaryWithPivot ) , _return as ( select * from _data #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 --->