select likelihoodPercent, 'P' + convert( varchar, likelihoodpercent * 100 ) pipelineStageCode from tbl_SPL_Stages with (nolock) where ownerId = and ownerType = and statusCode = 'A' order by likelihoodPercent with monthlyPipeline as ( select convert( datetime, format( fp.fiscalStartMonth, 'MM/01/yyyy' )) pipelineMonth, 'P' + convert( varchar, o.likelihoodpercent * 100 ) pipelineStage, o.#_quantityColumn# from tbl_DW_Opportunities#_tableSuffix# o (nolock) inner join tbl_CRM_FiscalPeriods fp with (nolock) on fp.ownerId = o.ownerId and fp.ownerType = o.ownerType and fp.periodType = 'M' where coalesce( o.pastDue, 'N' ) != 'Y' and o.statusCode = 'A' and o.estOrderDate between fp.startDate and fp.endDate and o.partnerTerritoryId in ( ) and o.effectiveRepId in ( ) and ( 1 = 2 or o.effectiveRepId = ) and ( 1 = 2 or o.territoryPath like ) and ( 1 = 2 or o.productHierarchyPath like ) and ( 1 = 2 or o.budgetCategoryId like ) and ( 1 = 2 or o.mfr_id like ) and o.estOrderDate >= and o.estOrderDate <= and (1=2 or o.clientSegPath like ) o.clientSegPath like and ( 1 = 2 or o.productSetTags like ) and o.effectiveRepOwnerType = ), pipelineSummary as ( select pipelineMonth, pipelineStage, sum( #_quantityColumn# ) as quantity from monthlyPipeline group by pipelineMonth, pipelineStage ), pipelinePivot as ( select pipelineMonth ,coalesce( #_pipelineStageCode#, 0 ) #_pipelineStageCode# from pipelineSummary pivot( sum( quantity ) for pipelineStage in ( #_pipelineStageCodes# )) p ) select pipelineMonth, format( pipelineMonth, 'MMM yy' ) pipelineMonthLabel ,sum( #_pipelineStageCode# ) #_pipelineStageCode# from pipelinePivot group by pipelineMonth select * from qmd_data order by pipelineMonth