with budgets as ( select b.budgetId, b.budgetType, ter.name as territoryName, ter.territoryId, convert( int, tpf.forecast_year ) budgetYear, convert( int, tpf.forecast_month ) budgetMonth, coalesce( tpf.forecast_amt, 0.0 ) as budgetAmt from tbl_TPM_BudgetTypes b with (nolock) inner join tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) on tpf.qualifierId = qualifierId and tpf.mfr_id = b.ownerId and 'MFR' = b.ownerType and tpf.tPartnerType = 'TER' and tpf.qualifierType = 'TBG' and tpf.forecast_status = 'B' inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = tpf.tPartnerId and ter.ownerId = tpf.mfr_id and ter.fsl_tablecode = b.ownerType where b.ownerId = and b.ownerType = and dateFromParts( convert( int, tpf.forecast_year ), convert( int, tpf.forecast_month ), 1 ) between '#lsdateformat( _startDate, "MM/DD/YYYY")#' and '#lsdateformat( _endDate, "MM/DD/YYYY")#' and ter.territoryId in ( ) ), forecasts as ( select b.budgetType, cdr.cdr_recordId, cdr.cdr_dstName, r.requestId, r.description agreementDescription, convert( int, tpf.forecast_year ) forecastYear, convert( int, tpf.forecast_month ) forecastMonth, ter.parentTerritoryId as territoryId, b.budgetId, sum( tpf.forecast_amt ) as forecastAmt from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId= tpf.mfr_id and cdr.fsl_tablecode = 'MFR' and cdr.cdr_recordId = tpf.tPartnerId inner join tbl_TER_Territories ter with (Nolock) on ter.territoryId= cdr.cdr_territoryId and ter.ownerId = cdr.ownerId and ter.fsl_tablecode = cdr.fsl_tablecode 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_BudgetTypes b on b.budgetId = r.budgetId and b.ownerId = r.ownerId and b.ownerType = r.ownerType where tpf.mfr_id = and tpf.forecast_status = 'T' and tpf.tPartnerType = 'CDR' group by cdr.cdr_recordId, cdr.cdr_dstName, tpf.forecast_year, tpf.forecast_month, ter.parentTerritoryId, b.budgetId, b.budgetType, r.requestId, r.description ), actuals as ( select cdr.cdr_recordId, cdr.cdr_dstName, t.parentTerritoryId territoryId, r.budgetId, r.requestId, r.description agreementDescription, convert( int, tpf.forecast_year ) actualYear, convert( int, tpf.forecast_month ) actualMonth, sum( tpf.forecast_amt ) actualAmt from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = tpf.mfr_id and r.ownerType ='MFR' and r.requestId = tpf.rebateContractId and r.budgetId > 0 inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId= tpf.mfr_id and cdr.fsl_tablecode = 'MFR' -- and cdr.cdr_recordId = case when tpf.qualifierType = 'CDR' then tpf.qualifierId else tpf.tPartnerId end and cdr.cdr_recordId = tpf.tPartnerId inner join tbl_TER_Territories t with (nolock) on t.territoryId = tpf.territoryId and t.ownerId = tpf.mfr_id and t.fsl_tablecode = 'MFR' where tpf.mfr_id = and tpf.forecast_status = 'R' and tpf.tPartnerType = 'CDR' group by cdr.cdr_recordId, cdr.cdr_dstName, t.parentTerritoryId, r.budgetId, tpf.forecast_year, tpf.forecast_month, r.description, r.requestId ), resultF as ( select b.budgetType, f.cdr_dstName, f.requestId, f.agreementDescription, sum( f.forecastAmt ) forecastAmt, convert( datetime, '#lsdateformat( _startDate, "MM/DD/YYYY" )#' ) startDate, convert( datetime, '#lsdateformat( _endDate, "MM/DD/YYYY" )#' ) endDate from forecasts f inner join budgets b on b.budgetId = f.budgetId and b.budgetYear = f.forecastYear and b.budgetMonth = f.forecastMonth and b.territoryId = f.territoryId group by b.budgetType, f.cdr_dstName, f.agreementDescription, f.requestId ), resultA as ( select b.budgetType, a.cdr_dstName, a.requestId, a.agreementDescription, sum( a.actualAmt ) actualAmt, convert( datetime, '#lsdateformat( _startDate, "MM/DD/YYYY" )#' ) startDate, convert( datetime, '#lsdateformat( _endDate, "MM/DD/YYYY" )#' ) endDate from actuals a inner join budgets b on b.budgetId = a.budgetId and b.budgetYear = a.actualYear and b.budgetMonth = a.actualMonth and b.territoryId = a.territoryId group by b.budgetType, a.cdr_dstName, a.agreementDescription, a.requestId ), resultZ as ( select f.budgetType, f.cdr_dstName, f.agreementDescription, f.requestId, f.forecastAmt, 0.0 actualAmt from resultF f union all select a.budgetType, a.cdr_dstName, a.agreementDescription, a.requestId, 0.0 forecastAmt, a.actualAmt from resultA a ), result as ( select budgetType, cdr_dstName, agreementDescription, requestId, sum( forecastAmt ) forecastAmt, sum( actualAmt ) actualAmt from resultZ group by budgetType, cdr_dstName,agreementDescription, requestId ) select * from result order by budgetType, cdr_dstName for json auto, include_null_values