CODEWITHSUNDEEP
sqlsql-serverdate
user2210516
user2210516

Reputation: 683

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.

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

Upvotes: 0

Views: 81

Answers (2)

mkRabbani
mkRabbani

Reputation: 16918

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

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 13009

You can try below CASE logic. 

CASE WHEN HiringDate IS NULL THEN 'InActive'
     WHEN CAST(HiringDate AS DATE) > CAST(GETDATE() AS DATE) THEN 'InActive'
     WHEN HiringDate IS NOT NULL AND CAST(ResignDate AS DATE) < CAST(GETDATE() AS DATE) THEN 'InActive'
     ELSE 'Active'
     END As Status

Upvotes: 1

Related Questions