Search code examples
sqlsql-server

Exclude a row based on the status

I'm trying to get a list of documents from the Documents table that have either a status of Effective or Pending Effective. If the document number has both statuses (multiple rows) , I want to ONLY return the Pending Effective row and exclude the Effective one.

For example, with this query:

SELECT
    Name,
    Number,
    Revision,
    Status
        
FROM Document
        
WHERE 
    Number = 'D001234' AND
    Status IN ('Pending Effective', 'Effective')

I get these results:

Name Number Revision Status
DocName D001234 1 Effective
DocName D001234 2 Pending Effective

How do I adjust this query such that:

  • If the document number has both statuses (multiple rows), it only returns the Pending Effective row.
  • If the document number only has an Effective row, just return that.

EDIT -

Here is some sample data (dumbed down):

Name Number Revision Status
DocName D001000 1 Effective
DocName D001200 2 Effective
DocName D001234 1 Effective
DocName D001234 2 Pending Effective
DocName D003000 5 Effective

The desired result of the updated query would yield:

(Notice the D001234 row with status "Effective" was excluded since the results had a "Pending Effective" row of the same Number)

Name Number Revision Status
DocName D001000 1 Effective
DocName D001200 2 Effective
DocName D001234 2 Pending Effective
DocName D003000 5 Effective
Solution 
  • SELECT
  Name,
  Number,
  Revision,
  Status  
FROM
  Document
WHERE
  (Status = 'Pending Effective') OR
  (
    (Status = 'Effective') AND
    NOT EXISTS (
      SELECT * FROM Document AS d WHERE (Document.Number = d.Number) AND
      (d.Status = 'Pending Effective')
    )  
  )

    The SQL query selects documents that are either 'Pending Effective' or 'Effective' (provided there are no pending documents with the same number).

    This example uses a subquery. In the subquery, we look for a document with the same number but with the status 'Pending Effective'. If such a document is found, we do not include the document with the status 'Effective' in the result set.