select cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstCode, cdr.cdr_dstCompanyType from tbl_CDR_Distributors cdr with (nolock) where ownerId = and fsl_tablecode = and ( ( cdr.cdr_dstCompanyType = 'B' and exists ( select * from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) where tpf.mfr_id = cdr.ownerId and tpf.tpartnerType = 'CDR' and tpf.tpartnerId = cdr.cdr_recordId and tpf.forecast_status = 'A') and exists ( select * from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) where tpf.mfr_id = cdr.ownerId and tpf.tpartnerType = 'CDR' and tpf.tpartnerId = cdr.cdr_recordId and tpf.forecast_status = 'C')) ) order by cdr.cdr_dstName

select c.partnerId, c.partnerType, opr.companyName, convert( datetime, tpf.forecast_year + '-' + tpf.forecast_month + '-01' ) as forecastDate, convert( int, tpf.forecast_month ) forecastMonth, convert( int, tpf.forecast_year ) forecastYear, sum( tpf.forecast_qty ) tradeCases, sum( tpf.forecast_amt ) tradeSales from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.requestId = tpf.rebateContractId and r.ownerId = tpf.mfr_Id and r.ownerType = 'MFR' inner join tbl_TPM_Contracts c with (nolock) on c.contractId = r.contractId and c.ownerId = r.ownerId and c.ownerType = r.ownerType inner join tbl_OPR_CLientOperators opr with (nolock) on opr.operatorId = c.partnerId and opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType and 'OPR' = c.partnerType where tpf.tpartnerId in ( ) and tpf.tpartnerType = 'CDR' and tpf.forecast_status = 'C' and tpf.mfr_id = and 'MFR' = and tpf.productLevel = 'SKU' and ( ( convert( int, tpf.forecast_year ) = and convert( int, tpf.forecast_month ) >= ) or ( convert( int, tpf.forecast_year ) = and convert( int, tpf.forecast_month ) <= ) convert( int, tpf.forecast_year ) = and convert( int, tpf.forecast_month ) between and ) group by c.partnerId, c.partnerType, opr.companyName, tpf.forecast_month, tpf.forecast_year order by companyName select partnerId, partnerType, companyName, max( forecastDate ) forecastDate from qmd_tradeImpact group by partnerId, partnerType, companyName select max( forecastDate ) from qmd_lastData select companyName, sum( tradeCases ) tradeCases, sum( tradeSales ) tradeSales, 0.0 tradeCost, 0.0 tradeSalesRate, 0.0 pendingTradeSales, 0.0 futureTradeSales, 0.0 projectedTradeSales, 0.0 pendingTradeCost, 0.0 futureTradeCost, 0.0 projectedTradeCost, 0.0 projectedTradeCostRate, 0.0 distributorSpending, 0.0 distributorSpendingRate, 0.0 futureDistributorSpending, 0.0 projectedDistributorSpending, max( forecastDate ) asOfMonth from qmd_tradeImpact where ( forecastYear < or ( forecastYear = and forecastMonth <= ) ) group by companyName select opr.companyName, sum( tpf.forecast_qty ) tradeCases, sum( tpf.forecast_amt ) tradeCost from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.requestId = tpf.rebateContractId and r.ownerId = tpf.mfr_Id and r.ownerType = 'MFR' inner join tbl_TPM_Contracts c with (nolock) on c.contractId = r.contractId and c.ownerId = r.ownerId and c.ownerType = r.ownerType inner join tbl_OPR_CLientOperators opr with (nolock) on opr.operatorId = c.partnerId and opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType and 'OPR' = c.partnerType where tpf.tpartnerId in ( ) and tpf.tpartnerType = 'CDR' and tpf.forecast_status = 'R' and tpf.mfr_id = and 'MFR' = and tpf.productLevel = 'SKU' and ( ( convert( int, tpf.forecast_year ) = and convert( int, tpf.forecast_month ) >= ) or ( convert( int, tpf.forecast_year ) = and convert( int, tpf.forecast_month ) <= ) convert( int, tpf.forecast_year ) = and convert( int, tpf.forecast_month ) between and ) group by c.partnerId, c.partnerType, opr.companyName --, tpf.forecast_month, tpf.forecast_year order by companyName select * from qmd_tradeImpact order by companyName

