如何只显示总标记或者在case语句结果(how to display only aggregate

我有一个查询，获得休息时间的例外。 我用Case语句来标记的记录。 问题是，我只想列出那些被标记或有一个“1”的跳过休息，或“小憩”列？

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

Answer 1:

你不能在过滤WHERE你有查询的一部分，因为分组还没有在这一点上进行。 您可以使用HAVING子句分组后做额外的过滤：

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

或者你可以使用子查询和过滤子查询的结果：

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

Answer 2:

您需要添加HAVING

...
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
))

文章来源: how to display only aggregate or flagged results in a Case statement