员工周工时统计子查询结果中NULL值的去除方法

Hey there! Let's fix those annoying NULL values in your weekly hour columns. I spot the issue right away—even though you used ISNULL inside your SUM, if an employee has no time entries for a specific week, the entire subquery returns NULL (since there are no rows to sum). The ISNULL inside the SUM doesn't help here because SUM() on zero rows gives NULL, not 0. Let's sort this out with two approaches: fixing your original query first, then a cleaner alternative using PIVOT.

Fixing Your Original Query #

The key is to wrap the entire subquery in ISNULL() instead of just the HOURS column. Also, I swapped DISTINCT for GROUP BY (more efficient for grouping employee records) and fixed the possible typo in the last subquery (TR_DATE → Date to match the rest):

SELECT 
    EM.EMPLOYEE, 
    RTRIM(LTRIM(LAST_NAME)) + ', ' + RTRIM(LTRIM(FIRST_NAME)) AS [FULL NAME],
    ISNULL((SELECT SUM(HOURS) 
            FROM TimeTbl PT2 
            WHERE DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -1, GETDATE())) 
              AND PAYROLL_YEAR = '2020' 
              AND PT2.EMPLOYEE = PT.EMPLOYEE), 0) AS [WEEK 1 HOURS],
    ISNULL((SELECT SUM(HOURS) 
            FROM TimeTbl PT2 
            WHERE DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -2, GETDATE())) 
              AND PAYROLL_YEAR = '2020' 
              AND PT2.EMPLOYEE = PT.EMPLOYEE), 0) AS [WEEK 2 HOURS],
    ISNULL((SELECT SUM(HOURS) 
            FROM TimeTbl PT2 
            WHERE DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -3, GETDATE())) 
              AND PAYROLL_YEAR = '2020' 
              AND PT2.EMPLOYEE = PT.EMPLOYEE), 0) AS [WEEK 3 HOURS],
    ISNULL((SELECT SUM(HOURS) 
            FROM TimeTbl PT2 
            WHERE DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -4, GETDATE())) 
              AND PAYROLL_YEAR = '2020' 
              AND PT2.EMPLOYEE = PT.EMPLOYEE), 0) AS [WEEK 4 HOURS],
    ISNULL((SELECT SUM(HOURS) 
            FROM TimeTbl PT2 
            WHERE DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -5, GETDATE())) 
              AND PAYROLL_YEAR = '2020' 
              AND PT2.EMPLOYEE = PT.EMPLOYEE), 0) AS [WEEK 5 HOURS]
FROM TimeTbl PT
RIGHT JOIN EMPLYTBL EM ON PT.EMPLOYEE = EM.EMPLOYEE
GROUP BY EM.EMPLOYEE, LAST_NAME, FIRST_NAME

Cleaner Alternative: Using PIVOT #

Nested subqueries can get messy as you add more weeks. A PIVOT makes this logic more readable and maintainable:

WITH WeeklyHours AS (
    SELECT 
        EMPLOYEE,
        DATEPART(WK, Date) AS WeekNum,
        SUM(HOURS) AS TotalHours
    FROM TimeTbl
    WHERE PAYROLL_YEAR = '2020'
      AND DATEPART(WK, Date) BETWEEN DATEPART(WK, DATEADD(WK, -5, GETDATE())) AND DATEPART(WK, DATEADD(WK, -1, GETDATE()))
    GROUP BY EMPLOYEE, DATEPART(WK, Date)
)
SELECT 
    EM.EMPLOYEE,
    RTRIM(LTRIM(EM.LAST_NAME)) + ', ' + RTRIM(LTRIM(EM.FIRST_NAME)) AS [FULL NAME],
    ISNULL([WEEK-1], 0) AS [WEEK 1 HOURS],
    ISNULL([WEEK-2], 0) AS [WEEK 2 HOURS],
    ISNULL([WEEK-3], 0) AS [WEEK 3 HOURS],
    ISNULL([WEEK-4], 0) AS [WEEK 4 HOURS],
    ISNULL([WEEK-5], 0) AS [WEEK 5 HOURS]
FROM EMPLYTBL EM
LEFT JOIN (
    SELECT 
        EMPLOYEE,
        'WEEK-' + CAST(DATEDIFF(WK, Date, GETDATE()) AS VARCHAR) AS WeekAlias,
        TotalHours
    FROM WeeklyHours
) WH
PIVOT (
    SUM(TotalHours)
    FOR WeekAlias IN ([WEEK-1], [WEEK-2], [WEEK-3], [WEEK-4], [WEEK-5])
) PivotWH ON EM.EMPLOYEE = PivotWH.EMPLOYEE

Quick Notes #

• DATEPART(WK, ...) depends on your server's DATEFIRST setting. If you need consistent week boundaries (e.g., Monday to Sunday), consider calculating week start/end dates explicitly instead of relying on DATEPART(WK).
• Double-check that all subqueries use the same date column (Date, not TR_DATE) to avoid mismatched data.

内容的提问来源于stack exchange，提问作者Teepo Sampson