select * from qmd_classifications order by sortRank desc update activity set activity.fseContactId = m.fspro_userId, activity.fseOperatorId = m.orgId, activity.fsrBusinessId = m.fsrBusinessId, activity.tmpMonth = convert( int, left( fsrDateStatus, 2 )), activity.tmpYear = convert( int, right( fsrDateStatus, 4 )) from tbl_IMPORT_FoodServiceRewardsTransactions activity inner join tbl_FSPro_Members m on m.foodServiceRewardsId = activity.fsrOperatorId and m.ownerId = activity.ownerId and m.fsl_tablecode = activity.ownerType and m.orgType = 'OPR' where activity.eai_batchId = and activity.ownerType = and activity.ownerId = update activity set activity.duplicate = 'Y' from tbl_IMPORT_FoodServiceRewardsTransactions activity inner join tbl_FSR_TransactionIDHistory history on history.ownerId = activity.ownerId and history.ownerType = activity.ownerType where activity.eai_batchId = and activity.ownerType = and activity.ownerId = and activity.fsrTransactionId between history.minFsrTransactionId and history.maxFsrTransactionId insert into tbl_FSR_OperatorActivity ( ownerId, ownerType, foodserviceRewardsId, salesMonth, salesYear, fsrProductName ) select distinct fa.ownerId, fa.ownerType, fa.fsrBusinessId, fa.tmpMonth, fa.tmpYear, fa.fsrProductName from tbl_IMPORT_FoodServiceRewardsTransactions fa left outer join tbl_FSR_OperatorActivity oa on oa.foodserviceRewardsId = fa.fsrBusinessId and oa.salesMonth = fa.tmpMonth and oa.salesYear = fa.tmpYear and oa.fsrProductName = fa.fsrProductName and oa.ownerId = fa.ownerId and oa.ownerType = fa.ownerType where fa.eai_batchId = and fa.ownerId = and fa.ownerType = and fa.fsrBusinessId is not null and fa.duplicate is null and oa.foodServiceRewardsId is null update oa set oa.labelCount = oa.labelCount + ta.labelCount, salesMonthStart = convert( datetime, convert( varchar, oa.salesMonth ) + '/1/' + convert( varchar, oa.salesYear )), salesMonthEnd = dateadd( d, -1, dateadd( m, 1, convert( datetime, convert( varchar, oa.salesMonth ) + '/1/' + convert( varchar, oa.salesYear )))) from tbl_FSR_OperatorActivity oa inner join ( select fa.ownerId, fa.ownerType, fa.fsrBusinessId, fa.tmpMonth, fa.tmpYear, fa.fsrProductName, count(*) labelCount from tbl_IMPORT_FoodServiceRewardsTransactions fa where fa.eai_batchId = and fa.ownerId = and fa.ownerType = and fa.fsrBusinessId is not null and fa.duplicate is null group by fa.ownerId, fa.ownerType, fa.fsrBusinessId, fa.tmpMonth, fa.tmpYear, fa.fsrProductName ) ta on ta.ownerId = oa.ownerId and ta.ownerType = oa.ownerType and ta.fsrBusinessId = oa.foodserviceRewardsId and ta.tmpMonth = oa.salesMonth and ta.tmpYear = oa.salesYear and ta.fsrProductName = oa.fsrProductName select fsrProductId, fsrProductName, sortRank, visible, defaultCatalogCode, defaultProdId, defaultSKUId from tbl_FSR_ProductNames where ownerId = and ownerType = order by visible desc, sortRank, fsrProductName select distinct fsrProductName from tbl_FSR_OperatorActivity where ownerId = and ownerType = SELECT MFR_NAME FROM tblManufacturers WHERE MFR_ID =

Changed Labels:

#variables.i#

