declare @ownerId int = ; declare @ownerType varchar(3) = ; select top 20 opportunityId, partnerId, partnerType, partnerIdTk, operatorName, mfr_name, sku, skuDesc, cdr_dstName, cases, lbs, dollars, equivUnits, estOrderDate, distributorLastInvoiceDate, distributorLastInvoiceCases, distributorLastInvoiceLbs, distributorLastInvoiceDollars, lastPurchaseMonth, lastPurchaseQty, purchasingStatus, last_opr_sku_purchase_month, last_opr_purchase_month, last_confirmed_cases, last_confirmed_lbs, last_confirmed_dollars, confirmed_rolling_12_cases, confirmed_rolling_12_lbs, confirmed_rolling_12_dollars, confirmed_ytd_cases, confirmed_ytd_lbs, confirmed_ytd_dollars, validation_age_days, annual_pace_cases, annual_pace_lbs, annual_pace_dollars, ty_remaining_pace_cases, ty_remaining_pace_lbs, ty_remaining_pace_dollars, annual_pace_cases_variance, annual_pace_cases_pct, annual_pace_lbs_variance, annual_pace_lbs_pct, annual_pace_dollars_variance, annual_pace_dollars_pct, ty_remaining_pace_cases_variance, ty_remaining_pace_cases_pct, ty_remaining_pace_lbs_variance, ty_remaining_pace_lbs_pct, ty_remaining_pace_dollars_variance, ty_remaining_pace_dollars_pct, ty_purchasing_pace_cases_variance, ty_purchasing_pace_cases_pct, ty_purchasing_pace_lbs_variance, ty_purchasing_pace_lbs_pct, ty_purchasing_pace_dollars_variance, ty_purchasing_pace_dollars_pct, impactCases, impactLbs, impactDollars, ty_purchasing_pace_cases, ty_purchasing_pace_lbs, ty_purchasing_pace_dollars from tbl_DW_Opportunities#_tableSuffix# dw with (nolock) where dw.ownerId = @ownerId and dw.ownerType = @ownerType and dw.partnerType = 'OPR' and dw.statusCode = 'C' and dw.partnerTerritoryId in ( ) -- all territory visibility mode and dw.effectiveRepId in ( ) and ( 1 = 2 or dw.territoryPath like ) and dw.effectiveRepId in ( ) and effectiveRepOwnerType = and dw.mfr_id in ( ) and dw.typeCode in ( ) and dw.estOrderDate between and order by dw.cases desc for json auto, include_null_values