问题描述
我有一个获取休息时间异常的查询.我使用 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