CODEWITHSUNDEEP
sqlcoalesce
Tim Richards
Tim Richards

Reputation: 324

COALESCE not working?

I have NULLS in my P.EMP_PAY_DUE_TO_LEAVE_DATE field and I've tried eliminating them by using the following bit of code but the NULLS remain!

SELECT DISTINCT V.EMP_CODE
, CASE 
    WHEN V.HIST_PERIOD < 10 
    THEN 
        CAST(V.HIST_YEAR AS VARCHAR)     + RIGHT('0' + CAST (V.HIST_PERIOD + 3 AS VARCHAR), 2) 
    ELSE 
        CAST(V.HIST_YEAR + 1 AS VARCHAR) + RIGHT('0' + CAST (V.HIST_PERIOD - 9 AS VARCHAR) ,2) 
  END AS PAYPERIOD
, V.Department_Id
, CASE 
    WHEN MONTH (P.EMP_PAY_DUE_TO_LEAVE_DATE) >= MONTH(DATEADD(M, -1, GETDATE()) 
    OR P.EMP_PAY_DUE_TO_LEAVE_DATE IS NULL 
    THEN 
        COALESCE
        (
            CONVERT
            (
                DECIMAL(10, 2)
                , V.EMP_SORT_DESC
            )
            , 0
        ) / 37.5
    END AS FTE

I can't see anything wrong with my code but clearly there is! Can anyone see the problem?

Upvotes: 1

Views: 835

Answers (1)

Yuck
Yuck

Reputation: 50835

Your last...

CASE
  WHEN (MONTH (P.EMP_PAY_DUE_TO_LEAVE_DATE) >= MONTH(DATEADD(M, -1, GETDATE())) OR P.EMP_PAY_DUE_TO_LEAVE_DATE IS NULL) 
    THEN (COALESCE (CONVERT (DECIMAL (10, 2) ,V.EMP_SORT_DESC), 0) / 37.5) END AS FTE

does not have an ELSE case, which implicity becomes NULL if the THEN case doesn't apply.

Upvotes: 3

Related Questions