USE XXXX
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/*********************************************************************************************************
--建立者: HERMAN 日期﹕2020-XX-XX
--调用的程序﹕
--说明﹕XXXXXXXXX
--更新记录﹕
-- 日期 更改人 更新说明
------------------ ------------- --------------------------------------------
-- 2020-XX-XX HERMAN 新增
--*******************************************************************************************************************************/
CREATE PROCEDURE [dbo].[SP_CRM_SERVICE_BRANCH_ATTRIBUTION]
AS
BEGIN
DECLARE @ErrorNo INT = 0;
DECLARE @ErrorMsg NVARCHAR(500) = N'';
DECLARE @NowTime DATETIME = CONVERT(DATETIME, GETDATE());
DECLARE @BeginDate DATETIME = DATEADD(MONTH,DATEDIFF(MONTH, 0, @NowTime) - 1,0) --上月第一天
DECLARE @EndDate DATETIME = DATEADD(MONTH,DATEDIFF(MONTH, -1, @NowTime) - 1,-1) --上月最后第一天
BEGIN TRY
BEGIN TRAN MyTransaction;
--服务分店为0的客户
SELECT CUM.CRM_Domain ,
CUM.Customer_ID
INTO #Customer_Master
FROM dbo.tbCRM_Customer_Master CUM WITH ( NOLOCK )
WHERE Service_Branch = 0;
--消费的客户
SELECT ROW_NUMBER() OVER ( PARTITION BY CCH1.CRM_Domain,CCH1.Customer_ID
ORDER BY CCH1.Tran_Date, CCH1.Customer_ID ) RN ,
CCH1.CRM_Domain ,
CCH1.Customer_ID ,
CCH1.Card_No ,
CCH1.Tran_Date ,
CCH1.Sales_Branch_Code
INTO #Credit_History
FROM #Customer_Master AS CUM
INNER JOIN dbo.tbCRM_Card_Master CM WITH ( NOLOCK )
ON CM.CRM_Domain = CUM.CRM_Domain
AND CM.Customer_ID = CUM.Customer_ID
AND CM.Status_ID = 0
INNER JOIN dbo.tbCRM_Credit_History AS CCH1 WITH ( NOLOCK )
ON CUM.CRM_Domain = CCH1.CRM_Domain
AND CUM.Customer_ID = CCH1.Customer_ID
AND CM.Card_No = CCH1.Card_No
LEFT JOIN dbo.tbCRM_Credit_History AS CCH10 WITH ( NOLOCK )
ON CCH1.CRM_Domain = CCH10.CRM_Domain
AND CCH1.Customer_ID = CCH10.Customer_ID
AND CCH1.Stock_Group = CCH10.Stock_Group
AND CCH1.Stock_Type = CCH10.Stock_Type
AND CCH1.Sales_Branch_Code = CCH10.Sales_Branch_Code
AND CCH1.Stock_Create_Domain = CCH10.Stock_Create_Domain
AND CCH1.Stock_No = CCH10.Stock_No
AND CCH1.Stock_Sub_No = CCH10.Stock_Sub_No
AND CCH1.Tran_Date < CCH10.Tran_Date
AND CCH10.Tran_Code = 10
AND CCH10.Tran_Date >= CONVERT(DATETIME, DATEADD(DAY,-1, @NowTime))
WHERE CCH1.Tran_Date >= CONVERT(DATETIME, DATEADD(DAY, -1,@NowTime))
AND CCH1.Tran_Date < @NowTime
AND CCH1.Tran_Code = 1
AND CCH10.Seq IS NULL;
--更新客户服务分店
UPDATE CUM
SET CUM.Service_Branch = CH.Sales_Branch_Code ,
CUM.Last_Modify_Date = GETDATE() ,
CUM.Last_Follow_By = 'SYSTEM'
FROM dbo.tbCRM_Customer_Master CUM
INNER JOIN #Credit_History CH
ON CH.CRM_Domain = CUM.CRM_Domain
AND CH.Customer_ID = CUM.Customer_ID
WHERE CUM.Service_Branch = 0
AND CH.RN = 1;
COMMIT TRANSACTION MyTransaction;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION MyTransaction;
SELECT @ErrorNo = ERROR_NUMBER();
SELECT @ErrorMsg = ERROR_MESSAGE();
RAISERROR(N'消费更新服务分店出错%s', 16, 1, @ErrorMsg);
END CATCH;
BEGIN TRY
BEGIN TRAN MyTransactionService;
--每月1号统计无消费客户并更新服务分店
IF DATEADD(MONTH, DATEDIFF(MONTH, 0, @NowTime), 0) = CONVERT(DATETIME, CONVERT(VARCHAR(10), @NowTime, 120))
BEGIN
--上月注册会员
IF OBJECT_ID('tempdb..#Customer_Master') IS NOT NULL
DROP TABLE #Customer_Master;
SELECT CRM_Domain ,
Customer_ID
INTO #Customer_Master
FROM dbo.tbCRM_Customer_Master WITH ( NOLOCK )
WHERE First_Join_Date >= @BeginDate
AND First_Join_Date <= @EndDate;
--消费的客户
IF OBJECT_ID('tempdb..#Credit_History') IS NOT NULL
DROP TABLE #Credit_History;
SELECT ROW_NUMBER() OVER ( PARTITION BY CCH1.CRM_Domain,CCH1.Customer_ID
ORDER BY CCH1.Tran_Date, CCH1.Customer_ID ) RN ,
CCH1.CRM_Domain ,
CCH1.Customer_ID ,
CCH1.Tran_Date ,
CCH1.Sales_Branch_Code
INTO #Credit_History
FROM #Customer_Master AS CUM
INNER JOIN dbo.tbCRM_Credit_History AS CCH1 WITH ( NOLOCK )
ON CUM.CRM_Domain = CCH1.CRM_Domain
AND CUM.Customer_ID = CCH1.Customer_ID
WHERE CCH1.Tran_Date >= @BeginDate
AND CCH1.Tran_Date <= @EndDate
AND CCH1.Tran_Code = 1;
--无消费客户
IF OBJECT_ID('tempdb..#NoConsumption') IS NOT NULL
DROP TABLE #NoConsumption;
SELECT *
INTO #NoConsumption
FROM #Customer_Master CUM
WHERE NOT EXISTS ( SELECT *
FROM #Credit_History CM
WHERE CUM.CRM_Domain = CM.CRM_Domain
AND CUM.Customer_ID = CM.Customer_ID
AND RN = 1 );
--无消费客户服务分店跟回注册分店
UPDATE CUM
SET CUM.Service_Branch = CUM.Originality_Join_Branch,
CUM.Last_Modify_Date = GETDATE() ,
CUM.Last_Follow_By = 'SYSTEM'
FROM dbo.tbCRM_Customer_Master CUM WITH ( NOLOCK )
INNER JOIN #NoConsumption NOCM ON NOCM.CRM_Domain = CUM.CRM_Domain
AND NOCM.Customer_ID = CUM.Customer_ID;
END;
COMMIT TRANSACTION MyTransactionService;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION MyTransactionService;
SELECT @ErrorNo = ERROR_NUMBER();
SELECT @ErrorMsg = ERROR_MESSAGE();
RAISERROR(N'无消费更新回注册分店出错%s', 16, 1, @ErrorMsg);
END CATCH;
END;
网友评论