SQL 查询：计算员工旷工时长和差旅月工时

你可以使用以下查询来实现你的需求：

SELECT
    name,
    bm,
    SUM(
        CASE
            WHEN bc LIKE '%白车身%' AND sb1dkjg = '缺卡' AND xb1dkjg = '缺卡' AND (gldspd IS NULL OR gldspd = '') THEN 480
            WHEN bc = '自由工时' AND sb1dksj IS NULL AND xb1dksj IS NULL AND (gldspd IS NULL OR gldspd = '') THEN 480
            ELSE 0
        END
    )/60.0 AS 旷工,
    ROUND(
        SUM(
            CASE
                WHEN bc = '自由工时' AND gzsc != 0 AND cqts = 1 THEN gzsc
                WHEN bc = '自由工时' AND gldspd LIKE '%出差%' AND gzsc = 0 AND cqts = 1 AND chuchai = 1 THEN 480
                WHEN bc = '自由工时' AND gzsc = 0 AND cqts = 0 AND chuchai = 1 THEN 480
                WHEN bc <> '自由工时' AND gldspd LIKE '%出差%' AND chuchai = 1 THEN 480
                ELSE 0
            END
        )/60.0,1) AS 差旅月工时
FROM Test_kqzhb
WHERE rq BETWEEN '2023-09-01' AND '2023-09-25' AND name = '蔡号强' AND sb2dksj IS NOT NULL AND xb2dksj IS NOT NULL AND chuchai <> 1
GROUP BY name, bm;

这个查询在原有的基础上增加了一个条件，即sb2dksj IS NOT NULL AND xb2dksj IS NOT NULL AND chuchai <> 1，表示只计算满足这个条件的记录。另外，查询结果中的'差旅月工时'的计算公式没有变化，仍然是根据不同的条件进行累计计算。