如何在 Case 语句中仅显示聚合或标记的结果

问题描述

我有一个获取休息时间异常的查询.我使用 Case 语句来标记记录.问题是，我只想列出那些在跳过的休息时间或ShortBreak"列中被标记或有1"的人?

I have a query that gets break time exceptions. I use Case statements to flag the records. The problem is, I only want to list those that are flagged or have a "1" in the Skipped break, or "ShortBreak" column?

SELECT        lEmployeeID, sFirstName, sLastName, SUM(TotalHours) AS TotalHours, DATEDIFF(mi, MIN(dtTimeOut), MAX(dtTimeIn)) AS BreakTime, 
                         CASE WHEN SUM(ftc.TotalHours) > 6 AND DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) < 0 THEN 1 ELSE 0 END AS SkippedBreak, CASE WHEN DATEDIFF(mi, 
                         MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) < 30 AND DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) > 0 THEN 1 ELSE 0 END AS ShortBreak
FROM            dbo.fTimeCard(@StartDate, @EndDate, @DeptList, @iActive, @EmployeeList) AS ftc
WHERE        (DID IS NOT NULL) OR
                         (DID IS NOT NULL) AND (dtTimeOut IS NULL)
GROUP BY lEmployeeID, sFirstName, sLastName, TotalHours

推荐答案

您无法在查询的 WHERE 部分进行过滤，因为此时尚未执行分组.您可以在分组后使用 HAVING 子句进行额外过滤:

You cannot filter in the WHERE part of the query you've got, because the grouping is not yet performed at that point. You can use a HAVING clause to do additional filtering after grouping:

SELECT        lEmployeeID, sFirstName, sLastName, SUM(TotalHours) AS TotalHours, DATEDIFF(mi, MIN(dtTimeOut), MAX(dtTimeIn)) AS BreakTime, 
                     CASE WHEN SUM(ftc.TotalHours) > 6 AND DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) < 0 THEN 1 ELSE 0 END AS SkippedBreak, CASE WHEN DATEDIFF(mi, 
                     MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) < 30 AND DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) > 0 THEN 1 ELSE 0 END AS ShortBreak
FROM            dbo.fTimeCard(@StartDate, @EndDate, @DeptList, @iActive, @EmployeeList) AS ftc
WHERE        (DID IS NOT NULL) OR
                     (DID IS NOT NULL) AND (dtTimeOut IS NULL)
GROUP BY lEmployeeID, sFirstName, sLastName, TotalHours
HAVING SUM(ftc.TotalHours) > 6 AND DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) < 0
OR DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) < 30 AND DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) > 0

或者你可以使用子查询，并过滤子查询的结果:

Or you can use a subquery, and filter the results of the subquery:

SELECT *
FROM (
SELECT        lEmployeeID, sFirstName, sLastName, SUM(TotalHours) AS TotalHours, DATEDIFF(mi, MIN(dtTimeOut), MAX(dtTimeIn)) AS BreakTime, 
                     CASE WHEN SUM(ftc.TotalHours) > 6 AND DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) < 0 THEN 1 ELSE 0 END AS SkippedBreak, CASE WHEN DATEDIFF(mi, 
                     MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) < 30 AND DATEDIFF(mi, MIN(ftc.dtTimeOut), MAX(ftc.dtTimeIn)) > 0 THEN 1 ELSE 0 END AS ShortBreak
FROM            dbo.fTimeCard(@StartDate, @EndDate, @DeptList, @iActive, @EmployeeList) AS ftc
WHERE        (DID IS NOT NULL) OR
                     (DID IS NOT NULL) AND (dtTimeOut IS NULL)
GROUP BY lEmployeeID, sFirstName, sLastName, TotalHours
) AS sub
WHERE sub.SkippedBreak = 1 OR sub.ShortBreak = 1