insert into tbl_FSR_TransactionIDHistory ( ownerId, ownerType, minFsrTransactionId, maxFsrTransactionId, dateProcessed ) select ownerId, ownerType, min( fsrTransactionId ), max( fsrTransactionId ), { fn now() } from tbl_IMPORT_FoodServiceRewardsTransactions where eai_batchId = and ownerType = and ownerId = and duplicate is null group by ownerId, ownerType update activity set mktCategoryId = mktCategories.filterId, mktSubCategoryId = mktSubCategories.attrId from tbl_FSR_OperatorActivity activity inner join tbl_ORG_Attributes fsrLabels on fsrLabels.attrDescription = activity.fsrProductName and fsrLabels.ownerId = activity.ownerId and fsrLabels.fsl_tablecode = activity.ownerType inner join tbl_ORG_AttributeLinks fsrLinks on fsrLinks.child_attrId = fsrLabels.attrId and fsrLinks.child_attrTypeId = 29 and fsrLinks.mstr_attrTypeId = 28 inner join tbl_CMM_Filters fsrTaxonomy on fsrTaxonomy.filterId= fsrLinks.mstr_attrId and fsrTaxonomy.ownerId = fsrLabels.ownerId and fsrTaxonomy.fsl_tablecode = fsrLabels.fsl_tablecode inner join tbl_CMM_FilterTypes ft on ft.filterTypeId = fsrTaxonomy.filterTypeId and ft.filterTypeName = 'FoodServiceRewards' inner join tbl_ORG_AttributeLinks skuLinks on skuLinks.mstr_attrId = fsrLabels.attrId and skuLinks.mstr_attrTypeId = 29 and skuLinks.child_attrTypeId = 60 inner join tbl_PRD_Skus s on s.skuId = skuLinks.child_attrId and s.ownerId = activity.ownerId and s.fsl_tablecode = activity.ownerType inner join tbl_ORG_AttributeLinks prodLinks on prodLinks.child_attrTypeId = 59 and prodLinks.child_attrId = s.prodId and prodLinks.mstr_attrTypeId = 29 inner join tbl_ORG_Attributes mktSubCategories on mktSubCategories.attrId = prodLinks.mstr_attrId and mktSubCategories.attrTypeId = prodLinks.mstr_attrTypeId inner join tbl_ORG_AttributeLinks mktCatSubCatLinks on mktCatSubCatLinks.child_attrId = mktSubCategories.attrId and mktCatSubCatLinks.child_attrTypeId = mktSubCategories.attrTypeId and mktCatSubCatLinks.mstr_attrTypeId = 28 inner join tbl_CMM_Filters mktCategories on mktCategories.filterId = mktCatSubCatLinks.mstr_attrId and mktCategories.ownerId = mktSubCategories.ownerId and mktCategories.fsl_tablecode = mktSubCategories.fsl_tablecode inner join tbl_CMM_FilterTypes ft2 on ft2.filterTypeId = mktCategories.filterTypeId and ft2.filterTypeName = 'ProductHierarchy' where activity.ownerId = and activity.ownerType = select a.ownerId, a.ownerType, ter.territoryId, ter.name territoryName, a.operatorId, opr.companyName, fp.mktCategoryid, f.filterLabel, f.sortRank filterSortRank, fp.mktSubCategoryid, oa.attrDescription, oa.sortRank attrSortRank, avg( a.labelCount ) avgQty from tbl_FSR_OperatorActivity a inner join tbl_MFR_FocusProducts fp on fp.ownerId = a.ownerId and fp.ownerType = a.ownerType and fp.mktCategoryId = a.mktCategoryId and fp.mktSubCategoryId = a.mktSubCategoryId inner join tbl_OPR_ClientOperators opr on opr.operatorId = a.operatorId and opr.ownerId = a.ownerId and opr.fsltablecode = a.ownerType inner join tbl_TER_Territories ter on ter.territoryId = opr.territoryId and ter.ownerId = opr.ownerId and ter.fsl_tablecode = opr.fsltablecode inner join tbl_ORG_Attributes oa on oa.attrId = fp.mktSubCategoryId and oa.ownerId = fp.ownerId and oa.fsl_tablecode = fp.ownerType inner join tbl_CMM_Filters f on f.filterId = fp.mktCategoryId and f.ownerId = fp.ownerId and f.fsl_tablecode = fp.ownerType where datediff( month, a.salesMonthStart, { fn now() } ) <= 6 and a.ownerType = and a.ownerId = group by a.ownerId, a.ownerType, ter.territoryId, ter.name, a.operatorId, opr.companyName, fp.mktCategoryid, f.filterLabel, f.sortRank, fp.mktSubCategoryId, oa.attrDescription, oa.sortRank order by filterSortRank, filterLabel, attrSortRank, attrDescription, avgQty desc, opr.companyName

#filterLabel# / #attrDescription#

Operator		Territory	Qty
#companyName#		#territoryName#	#avgQty#

--->