如何在 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
相关文章