declare @estOrderDateStart datetime = ; declare @estOrderDateEnd datetime = ; with noSaleOppsRaw as ( select case when n.parentReasonId is null then n.reasonPath else pn.reasonPath end as noSaleReasonPath, case when n.parentReasonId is null then n.reasonName else pn.reasonName end as reasonName, o.#_quantityColumn# -- , o.effectiveRepFirstName as firstName ,#_dataColumns# from tbl_DW_Opportunities#_tableSuffix# o with (nolock) inner join tbl_CRM_NoSaleReasons n with (nolock) on n.ownerId = o.ownerId and n.ownerType = o.ownerType and n.reasonPath = o.noSaleReasonPath left outer join tbl_CRM_NoSaleReasons pn with (nolock) on pn.ownerId = n.ownerId and pn.ownerType = n.ownerType and pn.reasonId = n.parentReasonId where o.effectiveRepId > 0 and o.statusCode= 'R' and o.partnerType in ('OPR', 'CDR') and o.ownerId = and o.ownerType = and o.territoryId in ( ) and o.effectiveRepId in ( ) and ( 1 = 2 or o.territoryPath like ) and o.prodid in () and o.typeCode in () and o.budgetCategoryId in () and o.effectiveRepId in () and o.mfr_id in () and (1=2 or o.clientSegPath like ) o.clientSegPath like and (1=2 or o.clientSegPath like ) o.clientSegPath like and ( 1 = 2 or o.productSetTags like ) AND o.prodId is not null and o.effectiveRepOwnerType = and o.estOrderDate between @estOrderDateStart and @estOrderDateEnd ), noSaleOppsViewByPercentage as ( select distinct #_dataColumns#, (sum(#_quantityColumn#) OVER(PARTITION BY #_keyColumn#)*1.0 / nullif(sum(#_quantityColumn#) OVER(),0))*100 AS theViewByPercentage from noSaleOppsRaw o ), noSaleOppsViewByTotal as ( select #_dataColumns#, nsvbp.theViewByPercentage, sum(o.#_quantityColumn#) as #_quantityColumn# from noSaleOppsRaw o inner join noSaleOppsViewByPercentage nsvbp on nsvbp.[#_keyColumnName#] = #_keyColumn# group by #_dataColumns#, nsvbp.theViewByPercentage ) , noSaleOppsReasonPercentage as ( select distinct noSaleReasonPath, reasonName, #_keyColumnName#, (sum(#_quantityColumn#) OVER(PARTITION BY noSaleReasonPath,reasonName,#_keyColumnName# )*1.0 /nullif(sum(#_quantityColumn#) OVER(PARTITION BY #_keyColumnName#),0))*100 AS theReasonViewByPercentage from noSaleOppsRaw where #_keyColumnName# = ), noSaleOppsReasonViewByTotal as ( select #_dataColumns#, o.noSaleReasonPath,o.reasonName, nsrp.theReasonViewByPercentage, sum(o.#_quantityColumn#) as reason_#_quantityColumn# from noSaleOppsRaw o inner join noSaleOppsReasonPercentage nsrp on nsrp.#_keyColumnName# = #_keyColumn# and nsrp.reasonName = o.reasonName and nsrp.noSaleReasonPath = o.noSaleReasonPath group by #_dataColumns#,o.noSaleReasonPath,o.reasonName, nsrp.theReasonViewByPercentage, o.#_quantityColumn# ) select * from noSaleOppsViewByTotal order by #_quantityColumn# desc select nsvt.theViewByPercentage, nsvt.#_quantityColumn#, nsrvt.#_keyColumnName#, nsrvt.#_displayColumnName#, nsrvt.noSaleReasonPath,nsrvt.reasonName, nsrvt.theReasonViewByPercentage, sum(reason_#_quantityColumn#) as reason_#_quantityColumn#_total from noSaleOppsViewByTotal nsvt left outer join noSaleOppsReasonViewByTotal nsrvt on nsrvt.[#_keyColumnName#] = nsvt.[#_keyColumnName#] group by nsvt.theViewByPercentage, nsvt.#_quantityColumn#, nsrvt.#_keyColumnName#, nsrvt.#_displayColumnName#, nsrvt.noSaleReasonPath, nsrvt.reasonName, nsrvt.theReasonViewByPercentage order by #_quantityColumn# desc,reason_#_quantityColumn#_total desc for json auto, include_null_values