lyfz_repos/铂金版源码/DBFile/SQL/更新聚合客户表中指定条件的客户.sql

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROCE_UpdateAggregationCustomer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PROCE_UpdateAggregationCustomer]
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		刘超
-- Create date: 2015-07-16
-- Description:	更新聚合客户表中指定条件的客户
-- =============================================
CREATE  PROCEDURE [dbo].[PROCE_UpdateAggregationCustomer]
(
 @WhereString varchar(2000) =N'',  --条件值 为 [GP_OrderNumber]订单号,[GP_CustomerGroupID]客户组ID 字段的值
 @ReturnI int output            --返回更新记录数
)
AS
BEGIN    
  DECLARE @M_Cus_CustomerNumber varchar(2000)
  DECLARE @GP_CustomerGroupID varchar(2000)
    --处理开始点和结束点 
    delete [tempTB_AggregationCustomer] where len(@WhereString)>0 and (GP_OrderNumber=@WhereString or GP_CustomerGroupID=@WhereString)
 insert into [tempTB_AggregationCustomer] 
 select * from [Vw_AggregationCustomer]
   where len(@WhereString)>0 and (GP_OrderNumber=@WhereString or GP_CustomerGroupID=@WhereString)
  set @ReturnI=(select count(*) from [tempTB_AggregationCustomer] where GP_OrderNumber=@WhereString or GP_CustomerGroupID=@WhereString)
 
 ---更新group by Cus_Name,M_Cus_CustomerNumber后的客户组临时表--
set @M_Cus_CustomerNumber=(select top 1 M_Cus_CustomerNumber from [tempTB_AggregationCustomer] where GP_OrderNumber=@WhereString or GP_CustomerGroupID=@WhereString)
delete [tempTB_GroupByAggregationCustomer]  where  M_Cus_CustomerNumber =@M_Cus_CustomerNumber;
set @GP_CustomerGroupID=(select top 1 GP_CustomerGroupID from [tempTB_AggregationCustomer] where GP_OrderNumber=@WhereString or GP_CustomerGroupID=@WhereString)
delete [tempTB_GroupByAggregationCustomer]  where  GP_CustomerGroupID =@GP_CustomerGroupID;
with t as(		
SELECT  
max(GP_CustomerGroupID) as GP_CustomerGroupID
from tempTB_AggregationCustomer
 where M_Cus_CustomerNumber =@M_Cus_CustomerNumber
  group by Cus_Name,M_Cus_CustomerNumber
  ),t2 as(
  select * from tempTB_AggregationCustomer 
  where GP_CustomerGroupID in (select GP_CustomerGroupID from t)
  )
 insert into [tempTB_GroupByAggregationCustomer] select * from t2
 -----
 END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROCE_UpdateAggregationCustomerAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PROCE_UpdateAggregationCustomerAll]
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		刘超
-- Create date: 2015-08-09
-- Description:	更新聚合客户表中所有客户
-- =============================================
CREATE  PROCEDURE [dbo].[PROCE_UpdateAggregationCustomerAll]
(
 @ReturnI int output            --返回更新记录数
)
AS
BEGIN    
 truncate table [dbo].[tempTB_AggregationCustomer]
  insert into [tempTB_AggregationCustomer]  select * from [Vw_AggregationCustomer]
 truncate table [dbo].[tempTB_GroupByAggregationCustomer]
 insert into [tempTB_GroupByAggregationCustomer] select * from [Vw_GroupByAggregationCustomer]
 set @ReturnI=(select count(*) from [tempTB_AggregationCustomer])
 -----
 END
GO