3

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
BramW
  • 31
  • 3

2 Answers2

1

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)
gotqn
  • 42,737
  • 46
  • 157
  • 243
0

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)

ismetguzelgun
  • 1,090
  • 8
  • 16
  • THANKS! very usefull tip. count was the sollutions – BramW Aug 14 '20 at 10:46
  • ) AS SRC PIVOT ( COUNT ([creationdate]) FOR [Code] IN ([C], [C1], [C1E], [CE])) AS PivotTable PIVOT ( COUNT ([Date] ) FOR [Description] IN ([Kooiaap certificaat] , [ADR certificaat] ,[LZV certificaat])) AS PivotTable – BramW Aug 14 '20 at 10:47
  • You should share your solution in your question as well. It would be great if you could upvote. Thanks! @BramW – ismetguzelgun Aug 14 '20 at 11:05
  • 1
    @BramW, it's good that you found a useful answer. Please consider accepting the answer by clicking on the grey check mark to the left. It rewards the person who answered with some reputation points, and you get some, too. You can also choose to up-vote the answer by clicking on the up arrow next to the answer. – Eric Brandt Aug 14 '20 at 12:15