( CASE WHEN [date] IS NULL THEN 0 ELSE 1 END) is not working in my pivot

user_14103533 0
Viewed 1324

I have written the follwoing code expecting to get 0's and 1's returned in the CASES but I get 1's and NULL's.

any suggestions to get 0's?

DECLARE @Today DATETIME

SET @Today = FLOOR(CAST(GETDATE() AS FLOAT))

SELECT*

FROM 
    (   SELECT e.[Employeenr],
           e.[Name],
           dc.[Code],
           c.[Description],
           (CASE WHEN ec.[date] IS NULL THEN 0 ELSE 1 END) as 'Date',
           (CASE WHEN dc.[creationdate] IS NULL THEN 0 ELSE 1 END) as 'Aanwezig'
FROM HR_Employee e
        left join HR_EmployeeDriverLicense d ON e.[Employeenr] = d.[Employee]
        left join DriverLicenseCategory dc ON d.[DriverLicenseCategory] = dc. 
        [DriverLicenseCategorynr]
        left join HR_EmployeeCertificate ec ON e.[Employeenr] = ec.[Employee]
        left join HR_Certificate c ON ec.[Certificate] = c.[Certificatenr]
        left join HR_Function f ON e.[Function] = f.[Functionnr]

     WHERE (e.[Date_out_of_employment] IS NULL 
        or e.[Date_out_of_employment] >= @today 
        or e.[Licensenumber] is not null) 
        and e.[LicenseExpireDate] is not null 
        and c.[Description] is not null 
        and ec.[Certificate] <> 5 
        and f.[Functionnr] = 1

 ) AS SRC 
 PIVOT
 ( MAX(Aanwezig) 
  FOR [Code] IN ([C], [C1], [C1E], [CE])) AS PivotTable 

  PIVOT
 ( MAX ([Date]) 
    FOR [Description] IN ([Kooiaap certificaat], [ADR Certificaat])) AS PivotTable
pivot isnull tsql sql-server sql
2 Answers

When you are performing PIVOT you will get a NULL for rows, which do not have a value for the corresponding PIVOT column. Unfortunately, if you want to get 0 instead NULL, you need to add this logic in the final SELECT statement.

Something like this:

SELECT ISNULL([Kooiaap certificaat], 0), ISNULL([ADR Certificaat], 0)
user_10803540

Instead of using MAX use COUNT function and CASE WHEN together.

Try below.

COUNT(CASE WHEN AANWEZIG= 0 THEN NULL WHEN AANWEZIG= 1 THEN 1 END)

user_47013890
Related