select distinct sa.salesAreaName, ter.name , ter.territoryId from tbl_SLS_SalesAreas sa with (nolock) left outer join tbl_SLS_SalesAreaTerritories sat with (nolock) on sat.ownerId = sa.ownerId and sat.ownerType = sa.ownerType and sat.salesAreaId = sa.salesAreaId left outer join tbl_TER_Territories ter with (nolock) on ter.ownerId = sat.ownerId and ter.fsl_tablecode = sat.ownerType and ter.territoryId = sat.territoryId where sa.ownerType = 'MFR' and sa.ownerId = 26 --and s.crmActive = 'y' order by sa.salesareaName, ter.name select sa.salesAreaName, sum(tpf.forecast_alt) as lm_alt,cdr.cdr_dstname as DistributorName, cdr.cdr_dstCity as city, cdr.cdr_dststate as state, s.sku, s.skuDesc,cdr.cdr_recordId, ter.name as territoryName, ter.territoryId from tbl_MFR_TPF_Detail tpf with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = tpf.tpartnerId and cdr.ownerId = tpf.mfr_id --and cdr.cdr_territoryId = tpf.territoryId inner join tbl_PRD_Skus s with (nolock) on s.skuId = tpf.skuId and s.ownerId = tpf.mfr_Id and s.fsl_TableCode = 'MFR' --and s.crmActive = 'Y' left outer join tbl_SLS_SalesAreas sa with (nolock) on sa.ownerId = s.ownerId and sa.ownerType = s.fsl_TableCode and sa.salesAreaId = s.salesAreaId left outer join tbl_SLS_SalesAreaTerritories sat with (nolock) on sat.ownerId = cdr.ownerId and sat.ownerType = cdr.fsl_tablecode and sat.cdr_recordId = cdr.cdr_recordId -- and sat.territoryId = cdr.cdr_territoryId and sat.salesAreaId = sa.salesAreaId left outer join tbl_TER_Territories ter with (nolock) on ter.ownerId = sat.ownerId and ter.fsl_tablecode = sat.ownerType and ter.territoryId = sat.territoryId where cdr.ownerId = 26 and cdr.fsl_tablecode = 'MFR' and ter.territoryId in ( ) and tpf.tPartnerType = 'CDR' and cdr.cdr_dstCompanyType <> 'O' and tpf.bgtCategoryId in ( ) and tpf.forecast_status = and tpf.productLevel = 'SKU' /* and ( ( convert( int, tpf.forecast_year ) = 2019 and convert( int, tpf.forecast_month ) >= 5 ) or ( convert( int, tpf.forecast_year ) = 2020 and convert( int, tpf.forecast_month ) <= 2 ) )*/ and convert( int, tpf.forecast_year ) = 2020 and convert( int, tpf.forecast_month ) = 3 GROUP by sa.salesAreaName, cdr.cdr_recordId, cdr.cdr_dstname, cdr.cdr_dstCity, cdr.cdr_dststate,s.sku, s.skuDesc, ter.name , ter.territoryId order by cdr.cdr_dstName select sa.salesAreaName, sum(tpf.forecast_alt) as lm_alt,cdr.cdr_dstname as DistributorName, cdr.cdr_dstCity as city, cdr.cdr_dststate as state, s.sku, s.skuDesc,cdr.cdr_recordId, ter.name as territoryName, ter.territoryId as terId, ter.territoryId --distinct tpf.territoryId --sum(tpf.forecast_alt) as forecast_alt from tbl_MFR_TPF_Detail tpf with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = tpf.tpartnerId and cdr.ownerId = tpf.mfr_id --and cdr.cdr_territoryId = tpf.territoryId inner join tbl_PRD_Skus s with (nolock) on s.skuId = tpf.skuId and s.ownerId = tpf.mfr_Id and s.fsl_TableCode = 'MFR' --and s.crmActive = 'Y' left outer join tbl_SLS_SalesAreas sa with (nolock) on sa.ownerId = s.ownerId and sa.ownerType = s.fsl_TableCode and sa.salesAreaId = s.salesAreaId left outer join tbl_SLS_SalesAreaTerritories sat with (nolock) on sat.ownerId = cdr.ownerId and sat.ownerType = cdr.fsl_tablecode and sat.cdr_recordId = cdr.cdr_recordId -- and sat.territoryId = cdr.cdr_territoryId and sat.salesAreaId = sa.salesAreaId left outer join tbl_TER_Territories ter with (nolock) on ter.ownerId = sat.ownerId and ter.fsl_tablecode = sat.ownerType and ter.territoryId = sat.territoryId where tpf.mfr_Id = 26 and tpf.tPartnerType = 'CDR' and cdr.cdr_dstCompanyType <> 'O' and ter.territoryId in ( ) and tpf.bgtCategoryId in ( ) and tpf.forecast_status = and convert( int, tpf.forecast_year ) = 2020 and convert( int, tpf.forecast_month ) =3 /* and ( ( convert( int, tpf.forecast_year ) = 2019 and convert( int, tpf.forecast_month ) >= 5 ) or ( convert( int, tpf.forecast_year ) = 2020 and convert( int, tpf.forecast_month ) <=3 ) )*/ and tpf.territoryId not in () GROUP by sa.salesAreaName, cdr.cdr_recordId, cdr.cdr_dstname, cdr.cdr_dstCity, cdr.cdr_dststate,s.sku, s.skuDesc, ter.name ,ter.territoryId, tpf.territoryId select sum( forecast_alt ) forecast_alt from tbl_MFR_TPF_Detail with (nolock) where mfr_Id = 26 and tPartnerType = 'TER' and tPartnerId in ( ) and bgtCategoryId in ( ) and forecast_status = and convert( int, forecast_year ) = 2020 and convert( int, forecast_month ) = 3 select sum(lm_alt) as total from qmd_getSkus select sa.salesAreaName, sum(tpf.forecast_alt) as lm_alt,cdr.cdr_dstname as DistributorName, cdr.cdr_dstCity as city, cdr.cdr_dststate as state, s.sku, s.skuDesc,cdr.cdr_recordId, ter.name as territoryName, ter.territoryId as terId, tpf.territoryId, cdr.cdr_territoryId --distinct tpf.territoryId --sum(tpf.forecast_alt) as forecast_alt from tbl_MFR_TPF_Detail tpf with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = tpf.tpartnerId and cdr.ownerId = tpf.mfr_id and cdr.fsl_tablecode = 'MFR' --and cdr.cdr_territoryId = tpf.territoryId left outer join tbl_PRD_Skus s on s.skuId = tpf.skuId and s.ownerId = tpf.mfr_id and s.fsl_tablecode= 'MFR' --and s.crmActive = 'Y' left outer join tbl_SLS_SalesAreas sa with (nolock) on sa.ownerId = s.ownerId and sa.ownerType = s.fsl_TableCode and sa.salesAreaId = s.salesAreaId left outer join tbl_SLS_SalesAreaTerritories sat with (nolock) on sat.ownerId = cdr.ownerId and sat.ownerType = cdr.fsl_tablecode and sat.cdr_recordId = cdr.cdr_recordId -- and sat.territoryId = cdr.cdr_territoryId and sat.salesAreaId = sa.salesAreaId left outer join tbl_TER_Territories ter with (nolock) on ter.ownerId = sat.ownerId and ter.fsl_tablecode = sat.ownerType and ter.territoryId = sat.territoryId where tpf.mfr_Id = 26 and tpf.tPartnerType = 'CDR' and cdr.cdr_dstCompanyType <> 'O' and tpf.bgtCategoryId in ( ) and tpf.forecast_status = and cdr.cdr_territoryId in () and tpf.territoryId = 0 -- and s.sku in ('5150010450', '5150010451', '5150010454','5150008051') and convert( int, tpf.forecast_year ) = 2020 and convert( int, tpf.forecast_month ) =3 /* and ( ( convert( int, tpf.forecast_year ) = 2019 and convert( int, tpf.forecast_month ) >= 5 ) or ( convert( int, tpf.forecast_year ) = 2020 and convert( int, tpf.forecast_month ) <=3 ) )*/ -- and tpf.territoryId not in (10707,4003,10695,4002,3999,6390,8539,193,8538,3960) GROUP by sa.salesAreaName, cdr.cdr_recordId, cdr.cdr_dstname, cdr.cdr_dstCity, cdr.cdr_dststate,s.sku, s.skuDesc, ter.name ,ter.territoryId, tpf.territoryId, cdr.cdr_territoryId Category : #url.category#
Forecast Type : #url.forecast_status#
Total Alt from Territory : #qmd_getAltFromSkus.forecast_alt#
Total Misplaced Alt : #qmd_totalAlt.total#
Grand Total Alt : #_gTotal#
--->