SP 存储过程随笔记录2

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;