Operator	Actual Tracking Cases	Actual Tracking Sales	As of	Tracking Sales Lag	Est. Tracking Sales	Sales	Operator Pgms Act Tracking	Operator Pgms Tracking Lag	Operator Pgms Est Tracking	Operator Pgms	Operator Pgm Pct	Dist Pgms Act Tracking	Dist Pgms Est Tracking	Distributor Pgms	Distributor Pgm Pct	Grand Total	Grand Total Pct
#htmleditformat( companyName )#	#lsnumberformat( tradeCases )#	#lscurrencyformat( tradeSales )#	#lsdateformat( asOfMonth, "Mmm YY" )#	#lscurrencyformat( pendingTradeSales )#	#lscurrencyformat( futureTradeSales )#	#lscurrencyformat( projectedTradeSales )#	#lscurrencyformat( tradeCost )#	#lscurrencyFormat( pendingTradeCost )#	#lscurrencyformat( futureTradeCost )#	#lscurrencyformat( projectedTradeCost )#	#lsnumberformat( projectedTradeCostRate * 100.0, "0.0")#%	#lscurrencyformat( distributorSpending )#	#lscurrencyformat( futureDistributorSpending )#	#lscurrencyformat( projectedDistributorSpending )#	#lsnumberFormat( ( projectedDistributorSpending / projectedTradeSales ) * 100.0, "0.0" )#%	#lscurrencyformat( projectedDistributorSpending + projectedTradeCost )#	#lsnumberFormat( ( ( projectedDistributorSpending + projectedTradeCost ) / projectedTradeSales ) * 100.0, "0.0" )#%
Total Through Operators	#lsnumberformat( _totalTradeCases )#	#lscurrencyFormat( _totalTradeSales )#		#lscurrencyFormat( _totalPendingTradeSales )#	#lscurrencyformat( _totalFutureTradeSales )#	#lscurrencyformat( _totalProjectedTradeSales )#	#lscurrencyFormat( _totalTradeCost )#	#lscurrencyformat( _totalPendingTradeCost )#	#lscurrencyFormat( _totalFutureTradeCost )#	#lscurrencyFormat( _totalProjectedTradeCost )#	#lsnumberformat( ( _totalProjectedTradeCost / _totalProjectedTradeSales ) * 100.0 , "0.0" )#%	#lscurrencyFormat( _totalOperatorDistributorSpending )#	#lscurrencyFormat( _futureDistributorSpendingTotal )#	#lscurrencyFormat( _projectedDistributorSpendingTotal )#	#lsnumberFormat( ( _projectedDistributorSpendingTotal / _totalProjectedTradeSales ) * 100.0, "0.0" )#%	#lscurrencyformat( _projectedDistributorSpendingTotal + _totalProjectedTradeCost )#	#lsnumberFormat( ( ( _projectedDistributorSpendingTotal + _totalProjectedTradeCost ) / _totalProjectedTradeSales ) * 100.0, "0.0" )#%
Street	#lsnumberformat( _totalStreetCases )#	#lscurrencyFormat( _totalStreetSales )#		#lscurrencyFormat( _pendingStreetSales )#	#lscurrencyformat( _totalFutureStreetSales )#	#lscurrencyformat( _totalProjectedStreetSales )#						#lscurrencyFormat( _totalStreetDistributorSpending )#	#lscurrencyFormat( _totalFutureStreetDistributorSpending )#	#lscurrencyFormat( _totalProjectedStreetDistributorSpending )#	#lsnumberFormat( ( _totalProjectedStreetDistributorSpending / _totalProjectedStreetSales ) * 100.0, "0.0" )#%	#lscurrencyFormat( _totalProjectedStreetDistributorSpending )#	#lsnumberFormat( ( _totalProjectedStreetDistributorSpending / _totalProjectedStreetSales ) * 100.0, "0.0" )#%
Total through Distribution	#lsnumberformat( _totalStreetCases + _totalTradeCases )#	#lscurrencyFormat( _totalStreetSales + _totalTradeSales )#		#lscurrencyformat( _pendingStreetSales + _totalPendingTradeSales )#	#lscurrencyformat( _futureSales )#	#lscurrencyformat( _projectedTotalSales )#	#lscurrencyFormat( _totalTradeCost )#	$0.0	#lscurrencyFormat( _totalFutureTradeCost )#	#lscurrencyFormat( _totalProjectedTradeCost )#	#lsnumberformat( ( _totalProjectedTradeCost / _projectedTotalSales ) * 100.0 , "0.0" )#%	#lscurrencyFormat( _totalDistributorSpending )#	#lscurrencyFormat( _gtFutureDistributorSpending )#	#lscurrencyFormat( _gtProjectedDistributorSpending )#	#lsnumberFormat( ( _gtProjectedDistributorSpending / _projectedTotalSales ) * 100.0, "0.0" )#%	#lscurrencyFormat( _gtProjectedDistributorSpending + _totalProjectedTradeCost )#	#lsnumberFormat( ( ( _gtProjectedDistributorSpending + _totalProjectedTradeCost ) / _projectedTotalSales ) * 100.0, "0.0" )#%
Operator	Actual Tracking Cases	Actual Tracking Sales	As of	Pnd. Tracking Sales	Est. Tracking Sales	Sales	Operator Pgms Act Tracking	Operator Pgms Pnd Tracking	Operator Pgms Est Tracking	Operator Pgms	Operator Pgm Pct	Dist Pgms Act Tracking	Dist Pgms Est Tracking	Distributor Pgms	Distributor Pgm Pct	Grand Total	Grand Total Pct