I am writing a SQL Query that returns a list of Cost Center's that are closed. This is known by a 'C' in the TE_INACTIVE Column.
The issue: Some Cost Centers were moved to a different company, so there is a duplicate of 3 cost centers that are both closed and open. Since this cost center was closed and re-opened, I do not want to read it in anymore since the new one doesn't have a 'C'. How can I do this?
In other words, if one of the duplicate Cost Center's doesn't have a 'C' in it, then I want to skip it.
Here is (part of) my original query and a screenshot of what I mean.
select distinct TE_COST_CENTER, TE_INACTIVE from ABCS_TABLE_E where
TE_COST_CENTER = '38M'
You should use the "NOT EXISTS" operator:
select distinct TE_COST_CENTER, TE_INACTIVE from ABCS_TABLE_E t1 where TE_INACTIVE = 'C' and not exists (select * from ABCS_TABLE_E t2 where t2.TE_COST_CENTER = t1.TE_COST_CENTER and t2.TE_INACTIVE <>'C')