文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >不能在另一个窗口函数或聚合的上下文中使用SQL加窗函数。

问不能在另一个窗口函数或聚合的上下文中使用SQL加窗函数。
EN

Stack Overflow用户
提问于 2015-03-25 09:09:49
回答 1查看 3.7K关注 0票数 0

嗨，我们的呼叫记录系统使用了一些SQL，我想看看有多少个电话违反了4小时的SLQ响应时间。

我的问题是，在第33行，我使用MIN并得到一个错误，因为我也使用COUNT。

(Msg 4109、15级、State 1、第33行窗口函数不能在另一个窗口函数或聚合的上下文中使用。)

有什么更好的方法来对它进行编码或者如何绕过它

代码语言：javascript
复制
    SELECT Count(CASE 
               WHEN dbo.assignment.enddate IS NOT NULL 
                    AND dbo.assignment.enddate < ( CASE 
                                                     WHEN Datepart(dw, 
       dbo.dt_list_requests.createdon) = 
       1 THEN 
       Dateadd(dd, 1, ( Dateadd(hh, 11, ( 
                   Dateadd(dd, 0, Datediff 
                   (dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 7 THEN 
       Dateadd(dd, 2, ( Dateadd(hh, 11, ( Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6 
       AND dbo.dt_list_requests.createdon > Dateadd(hh, 17, ( 
       Dateadd(mi, 30, ( 
       Dateadd(dd, 0, Datediff( 
       dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 3, 
         ( 
       Dateadd(hh, 11, ( Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6 
       AND Dateadd(hh, 4, dbo.dt_list_requests.createdon) > 
       Dateadd(hh, 17, ( 
              Dateadd(mi, 30, 
              ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 3, 
         ( 
       Dateadd(hh, 17, ( Dateadd(mi, 30, 
       dbo.dt_list_requests.createdon) )) )) 
       WHEN dbo.dt_list_requests.createdon < Dateadd(hh, 7, ( 
       Dateadd(dd, 0, Datediff 
       (dd, 0, 
       dbo.dt_list_requests.createdon)) )) THEN Dateadd(hh, 11, ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) 
       WHEN dbo.dt_list_requests.createdon > Dateadd(hh, 17, ( 
           Dateadd(mi, 30, ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 1, ( 
       Dateadd(hh, 11, ( 
       Dateadd(dd, 0, 
       Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Dateadd(hh, 4, dbo.dt_list_requests.createdon) > 
       Dateadd(hh, 17, ( 
       Dateadd(mi, 30, ( Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(hh, 17, ( 
       Dateadd(mi, 30, dbo.dt_list_requests.createdon) )) 
       ELSE Dateadd(hh, 4, dbo.dt_list_requests.createdon) 
       END ) THEN NULL 
       WHEN dbo.dt_list_requests.respondedtoon IS NOT NULL 
       AND dbo.dt_list_requests.respondedtoon < ( CASE 
              WHEN Datepart(dw, 
       dbo.dt_list_requests.createdon) = 1 THEN 
       Dateadd(dd, 1, ( Dateadd(hh, 11, ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, 
       dbo.dt_list_requests.createdon) = 7 THEN 
       Dateadd(dd, 2, ( Dateadd(hh, 11, ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, 
       dbo.dt_list_requests.createdon) = 6 
       AND 
       dbo.dt_list_requests.createdon > Dateadd(hh, 17, ( 
       Dateadd(mi, 30, ( 
       Dateadd(dd, 0, Datediff( 
       dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 3, 
       ( 
       Dateadd(hh, 11, ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, 
       dbo.dt_list_requests.createdon) = 6 
       AND 
       Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, ( 
       Dateadd(mi, 30, 
       ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 3, 
       ( 
       Dateadd(hh, 17, ( Dateadd(mi, 30, 
       dbo.dt_list_requests.createdon) )) )) 
       WHEN 
       dbo.dt_list_requests.createdon < Dateadd(hh, 7, ( 
       Dateadd(dd, 0, Datediff 
       (dd, 0, 
       dbo.dt_list_requests.createdon)) )) THEN Dateadd(hh, 11, ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) 
       WHEN 
       dbo.dt_list_requests.createdon > Dateadd(hh, 17, ( Dateadd(mi, 30, ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 1, ( 
       Dateadd(hh, 11, ( 
       Dateadd(dd, 0, 
       Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN 
       Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, ( 
       Dateadd(mi, 30, ( Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(hh, 17, ( 
       Dateadd(mi, 30, 
       dbo.dt_list_requests.createdon) )) 
       ELSE 
       Dateadd(hh, 4, dbo.dt_list_requests.createdon) 
       END ) THEN NULL 
       WHEN dbo.dt_list_requests.assignment_startdate IS NOT NULL 
       AND dbo.dt_list_requests.assignment_startdate < ( CASE 
                     WHEN 
       Datepart(dw, dbo.dt_list_requests.createdon) = 1 THEN 
                     Dateadd(dd, 1, ( 
                     Dateadd(hh, 11, ( 
                     Dateadd(dd, 0, 
                     Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 7 THEN 
       Dateadd(dd, 2, ( Dateadd(hh, 11, ( Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6 
       AND dbo.dt_list_requests.createdon > Dateadd(hh, 17, ( 
               Dateadd(mi, 30, ( 
               Dateadd(dd, 0, Datediff( 
               dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 3, 
                          ( 
       Dateadd(hh, 11, ( Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6 
       AND Dateadd(hh, 4, dbo.dt_list_requests.createdon) > 
       Dateadd(hh, 17, ( 
       Dateadd(mi, 30, 
       ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 3, 
                          ( 
       Dateadd(hh, 17, ( Dateadd(mi, 30, 
       dbo.dt_list_requests.createdon) )) )) 
       WHEN dbo.dt_list_requests.createdon < Dateadd(hh, 7, ( 
           Dateadd(dd, 0, Datediff 
           (dd, 0, 
       dbo.dt_list_requests.createdon)) )) THEN Dateadd(hh, 11, ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) 
       WHEN dbo.dt_list_requests.createdon > Dateadd(hh, 17, ( 
           Dateadd(mi, 30, ( 
           Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 1, ( 
                      Dateadd(hh, 11, ( 
                      Dateadd(dd, 0, 
                      Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Dateadd(hh, 4, dbo.dt_list_requests.createdon) > 
       Dateadd(hh, 17, ( 
       Dateadd(mi, 30, ( Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(hh, 17, ( 
       Dateadd(mi, 30, dbo.dt_list_requests.createdon) )) 
       ELSE Dateadd(hh, 4, dbo.dt_list_requests.createdon) 
       END ) THEN NULL 
       WHEN Min(dbo.assignment.enddate) 
       OVER ( 
       partition BY dbo.assignment.requestid) < ( CASE 
                  WHEN 
       Datepart(dw, dbo.dt_list_requests.createdon) = 1 THEN 
                  Dateadd(dd, 1, ( 
                  Dateadd(hh, 11, ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 7 THEN 
       Dateadd(dd, 2, ( Dateadd(hh, 11, ( Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6 
       AND dbo.dt_list_requests.createdon > Dateadd(hh, 17, ( 
           Dateadd(mi, 30, ( 
           Dateadd(dd, 0, Datediff( 
           dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 3, 
                      ( 
       Dateadd(hh, 11, ( Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6 
       AND Dateadd(hh, 4, dbo.dt_list_requests.createdon) > 
       Dateadd(hh, 17, ( 
       Dateadd(mi, 30, 
       ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 3, 
                      ( 
       Dateadd(hh, 17, ( Dateadd(mi, 30, 
       dbo.dt_list_requests.createdon) )) )) 
       WHEN dbo.dt_list_requests.createdon < Dateadd(hh, 7, ( 
       Dateadd(dd, 0, Datediff 
       (dd, 0, 
       dbo.dt_list_requests.createdon)) )) THEN Dateadd(hh, 11, ( 
               Dateadd(dd, 0, 
               Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) 
       WHEN dbo.dt_list_requests.createdon > Dateadd(hh, 17, ( Dateadd(mi, 30, ( 
       Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(dd, 1, ( 
                  Dateadd(hh, 11, ( 
                  Dateadd(dd, 0, 
                  Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) 
       WHEN Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, ( 
       Dateadd(mi, 30, ( Dateadd(dd, 0, Datediff(dd, 0, 
       dbo.dt_list_requests.createdon)) )) )) THEN Dateadd(hh, 17, ( 
       Dateadd(mi, 30, dbo.dt_list_requests.createdon) )) 
       ELSE Dateadd(hh, 4, dbo.dt_list_requests.createdon) 
       END ) THEN NULL 
       ELSE '1' 
       END) AS Number_Breached 
FROM   dbo.request 
       INNER JOIN dbo.user_ 
               ON dbo.request.solvedbyuserid = dbo.user_.userid 
       INNER JOIN dbo.person 
               ON dbo.user_.personorganisationalunitid = dbo.person.ciid 
       INNER JOIN dbo.assignment 
               ON dbo.request.assignmentid = dbo.assignment.assignmentid 
       INNER JOIN dbo.dt_list_requests 
               ON dbo.request.requestid = dbo.dt_list_requests.requestid 
WHERE  ( dbo.person.jobtitle LIKE '%IS 1st Line Support%' ) 
       AND dbo.request.datecreated > Dateadd(month, Datediff(month, 0, Getdate() 
                                                    ) - 1, 
                                     0) 
       AND dbo.request.datecreated < Dateadd(month, Datediff(month, -1, Getdate( 
                                                    )) - 1, 
                                     -1) 
       AND dbo.request.requesttypeid = '1'
sql
sql-server
tsql
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2015-03-25 13:19:29

它就像在子查询中抛出所有代码并像这样执行计数一样容易。顺便说一句，http://poorsql.com/是一个非常好的工具，可以快速方便地格式化代码。

代码语言：javascript
复制
SELECT COUNT(Col_to_Be_Counted) AS Number_Breached
FROM
(
    SELECT CASE 
                WHEN dbo.assignment.enddate IS NOT NULL
                    AND dbo.assignment.enddate < (
                        CASE 
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 1
                                THEN Dateadd(dd, 1, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 7
                                THEN Dateadd(dd, 2, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6
                                AND dbo.dt_list_requests.createdon > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 3, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6
                                AND Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 3, (Dateadd(hh, 17, (Dateadd(mi, 30, dbo.dt_list_requests.createdon)))))
                            WHEN dbo.dt_list_requests.createdon < Dateadd(hh, 7, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))
                                THEN Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))
                            WHEN dbo.dt_list_requests.createdon > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 1, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(hh, 17, (Dateadd(mi, 30, dbo.dt_list_requests.createdon)))
                            ELSE Dateadd(hh, 4, dbo.dt_list_requests.createdon)
                            END
                        )
                    THEN NULL
                WHEN dbo.dt_list_requests.respondedtoon IS NOT NULL
                    AND dbo.dt_list_requests.respondedtoon < (
                        CASE 
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 1
                                THEN Dateadd(dd, 1, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 7
                                THEN Dateadd(dd, 2, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6
                                AND dbo.dt_list_requests.createdon > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 3, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6
                                AND Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 3, (Dateadd(hh, 17, (Dateadd(mi, 30, dbo.dt_list_requests.createdon)))))
                            WHEN dbo.dt_list_requests.createdon < Dateadd(hh, 7, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))
                                THEN Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))
                            WHEN dbo.dt_list_requests.createdon > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 1, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(hh, 17, (Dateadd(mi, 30, dbo.dt_list_requests.createdon)))
                            ELSE Dateadd(hh, 4, dbo.dt_list_requests.createdon)
                            END
                        )
                    THEN NULL
                WHEN dbo.dt_list_requests.assignment_startdate IS NOT NULL
                    AND dbo.dt_list_requests.assignment_startdate < (
                        CASE 
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 1
                                THEN Dateadd(dd, 1, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 7
                                THEN Dateadd(dd, 2, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6
                                AND dbo.dt_list_requests.createdon > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 3, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6
                                AND Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 3, (Dateadd(hh, 17, (Dateadd(mi, 30, dbo.dt_list_requests.createdon)))))
                            WHEN dbo.dt_list_requests.createdon < Dateadd(hh, 7, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))
                                THEN Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))
                            WHEN dbo.dt_list_requests.createdon > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 1, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(hh, 17, (Dateadd(mi, 30, dbo.dt_list_requests.createdon)))
                            ELSE Dateadd(hh, 4, dbo.dt_list_requests.createdon)
                            END
                        )
                    THEN NULL
                WHEN Min(dbo.assignment.enddate) OVER (PARTITION BY dbo.assignment.requestid) < (
                        CASE 
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 1
                                THEN Dateadd(dd, 1, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 7
                                THEN Dateadd(dd, 2, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6
                                AND dbo.dt_list_requests.createdon > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 3, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Datepart(dw, dbo.dt_list_requests.createdon) = 6
                                AND Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 3, (Dateadd(hh, 17, (Dateadd(mi, 30, dbo.dt_list_requests.createdon)))))
                            WHEN dbo.dt_list_requests.createdon < Dateadd(hh, 7, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))
                                THEN Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))
                            WHEN dbo.dt_list_requests.createdon > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(dd, 1, (Dateadd(hh, 11, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                            WHEN Dateadd(hh, 4, dbo.dt_list_requests.createdon) > Dateadd(hh, 17, (Dateadd(mi, 30, (Dateadd(dd, 0, Datediff(dd, 0, dbo.dt_list_requests.createdon))))))
                                THEN Dateadd(hh, 17, (Dateadd(mi, 30, dbo.dt_list_requests.createdon)))
                            ELSE Dateadd(hh, 4, dbo.dt_list_requests.createdon)
                            END
                        )
                    THEN NULL
                ELSE '1'
                END AS Col_to_Be_Counted
    FROM dbo.request
    INNER JOIN dbo.user_ ON dbo.request.solvedbyuserid = dbo.user_.userid
    INNER JOIN dbo.person ON dbo.user_.personorganisationalunitid = dbo.person.ciid
    INNER JOIN dbo.assignment ON dbo.request.assignmentid = dbo.assignment.assignmentid
    INNER JOIN dbo.dt_list_requests ON dbo.request.requestid = dbo.dt_list_requests.requestid
    WHERE (dbo.person.jobtitle LIKE '%IS 1st Line Support%')
        AND dbo.request.datecreated > Dateadd(month, Datediff(month, 0, Getdate()) - 1, 0)
        AND dbo.request.datecreated < Dateadd(month, Datediff(month, - 1, Getdate()) - 1, - 1)
        AND dbo.request.requesttypeid = '1'
) A
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/29251458

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档