我有一个包含用户每月出勤详细信息的表格,其中包含加班列。因此,该表具有以下结构和示例数据:
Date - Id - Dept - OT - Status
12/1/2019 - 1001 - HR - 2 - P
12/2/2019 - 1001 - HR - 4 - P
12/1/2019 - 1002 - IT - 2 - P
12/2/2019 - 1002 - IT - 4 - P
我在这里省略了进出时间,因为这不是这里的主要问题。见上文,加班是针对每个用户计算的。因此,很容易计算用户或部门的加班总详细信息,如下所示:
SELECT DEPT, SUM(OT) TOTAL FROM ATTENDANCE m WHERE M.DATE >= '01-DEC-2019' AND M.DATE <= '10-DEC-2019'
GROUP BY M.DEPT
我的要求是获取具有特定日期范围的部门的每周或 7 天加班详细信息,加班范围为 40。例如:对于某个部门,日期范围为 2019 年 12 月 7 日,总加班时间最长为 40 小时或介于 41 到 50 小时之间。所以我尝试了以下方法:
SELECT DEPT, SUM(OT) TOTAL FROM ATTENDANCE m WHERE M.DATE >= '01-DEC-2019' AND M.DATE <= '07-DEC-2019'
HAVING SUM(OT) <= 40 GROUP BY M.DEPT
或:
SELECT DEPT, SUM(OT) TOTAL FROM ATTENDANCE m WHERE M.DATE >= '01-DEC-2019' AND M.DATE <= '07-DEC-2019'
HAVING SUM(OT) BETWEEN 41 AND 50 GROUP BY M.DEPT
或:
SELECT DEPT, SUM(OT) TOTAL FROM ATTENDANCE m WHERE M.DATE >= '01-DEC-2019' AND M.DATE <= '07-DEC-2019'
HAVING SUM(OT) > 50 GROUP BY M.DEPT
但是最后一个查询似乎返回奇怪的输出,并且似乎返回了我用第一个查询检索到的数据。这些是我厌倦的单独查询,但我在想我是否可以管理它以使用具有上述所有范围CASE WHEN
,如下所示:
CASE WHEN M.DATE >= '01-DEC-2019' AND M.DATE <= '07-DEC-2019'
AND SUM(OT) <= 40 THEN SUM(OT)
CASE WHEN M.DATE >= '01-DEC-2019' AND M.DATE <= '07-DEC-2019'
AND SUM(OT) BETWEEN 41 AND 50 THEN SUM(OT)
我不确定聚合函数是否可以与CASE WHEN
语句一起使用,或者我可以使用上述方法在查询中运行多个范围的任何方式?谢谢。
你能更明确地说明你想要的输出是什么吗?似乎您正在寻找两个结果(加班时间长达40 小时的部门,以及在给定日期范围内加班时间超过40 小时的部门(?
查看您的最后一个查询:
CASE WHEN M.DATE >= '01-DEC-2019' AND M.DATE <= '07-DEC-2019'
AND SUM(OT) <= 40 THEN SUM(OT)
CASE WHEN M.DATE >= '01-DEC-2019' AND M.DATE <= '07-DEC-2019'
AND SUM(OT) BETWEEN 41 AND 50 THEN SUM(OT)
鉴于应用的日期范围相同,您可以将其移至WHERE
子句吗?然后,您可以使用子查询或其他方法按使用的加班量对部门进行分组(如下所示(:
SELECT
Dept
, CASE
WHEN TotalOvertime <= 40 THEN 'something'
WHEN (TotalOvertime > 40 AND <= 50) THEN 'something_else'
ELSE 'exception'
END AS OvertimeCaseStatement
FROM (
SELECT Dept
, SUM(OT) AS TotalOvertime
FROM Attendance
WHERE Date BETWEEN TO_DATE('01-DEC-2019', 'DD-MON-YYYY') AND TO_DATE('07-DEC-2019', 'DD-MON-YYYY')
GROUP BY Dept
)
;
我还删除了 int 比较中的BETWEEN
运算符,因为我认为使用逻辑运算符更清晰一些(BETWEEN
是一个包含运算符(,并添加了一些不好的名称,如something
、something_else
等,您可以更改更多描述性名称。
如果我正在解决您的实际问题,以及这种方法是否有新的错误/问题,请告诉我!
还有 FWIW,如果您Date
列是日期而不是字符串,则CAST
参数('01-DEC-2019'
等(为日期类型可能会有所帮助。希望这有帮助!
已编辑:根据@mathguy的良好标注添加了带有TO_DATE()
的日期投射(我假设Date
已经是日期类型列(。
您可以使用CASE
来区分所有值。例如:
select
dept,
sum(ot) as total,
case when sum(ot) <= 40 then 'g1'
when sum(ot) > 40 and sum(ot) <= 50 then 'g2'
else 'g3' end as g
from attendance
where between to_date('2019-12-01', 'yyyy-mm-dd')
and to_date('2019-12-07', 'yyyy-mm-dd')
group by dept