Problems finding out Active users due to Hire/Resign Date

In my Employee table in SQL Server I have two datetime columns: HiringDate and ResignDate. I want to create a new column Status (Active, Inactive) in a view.

• If HiringDate is NULL or greater than today = Inactive
• If HiringDate is Active but ResignDate is earlier than today then Status also have to be Inactive.

Do I have to make some kind of nested case to make this work and I’m also wondering do I have to convert it to date format so the time portion are not included.

Would be very thankful for help.

Here is what ive tried so far but it doesnt work properly…

CASE 
            WHEN CONVERT(DATE,HiringDate) IS NOT NULL 
            OR CONVERT(DATE,HiringDate) <= CONVERT(DATE,GETDATE()) 
            THEN 
                CASE 
                    WHEN CONVERT(DATE,ISNULL(ResignDate, CONVERT(DATE,'2099-12-30'))) <= CONVERT(DATE,GETDATE()) 
                    THEN 'Active'
                    ELSE 'Inactive'
                END 
            ELSE 'Inactive' 
        END as Status

Answer

Try this below combine logic in one CASE expression:

CASE 
    WHEN HiringDate IS NOT NULL 
        or HiringDate >= GETDATE()
        or ResignDate >= GETDATE() 
    THEN 'Active' 
    ELSE 'Inactive' 
END as Status

Can you please try with this below new logic-

CASE 
    WHEN (
        CONVERT(DATE,HiringDate) IS NOT NULL 
        OR 
        CONVERT(DATE,HiringDate) <= CONVERT(DATE,GETDATE()
    )
    AND
    CONVERT(DATE,ISNULL(ResignDate, CONVERT(DATE,'2099-12-30'))) 
            <= CONVERT(DATE,GETDATE()) 
    THEN 'Active'

    ELSE 'Inactive' 
END as EmployeeStatus