Eliminating Data where conditions Met

SQL 2008

I  have data as follows in table (Matter) as follows (example below)

Matterid              Project             MiniQ                Date
1                             1                     Y                         24/07/2015
2                             0                     N                        21/07/2015

I just want to eliminate matters in my query where Project = 1 and Mini Q = Y and Date >= 22/07/2015 (all three conditions must be met)

In the example above the query would only bring back matter 2

Just cant seem to get anything to work for this
Mark WilsonBI DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
Hi

Try this

SELECT * FROM Matter 
EXCEPT
SELECT * FROM MATTER WHERE Project =1 and MiniQ = 'Y' and Date >= '22/07/2015'

Open in new window

John TsioumprisSoftware & Systems EngineerCommented:
SELECT * FROM Matter
WHERE ID NOT IN (Select MatterID FROM Matter
WHERE Project=1
AND MiniQ= 'y'
AND Date >='22/07/2015')
PortletPaulEE Topic AdvisorCommented:
SELECT
      M.*
FROM Matter M
LEFT JOIN (
          SELECT
                MatterID
          FROM Matter
          WHERE Project = 1
          AND MiniQ = 'y'
          AND Date >= '20150722'
           ) X
      ON M.MatterID = x.MatterID
WHERE x.MatterID IS NULL

Open in new window

btw:
The safest date literal in SQL Server is the format YYYYMMDD

dd/mm/yyyy is "ambiguous" and can fail if defaults aren't setup for that formay
Vitor MontalvãoMSSQL Senior EngineerCommented:
--Eliminate the desired rows
DELETE FROM Matter
WHERE M2.Project = 1
     AND M2.MiniQ = 'Y'
     AND M2.Date >= '20150722'

--Check which rows were not eliminated
SELECT * FROM Matter

Open in new window

Scott PletcherSenior DBACommented:
SELECT *
FROM matter
WHERE NOT (Project = 1 and [Mini Q] = 'Y' and Date >= '20150722')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.