How do I construct my WHERE clause to pull records based on this specific criteria?

I am using SQL Server 2014 and I have a T-SQL query running against a specific Table (t1). Table t1 includes 3 columns named [HR_LeftDate], [Payroll_LeftDate] and [Status].

In that table, there are records which have their [HR_LeftDate] and [Payroll_LeftDate] as ‘NULL’ and some other records which have a BLANK (for both columns).

I need to extract those records and my T-SQL query stands as follows:

SELECT *
FROM [t1]
WHERE ([HR_leftDate] IS NULL AND  [Payroll_leftDate] IS NULL )
   OR ([HR_leftDate] = '' AND  [Payroll_leftDate] = '') AND [Status] = 'Left'

Something must be wrong in my WHERE clause where I am filtering the [HR_LeftDate] and [HR_PayrollDate] as I am not getting those specific records.

Any help would be appreciated.

Answer

You can apply ISNULL clause to make the nullable values as empty strings and do comparison like given below:

SELECT * FROM [t1]
WHERE ISNULL([HR_leftDate],'') = ''  AND ISNULL([Payroll_leftDate],'') = ''
AND [Status] = 'Left'