SQL窗功能无法在其他窗口函数或聚合
问题描述:
喜的上下文中使用有一点是用于我们的电话记录系统SQL的,我想看到许多调用如何突破4小时SLQ性反应的时间SQL窗功能无法在其他窗口函数或聚合
,我有问题的是,第33行我使用MIN,并得到一个错误,因为我也使用COUNT
(消息4109,级别15,状态1,行33个 窗函数不能在上下文中使用另一个窗口函数或聚合)。
任何更好的方式来编码或如何绕它
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'
答
这就像在子查询中抛出所有代码并执行计数一样简单。 BTW http://poorsql.com/是快速简单地编写代码的好工具。
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
将其中一个替换为子查询。 – 2015-03-25 13:00:47