sql 服務存儲過程

CREATE   PROCEDURE  SP_DAY_SERVICE  AS
        
         UPDATE  RES_USER
         SET  RES_USER.BC = CQ_HB.XBC
         FROM  RES_USER,CQ_HB
         WHERE  RES_USER.ACCOUNT_ID = CQ_HB.ACCOUNT_ID  AND
              DATEDIFF ( DAY ,CQ_HB.SXRQ, GETDATE ()) >= 0
         DELETE    FROM  CQ_HB     WHERE   DATEDIFF ( DAY ,CQ_HB.SXRQ, GETDATE ()) >= 0   -- 換班

         UPDATE  RES_USER  SET  RES_USER.ZW = CQ_JS.XZW
         FROM  RES_USER,CQ_JS
         WHERE  RES_USER.ACCOUNT_ID = CQ_JS.ACCOUNT_ID  AND
              DATEDIFF ( DAY ,CQ_JS.SXRQ, GETDATE ()) >= 0     
                 DELETE    FROM  CQ_JS     WHERE   DATEDIFF ( DAY ,CQ_JS.SXRQ, GETDATE ()) >= 0   -- 晉升

         UPDATE  RES_USER
         SET  RES_USER.BMID = CQ_YD.XZBM,RES_USER.KBID = CQ_YD.XZKB,
               RES_USER.XBID = CQ_YD.XZZX,RES_USER.ZW = CQ_YD.XZZW
         FROM  RES_USER,CQ_YD 
         WHERE  RES_USER.ACCOUNT_ID = CQ_YD.ACCOUNT_ID  AND  
             DATEDIFF ( DAY ,CQ_YD.SXRQ, GETDATE ()) >= 0
         DELETE    FROM  CQ_YD     WHERE   DATEDIFF ( DAY ,CQ_YD.SXRQ, GETDATE ()) >= 0   -- 異動


         UPDATE  RES_USER
         SET  RES_USER.STATUS = 1  
         FROM  RES_USER,CQ_LZ
         WHERE  RES_USER.ACCOUNT_ID = CQ_LZ.ACCOUNT_ID  AND  
             DATEDIFF ( DAY ,CQ_LZ.SXRQ, GETDATE ()) >= 0
         DELETE    FROM  CQ_LZ     WHERE   DATEDIFF ( DAY ,CQ_LZ.SXRQ, GETDATE ()) >= 0   -- 離職


         -- 生成崗位除名數據
        
         INSERT   INTO  CQ_GWHF(ACCOUNT_ID,BM,KB,ZXB,FULL_NAME)
         SELECT   TMP0033.ACCOUNT_ID, RES_USER.BMID,RES_USER.KBID,RES_USER.XBID,RES_USER.FULL_NAME  FROM
        (
             SELECT  ACCOUNT_ID
             FROM
            (
                 SELECT  ACCOUNT_ID, SUM (T)  AS  KGT
                 FROM
                (    
                      SELECT  ACCOUNT_ID, 
                                    ST,ET, datediff (HH,ST,ET)  as  T,
                                     CASE   WHEN   ( DATEDIFF ( DAY ,ST, GETDATE ()) >= 0 )  AND    ( DATEDIFF ( DAY ,ET, GETDATE ()) <= 0 )   THEN   1   ELSE   0   END   AS  KG
                     from  
                    (    
                              select  ACCOUNT_ID,
                                        case    when  KSSJ > convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' )     then  KSSJ  else   convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' )  end   as  ST,
                                                   case    when  JSSJ < dateadd ( day , day ( dateadd ( day , - 1 , convert ( char ( 8 ), dateadd ( month , 1 , getdate () ), 120 ) + ' 1 ' ))  - 1 ,  convert ( smalldatetime ,  CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' ))     then  JSSJ   else   dateadd ( day , day ( dateadd ( day , - 1 , convert ( char ( 8 ), dateadd ( month , 1 , getdate () ), 120 ) + ' 1 ' ))  - 1 ,  convert ( smalldatetime ,  CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' ))  end   as  ET
                             from  CQ_KG
                             where  KSSJ > convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' )
                    )  as  Tmp02
                ) AS  TMP31
                  GROUP   BY  ACCOUNT_ID
            )  AS  TMP0032
             WHERE  KGT > 72   -- 三天時間
        )  AS  TMP0033
         LEFT   JOIN  RES_USER
         ON  TMP0033.ACCOUNT_ID = RES_USER.ACCOUNT_ID

/**/ /*-        RES_USER 表中的STATUS
        0  表示 在職
        1 崗位除名狀態中
        2 離職
*/     
         UPDATE  RES_USER
         SET  RES_USER.STATUS = 2  
         FROM  RES_USER,CQ_GWHF
         WHERE  RES_USER.ACCOUNT_ID = CQ_GWHF.ACCOUNT_ID  AND  
             DATEDIFF ( DAY ,CQ_GWHF.CMRQ, GETDATE ()) >= 15   AND  CQ_GWHF.STATUS = 0   -- 崗位除名 離職

         UPDATE  CQ_GWHF
          SET  STATUS = 1
         WHERE   DATEDIFF ( DAY ,HFRQ, GETDATE ()) = 0   AND  STATUS = 0

         UPDATE  RES_USER
         SET  RES_USER.STATUS = 0  
         FROM  RES_USER,CQ_GWHF
         WHERE  RES_USER.ACCOUNT_ID = CQ_GWHF.ACCOUNT_ID  AND  
             DATEDIFF ( DAY ,CQ_GWHF.HFRQ, GETDATE ()) = 0   AND  CQ_GWHF.STATUS = 1   -- 崗位恢復

         UPDATE  RES_USER  -- 新進
         SET  RES_USER.CBID = CQ_XJ.XCB,RES_USER.BMID = CQ_XJ.XBM,RES_USER.KBID = CQ_XJ.XKB,RES_USER.XBID = CQ_XJ.XXB,
                RES_USER.ZW = CQ_XJ.XZW,RES_USER.BC = CQ_XJ.XBC
         FROM  RES_USER,CQ_XJ
         WHERE  RES_USER.BMID = ' NA '   AND  RES_USER.ACCOUNT_ID = CQ_XJ.ACCOUNT_ID  AND
                  DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
          DELETE    FROM  CQ_XJ  WHERE  ACCOUNT_ID  IN (  SELECT  ACCOUNT_ID   FROM  RES_USER )  AND    DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
        
         INSERT   INTO  RES_USER(ACCOUNT_ID,FULL_NAME,CBID,BMID,KBID,XBID,ZW,BC)
         SELECT  ACCOUNT_ID,FULL_NAME,XCB,XBM,XKB,XXB,XZW,XBC
         FROM  CQ_XJ
         WHERE    DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
         DELETE    FROM  CQ_XJ  WHERE     DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0


         INSERT   INTO  MFDREPORT(ACCOUNT_ID,FULL_NAME,CBID,BMID,KBID,XBID,BC,RQ)
         SELECT  ACCOUNT_ID,FULL_NAME,CBID,BMID,KBID,XBID,BC, CONVERT ( NVARCHAR ( 10 ), GETDATE (), 111 )  AS  RQ
         FROM  RES_USER
         WHERE  BMID <> ' NA '   AND  STATUS <> 2
GO