damixa
asked on
sql statement for same items where the flag is not the same across the board
so I have this table with lets say three fields
id primary key
staffid varchar
active bit
I would like to get a list of items where staffid has different active flags. You can have staffid with multiple locations, but they should have the same flag. So I want to generate a query of staffid where the "active" flag is not consistent (its not all either 0 or 1)
some staffid can occur 1 time in the list, (we want to ingnore those) some may have 2 occorunces, some 3 or even more. I would like to generate a list of those with more than 1 occurrence where the active flag is not consistent.
thanks in advance
id primary key
staffid varchar
active bit
I would like to get a list of items where staffid has different active flags. You can have staffid with multiple locations, but they should have the same flag. So I want to generate a query of staffid where the "active" flag is not consistent (its not all either 0 or 1)
some staffid can occur 1 time in the list, (we want to ingnore those) some may have 2 occorunces, some 3 or even more. I would like to generate a list of those with more than 1 occurrence where the active flag is not consistent.
thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Modified code from @jimhorn.
I think this is what you are looking for:
I think this is what you are looking for:
SELECT yt.id, yt.staffid, yt.active
FROM
YourTable yt
INNER JOIN (
SELECT staffid, COUNT(DISTINCT active) as active_distinct_count
FROM YourTable
GROUP BY staffid
HAVING COUNT(DISTINCT active) > 1
) ytdc ON yt.staffid = ytdc.staffid
ORDER BY yt.staffid, yt.active
select distinct t1.staffId from
table t1 join
table t2 on t1.staffId = t2.staffid and t1.active != t2.active
damixa, do you still need help with this question?
I think we've answered this multiple ways.
FROM table_name
GROUP BY staffid
HAVING MIN(active) <> MAX(active)