SELECT 		staging.ownerid,
			staging.ownertype, 
			staging.salesMonth, 	
			staging.salesYear, 
			staging.fseOperatorId, 
			staging.ProductIdentifier, 
			staging.fseAffiliationOperatorId, 
			count(*) as stuffcount
    FROM 		tbl_import_gpoactivity staging
	LEFT OUTER JOIN  tbl_GPO_OperatorActivity history
	ON 		history.ownerId = staging.ownerId
	AND		history.ownerType = staging.ownerType
	AND		history.salesMonth = staging.salesMonth
	AND		history.salesYear = staging.salesYear
	AND		history.affiliationOperatorId = staging.fseAffiliationOperatorId
	AND		history.sku = staging.ProductIdentifier
	AND		history.operatorId = staging.fseOperatorId
   	WHERE 	staging.ownerId = 1644
   	AND 	staging.ownertype = 'MFR'
   	AND 	staging.eai_batchid = 534
	AND		history.operatorId IS NULL
	AND 	staging.fseskuid IS NOT NULL
	AND 	staging.fseOperatorId IS NOT NULL
	AND 	staging.fseAffiliationOperatorId IS NOT NULL
group by staging.ownerid, staging.ownertype, staging.salesmonth, staging.salesyear, staging.fseoperatorid, staging.productidentifier, staging.fseaffiliationoperatorid
order by stuffcount desc
	
select * from tbl_import_gpoactivity where eai_batchid = 534 and fseoperatorid = 500833 and productidentifier = '01441EGLDZ'
and fseaffiliationoperatorid = 382172

select count(*) from tbl_GPO_OperatorActivity 

INSERT INTO tbl_GPO_OperatorActivity (ownerid, ownerType, salesMonth, salesYear, operatorId, sku, salesQty, 
		mktcategoryId, mktSubCategoryId, salesMonthStart, salesMonthEnd, skuId, prodId, 
		affiliationOperatorId, salesAmt, lastUpdated)
	SELECT 	staging.ownerid,
			staging.ownertype,
			staging.salesMonth,	
			staging.salesYear,
			staging.fseOperatorId,
			staging.ProductIdentifier,
			sum(staging.TotalUnits) as totalunits,
			staging.fseMktCategoryId,
			staging.fseMktSubCategoryId,
			cast(cast(staging.salesMonth as varchar(2))+ '/01/' + cast(staging.salesYear as varchar(4)) AS datetime) AS salesMonthStart,
			dateadd(s,-1,dateadd(m,1,cast(cast(staging.salesMonth as varchar(2))+ '/01/' + cast(staging.salesYear as varchar(4)) AS datetime))) AS SalesMonthEnd,
			staging.fseSkuId,
			staging.fseProdId,
			staging.fseAffiliationOperatorId,
			sum(staging.TotalDollars) as totaldollars,
			getdate()			
    FROM 		tbl_import_gpoactivity staging
	LEFT OUTER JOIN  tbl_GPO_OperatorActivity history
	ON 		history.ownerId = staging.ownerId
	AND		history.ownerType = staging.ownerType
	AND		history.salesMonth = staging.salesMonth
	AND		history.salesYear = staging.salesYear
	AND		history.affiliationOperatorId = staging.fseAffiliationOperatorId
	AND		history.sku = staging.ProductIdentifier
	AND		history.operatorId = staging.fseOperatorId
   	WHERE 	staging.ownerId = 1644
   	AND 	staging.ownertype = 'MFR'
   	AND 	staging.eai_batchid = 534
	AND		history.operatorId IS NULL
	AND 	staging.fseskuid IS NOT NULL
	AND 	staging.fseOperatorId IS NOT NULL
	AND 	staging.fseAffiliationOperatorId IS NOT NULL
	group by staging.ownerid,
			staging.ownertype,
			staging.salesMonth,	
			staging.salesYear,
			staging.fseOperatorId,
			staging.ProductIdentifier,
			staging.fseMktCategoryId,
			staging.fseMktSubCategoryId,
			salesMonthStart,
			SalesMonthEnd,
			staging.fseSkuId,
			staging.fseProdId,
			staging.fseAffiliationOperatorId



UPDATE 	history
	SET 	salesQty = staging.TotalUnits,
			mktcategoryId = staging.fseMktCategoryId,
			mktSubCategoryId = staging.fseMktSubCategoryId,
			skuId = staging.fseSkuId,
			prodId = staging.fseProdId,
			salesAmt = staging.TotalDollars,
			lastUpdated = getDate() 
	FROM 		tbl_GPO_OperatorActivity history
	INNER JOIN 	tbl_import_gpoactivity staging
	ON 		history.ownerId = staging.ownerId
	AND		history.ownerType = staging.ownerType
	AND		history.salesMonth = staging.salesMonth
	AND		history.salesYear = staging.salesYear
	AND		history.affiliationOperatorId = staging.fseAffiliationOperatorId
	AND		history.sku = staging.ProductIdentifier
	AND		history.operatorId = staging.fseOperatorId
   	WHERE 	staging.ownerId = 1644
   	AND 	staging.ownertype = 'MFR'
   	AND 	staging.eai_batchid = 534
	AND 	staging.fseskuid IS NOT NULL
	AND 	staging.fseOperatorId IS NOT NULL
	AND 	staging.fseAffiliationOperatorId IS NOT NULL


select rollup.operatorId,
       rollup.mktCategoryId, 
       rollup.mktSubCategoryId, 
      sum( rollup.avgQty ) avgQty,
      sum( rollup.avgSale ) avgSale
    from (
        select operatorId,
		 mktCategoryId, 
		mktSubCategoryId, 
			prodid, 
		round( avg( convert( float, salesQty )), 0 ) avgQty, 
		round( avg( convert( float, salesAmt)), 0) avgSale
          from tbl_GPO_OperatorActivity
         where ownerId = 1644
           and ownerType = 'MFR'
         group by operatorId, mktCategoryId, mktSubCategoryId, prodid ) rollup

  group by rollup.operatorId, rollup.mktCategoryId, rollup.mktSubCategoryId