--检查包装数量和用料数量比例不一样的情况

Select * From [GSS_RoleCenter4KGExtend4ActivityDetailView] Where 包装数量<>用料数量 and MaterialBill_ShopCode=100

--比较耗材活动明细和库存数量不一致的数据

select A.*,B.* 

From(

(Select Material_Code,MaterialBill_StorehouseEnumItemCode,MaterialBill_ShopCode,Sum(包装数量) AS DetailNumber

From [GSS_RoleCenter4KGExtend4ActivityDetailView] 

Group by Material_Code,MaterialBill_StorehouseEnumItemCode,MaterialBill_ShopCode) A

Left outer join 

(Select MaterialCode,StorehouseEnumItemCode,ShopCode,Number/PackageToUseRate AS StockNumber 

From INM_MaterialStock  inner join INM_Material on INM_Material.Code=INM_MaterialStock.MaterialCode) B

On A.Material_Code=B.MaterialCode And A.MaterialBill_StorehouseEnumItemCode=B.StorehouseEnumItemCode)

Where A.DetailNumber<>B.StockNumber and A.MaterialBill_ShopCode=100 and B.ShopCode=100

--修正库存数量（按照耗材活动明细的汇总数据进行修正）

Update INM_MaterialStock Set Number =C.DetailNumber

From (

select A.*,B.* 

From(

(Select Material_Code,MaterialBill_StorehouseEnumItemCode,MaterialBill_ShopCode,Sum(包装数量) AS DetailNumber

From [GSS_RoleCenter4KGExtend4ActivityDetailView] 

Group by Material_Code,MaterialBill_StorehouseEnumItemCode,MaterialBill_ShopCode) A

Left outer join 

(Select MaterialCode,StorehouseEnumItemCode,ShopCode,Number/PackageToUseRate AS StockNumber 

From INM_MaterialStock  inner join INM_Material on INM_Material.Code=INM_MaterialStock.MaterialCode) B

On A.Material_Code=B.MaterialCode And A.MaterialBill_StorehouseEnumItemCode=B.StorehouseEnumItemCode)

Where A.DetailNumber<>B.StockNumber and A.MaterialBill_ShopCode=100 and B.ShopCode=100

) C 

Where INM_MaterialStock.MaterialCode=C.Material_Code 

AND INM_MaterialStock.MaterialCode=C.MaterialCode 

And INM_MaterialStock.StorehouseEnumItemCode=C.StorehouseEnumItemCode

And INM_MaterialStock.StorehouseEnumItemCode=C.MaterialBill_StorehouseEnumItemCode

and INM_MaterialStock.ShopCode=100 and C.MaterialBill_ShopCode=100 and c.ShopCode=100