技术答疑.常用SQL.查询已超时的待办任务(SqlServer版)

【应用场景】

查询已超时的待办任务（对应执行计划中的任务【工作流超时处理服务】）

【案例演示】

<1>未启用工作流日历

SELECT  A.FAPPROVALASSIGNID ,
        A.FASSIGNID ,
        A.FOBJECTTYPEID ,
        A.FKEYVALUE ,
        A.FPOLICYXML ,
        A.FAPPROVALACTION ,
        A.FVARIABLENAME ,
        A.FDEFAULTRESULT ,
        A.FRESULT ,
        A.FISLOCKED ,
        A.FLOCKEDUSERID ,
        A.FISALLOWLOCK ,
        A.FISALLOWFORWARD ,
        A.FISALLOWBATCHDEAL ,
        A.FCANSPECIFYNEXT ,
        A.FTIMEOUTDEAL ,
        A.FTIMEOUTHOURS ,
        A.FTIMEOUTSTRATEGY ,
        A.FAUTOAPPROVAL ,
        A.FTIMEOUTDEALWITH
FROM    T_WF_APPROVALASSIGN A
        INNER JOIN ( SELECT T.FAPPROVALASSIGNID
                     FROM   T_WF_APPROVALASSIGN T
                            INNER JOIN T_WF_ASSIGN B ON B.FASSIGNID = T.FASSIGNID
                     WHERE  B.FSTATUS = '0'
                            AND T.FTIMEOUTDEAL = '1'
                            AND T.FDEALTIME < GETDATE()
                            - ( 1.0 * T.FTIMEOUTHOURS / 24 + 1.0
                                * T.FTIMEOUTMINUTE / 60 / 24 )
                            AND EXISTS ( SELECT 1
                                         FROM   T_WF_PROCINST C
                                         WHERE  B.FPROCINSTID = C.FPROCINSTID
                                                AND C.FSTATUS IN ( '0', '2' ) )
                   ) U ON U.FAPPROVALASSIGNID = A.FAPPROVALASSIGNID

<2>启用了工作流日历

SELECT  A.FAPPROVALASSIGNID ,
        A.FASSIGNID ,
        A.FOBJECTTYPEID ,
        A.FKEYVALUE ,
        A.FPOLICYXML ,
        A.FAPPROVALACTION ,
        A.FVARIABLENAME ,
        A.FDEFAULTRESULT ,
        A.FRESULT ,
        A.FISLOCKED ,
        A.FLOCKEDUSERID ,
        A.FISALLOWLOCK ,
        A.FISALLOWFORWARD ,
        A.FISALLOWBATCHDEAL ,
        A.FCANSPECIFYNEXT ,
        A.FTIMEOUTDEAL ,
        A.FTIMEOUTHOURS ,
        A.FTIMEOUTSTRATEGY ,
        A.FAUTOAPPROVAL ,
        A.FTIMEOUTDEALWITH
FROM    T_WF_APPROVALASSIGN A
        INNER JOIN ( SELECT T.FAPPROVALASSIGNID
                     FROM   T_WF_APPROVALASSIGN T
                            INNER JOIN T_WF_ASSIGN B ON B.FASSIGNID = T.FASSIGNID
                     WHERE  B.FSTATUS = '0'
                            AND T.FTIMEOUTDEAL = '1'
                            AND ( T.FCALENDARID > 0
                                  AND EXISTS ( SELECT   1
                                               FROM     T_WF_CALENDAR tc
                                               WHERE    tc.FID = T.FCALENDARID
                                                        AND tc.FFORBIDSTATUS = 'A' )
                                )
                            AND EXISTS ( SELECT 1
                                         FROM   T_WF_PROCINST C
                                         WHERE  B.FPROCINSTID = C.FPROCINSTID
                                                AND C.FSTATUS IN ( '0', '2' ) )
                            AND ( ISNULL(FDEALDATEHOURS, 0) --当天提交单据时剩余工作小时数（比如当天工作8小时，8点上班，9点提交单据，那么剩余工作小时是7小时）
                                  + ( SELECT    ISNULL(SUM(FTEMPCOSTHOURS), 0)
                                      FROM      T_WF_CALENDAR
                                      WHERE     FID = T.FCALENDARID
                                    )     -- 截止当前当天已耗工时（比如当天工作8小时，8点上班，10点的时候去查询是否超时，那么当前已使用工作小时数是2小时）
                                  - ( CASE WHEN DATEDIFF(DD, FDEALTIME,
                                                         GETDATE()) <= 0
                                           THEN ( SELECT    ISNULL(SUM(FWORKHOURS),
                                                              0)
                                                  FROM      T_WF_CALENDARDATA
                                                  WHERE     FID = T.FCALENDARID
                                                            AND FISWORKTIME = '1'
                                                            AND DATEDIFF(DD,