CODEWITHSUNDEEP
sqlsql-server
SuperDOS
SuperDOS

Reputation: 331

SQL Query of disabled users with no active employment

Have a table with all our users and their employment.

when someone is reemployed a new row will be added for that employment period. Username will always be the same.

Trying to figure out the best approach to find which users that is disabled and doesn't have an active employment. The database is SQL Server 2016.

Example table:

|  username | name      | active | enddate 
+-----------+-----------+--------+----------
| 1111      | Jane Doe  |    1   | 1/3/2022
| 1111      | Jane Doe  |    0   | 1/2/2018
| 1112      | Bob Doe   |    1   | NULL
| 1113      | James Doe |    0   | 1/3/2018
| 1114      | Ray Doe   |    1   | NULL
| 1114      | Ray Doe   |    0   | 2/2/2019
| 1115      | Emma Doe  |    1   | NULL
| 1116      | Sara Doe  |    0   | 3/4/2016
| 1116      | Sara Doe  |    0   | 4/5/2019

Ideally I would also like to get only one row per username even if they have several employments that has ended.

So a query would get me a list of two rows with user 1113 and 1116.

Upvotes: 0

Views: 695

Answers (7)

EzLo
EzLo

Reputation: 14189

I found other answers to be hard to read (or not giving what you are asking for), compared to this one:

SELECT DISTINCT
    U.username
FROM
    UserEmployment AS U
WHERE
    U.active = 0 AND
    NOT EXISTS (SELECT 'no current active employment' 
                FROM UserEmployment AS C
                WHERE U.username = C.username AND 
                C.active = 1 AND
                (C.enddate IS NULL OR C.enddate >= CONVERT(DATE, GETDATE())))

But it's personal taste. This searches for all disabled users that don't have any active record for today.

Just make sure that your enddate column is actually DATE and not VARCHAR, because it might give you conversion problems.

If you want to display all columns but once per username, then we will have to join back against a disabled record. We can control the amount of rows by using CROSS APPLY with TOP N + ORDER BY:

;WITH NonActiveDisabledUsers AS
(
    SELECT DISTINCT
        U.username
    FROM
        UserEmployment AS U
    WHERE
        U.active = 0 AND
        NOT EXISTS (SELECT 'no current active employment' 
                    FROM UserEmployment AS C
                    WHERE U.username = C.username AND 
                    C.active = 1 AND
                    (C.enddate IS NULL OR C.enddate >= CONVERT(DATE, GETDATE())))
)
SELECT
    R.*
FROM
    NonActiveDisabledUsers AS N
    CROSS APPLY (
        SELECT TOP 1        -- Just 1 record
            U.*
        FROM
            UserEmployment AS U
        WHERE
            N.username = U.username AND
            U.active = 0
        ORDER BY
            U.enddate DESC  -- Determine which record should we display
        ) AS R

Upvotes: 1

TS Kahlon
TS Kahlon

Reputation: 106

Following query should work for your solution:

SELECT UserName
    ,Name
    ,Max(EndDate) AS EndDate
FROM employment
GROUP BY UserName
    ,Name
HAVING username IN (
        SELECT username
        FROM Employment
        WHERE active = 0
            AND enddate IS NOT NULL
            AND username NOT IN (
                SELECT username
                FROM Employment
                WHERE active = 1
                )
        )

Resulted output:

1113    James   2018-01-03
1116    Sara    2019-04-05

Upvotes: 0

SQLChao
SQLChao

Reputation: 7847

One possibility is to get the max active. If it isn't 0 then that means they are active.

SELECT 
  username
, name
FROM dbo.YourTable
GROUP BY username
HAVING MAX(CAST(active AS INT)) = 0

Upvotes: 0

David Barclay
David Barclay

Reputation: 45

Only things I can add to above are that you'd be best putting username (name) into a lookup table so they retain the same unique key but if their name changes they hold the same reference in the data (for example if Sara Doe gets married).

Use

Select DISTINCT...and ORDER BY enddate DESC

To return only one result and that being the most rescent.

Upvotes: 0

mzaifquraishi
mzaifquraishi

Reputation: 86

you can do it like this

|  username | name      | active | enddate |
--------------------------------------------
| 1111      | Jane Doe  |    1   | 
| 1111      | Jane Doe  |    0   | 1/2/2018
| 1112      | Bob Doe   |    1   |
| 1113      | James Doe |    1   | **30/12/2019**
| 1114      | Ray Doe   |    1   |
| 1114      | Ray Doe   |    0   | 2/2/2019
| 1115      | Emma Doe  |    1   |
| 1116      | Sara Doe  |    0   | 3/4/2016
| 1116      | Sara Doe  |    1   | **30/12/2019**

here you can create query like 

Select * from employee where active=1 and enddate>=getdate()

so employee who are active and who's employment enddate is not over

Upvotes: 0

GMB
GMB

Reputation: 222482

If you are looking for users whose greatest end date is in the past and that have no null end date, then you can do:

select username, name, max(enddate) enddate
from mytable
group by username, name
having 
    max(enddate) < getdate() 
    and max(case when enddate is null then 1 end) is null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269833

Assuming end dates are all in the past, this should work:

select username, name
from t
group by username, name
having sum(case when enddate is null then 1 else 0 end) = 0;  -- no NULL values

Upvotes: 0

Related Questions