declare @estOrderDateStart datetime = ; declare @estOrderDateEnd datetime = ; with opportunities as ( select impactCases, impactLbs, impactDollars, cases, lbs, dollars, commissionAmt, case when statusCode = 'A' and typeCode in ( 'NEW', 'INC' ) and pastDue = 'N' then 'ACTIVE' when pastDue = 'Y' and typeCode in ( 'NEW', 'INC', 'PRO' ) then 'PASTDUE' when statusCode = 'A' and typeCode in ( 'PRO' ) and pastDue = 'N' then 'ATRISK' when statusCode = 'C' and typeCode in ( 'NEW', 'INC' ) then 'SOLD' when statusCode = 'C' and typeCode in ( 'PRO' ) then 'RETAINED' when statusCode = 'R' and typeCode in ( 'PRO' ) then 'LOST' when statusCode = 'R' and typeCode in ( 'NEW', 'INC' ) then 'NOSALE' else null end as bucketCode , equivUnits, impactEquivUnits from tbl_DW_Opportunities#_tableSuffix# opps with (nolock) where estOrderDate between @estOrderDateStart and @estOrderDateEnd and partnerTerritoryId in ( ) and effectiveRepId in ( ) and ( 1 = 2 or territoryPath like ) and effectiveRepId in ( ) and mfr_id in ( ) and ( 1 = 2 or productHierarchyPath like ) and (1=2 or clientSegPath like ) clientSegPath like and budgetCategoryId in ( ) and ( 1 = 2 or productSetTags like ) and effectiveRepOwnerType = ), bucketSummary as ( select bucketCode, sum( impactCases ) impactCases, sum( impactLbs ) impactLbs, sum( impactDollars ) impactDollars, count(*) opportunities, sum( cases ) cases, sum( lbs ) lbs, sum( dollars ) dollars, sum( equivUnits) equivUnits, sum( impactEquivUnits ) impactEquivUnits , sum( commissionAmt ) commissionAmt, avg( commissionAmt ) commissionAvg from opportunities with (nolock) where bucketCode is not null group by bucketCode ), data as ( select 'IMPACTVOLUME' as metric, sum ( coalesce( ACTIVE, 0.0 )) as active, sum( coalesce( PASTDUE, 0.0 )) as pastDue, sum( coalesce( ATRISK, 0.0 )) as atRisk, sum( coalesce( SOLD, 0.0 )) as sold, sum( coalesce( RETAINED, 0.0 )) as retained, sum( coalesce( LOST, 0.0 )) as lost, sum( coalesce( NOSALE, 0.0 )) noSale from ( select * from bucketSummary ) as pivotSource pivot ( sum( impact#_uom# ) for bucketCode in ( ACTIVE, PASTDUE, ATRISK, SOLD, RETAINED, LOST, NOSALE )) as pivotTable union all select 'VOLUME' as metric, sum ( coalesce( ACTIVE, 0.0 )) as active, sum( coalesce( PASTDUE, 0.0 )) as pastDue, sum( coalesce( ATRISK, 0.0 )) as atRisk, sum( coalesce( SOLD, 0.0 )) as sold, sum( coalesce( RETAINED, 0.0 )) as retained, sum( coalesce( LOST, 0.0 )) as lost, sum( coalesce( NOSALE, 0.0 )) noSale from ( select * from bucketSummary ) as pivotSource pivot ( sum( #_uom# ) for bucketCode in ( ACTIVE, PASTDUE, ATRISK, SOLD, RETAINED, LOST, NOSALE )) as pivotTable union all select 'OPPORTUNITIES' as metric, sum ( coalesce( ACTIVE, 0.0 )) as active, sum( coalesce( PASTDUE, 0.0 )) as pastDue, sum( coalesce( ATRISK, 0.0 )) as atRisk, sum( coalesce( SOLD, 0.0 )) as sold, sum( coalesce( RETAINED, 0.0 )) as retained, sum( coalesce( LOST, 0.0 )) as lost, sum( coalesce( NOSALE, 0.0 )) noSale from ( select * from bucketSummary ) as pivotSource pivot ( sum( opportunities ) for bucketCode in ( ACTIVE, PASTDUE, ATRISK, SOLD, RETAINED, LOST, NOSALE )) as pivotTable union all select 'COMMISSIONAMT' as metric, sum ( coalesce( ACTIVE, 0.0 )) as active, sum( coalesce( PASTDUE, 0.0 )) as pastDue, sum( coalesce( ATRISK, 0.0 )) as atRisk, sum( coalesce( SOLD, 0.0 )) as sold, sum( coalesce( RETAINED, 0.0 )) as retained, sum( coalesce( LOST, 0.0 )) as lost, sum( coalesce( NOSALE, 0.0 )) noSale from ( select * from bucketSummary ) as pivotSource pivot ( sum( commissionAmt ) for bucketCode in ( ACTIVE, PASTDUE, ATRISK, SOLD, RETAINED, LOST, NOSALE )) as pivotTable union all select 'COMMISSIONAVG' as metric, sum ( coalesce( ACTIVE, 0.0 )) as active, sum( coalesce( PASTDUE, 0.0 )) as pastDue, sum( coalesce( ATRISK, 0.0 )) as atRisk, sum( coalesce( SOLD, 0.0 )) as sold, sum( coalesce( RETAINED, 0.0 )) as retained, sum( coalesce( LOST, 0.0 )) as lost, sum( coalesce( NOSALE, 0.0 )) noSale from ( select * from bucketSummary ) as pivotSource pivot ( sum( commissionAvg ) for bucketCode in ( ACTIVE, PASTDUE, ATRISK, SOLD, RETAINED, LOST, NOSALE )) as pivotTable ) select metric, coalesce( active, 0.0 ) as active, coalesce( pastDue, 0.0 ) as pastDue, coalesce( atRisk, 0.0 ) as atRisk, coalesce( sold, 0.0 ) as sold, coalesce( retained, 0.0 ) as retained, coalesce( lost, 0.0 ) as lost, coalesce( nosale, 0.0 ) as noSale from data for json auto