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 convert( int, tpf.forecast_year ) forecastYear, convert( int, tpf.forecast_month ) forecastMonth, ter.parentTerritoryId as territoryId, r.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' and r.budgetId > 0 where tpf.mfr_id = and tpf.forecast_status = 'T' and tpf.tPartnerType = 'CDR' group by tpf.forecast_year, tpf.forecast_month, ter.parentTerritoryId, r.budgetId ), actuals as ( select t.parentTerritoryId territoryId, r.budgetId, 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_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' group by t.parentTerritoryId, r.budgetId, tpf.forecast_year, tpf.forecast_month ), budgetSummary as ( select b.budgetType, b.territoryName, b.territoryId, b.budgetId, b.budgetMonth, b.budgetYear, f.forecastAmt, b.budgetAmt, a.actualAmt from budgets b left outer join forecasts f on f.budgetId = b.budgetId and f.territoryId = b.territoryId and f.forecastMonth = b.budgetMonth and f.forecastYear = b.budgetYear left outer join actuals a on a.budgetId = b.budgetId and a.territoryId = b.territoryId and a.actualMonth = b.budgetMonth and a.actualYear = b.budgetYear ) select budgetType, sum( forecastAmt ) forecastAmt, sum( actualAmt ) actualAmt, sum( budgetAmt ) as budgetAmt, convert( datetime, '#lsdateformat( _startDate, "MM/DD/YYYY" )#' ) startDate, convert( datetime, '#lsdateformat( _endDate, "MM/DD/YYYY" )#' ) endDate from budgetSummary group by budgetType for json auto, include_null_values