CODEWITHSUNDEEP
sqlsql-servert-sql
user1327064
user1327064

Reputation: 4337

Is there a better way to writing tsql query?

Here is the query:-

SELECT *  FROM table 
WHERE CutOffDate > '2013-05-23 00:00:00.001'
   AND Zone = 1
  OR id IN (SELECT id FROM table 
            WHERE  Zone = 1 
               and status = 1)

All i need is all records greater than cutoffdate in same Zone. Also if any record from same zone has status 1. And records are from same Table.

The above query is working fine. But I am hoping there is definitely a better way to write this kind of query.

Upvotes: 1

Views: 98

Answers (6)

Jodrell
Jodrell

Reputation: 35706

SELECT * FROM [table] 
    WHERE Zone = 1
        AND
           (CutOffDate > '2013-05-23 00:00:00.001'
            OR  status = 1)

This is the same as the query in your question. I can't really tell what you mean other than that.

Upvotes: 0

Serge
Serge

Reputation: 6692

You could rewrite it like this:

SELECT
    *
FROM table
WHERE CutOffDate > '2013-05-23 00:00:00.001'
AND (
    Zone = 1
    OR
    Status = 1
)

But I guess your original query isn"t really doing what you expect it to do...

EDIT: Just in case you actually figure your query doesn't do the job, this one might:

SELECT
    *
FROM table AS T1
WHERE T1.CutOffDate > '2013-05-23 00:00:00.001'
AND EXISTS (
    SELECT TOP(1)
        1
    FROM table AS T2
    WHERE T2.Zone = T1.Zone
    AND T2.Status = 1
)

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

Logically, your query is equivalent to:

SELECT *  FROM table 
WHERE Zone = 1
AND (CutOffDate > '2013-05-23 00:00:00.001' OR status = 1)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Assuming id is unique in the original table, then your logic is equivalent to:

SELECT *
FROM table 
WHERE (CutOffDate > '2013-05-23 00:00:00.001' AND Zone = 1)
      or (zone = 1 and status = 1);

You can further simplify this to:

SELECT *
FROM table 
WHERE zone = 1 and
       (CutOffDate > '2013-05-23 00:00:00.001' or status = 1);

If id is not unique, then your query is a good way of doing what you need. Alternatively, you could phrase the query using window functions.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146449

What do you mean by Same Zone ? the Same as what ?? The way the query is written it appears you mean Zone 1.

If so, then you have over complicated it. I believe you might try

SELECT *  FROM table 
WHERE Zone = 1
  And (status = 1 Or  CutOffDate 
        > '2013-05-23 00:00:00.001')

Upvotes: 2

Andomar
Andomar

Reputation: 238078

After the cutoff date, or a row with status 1 in the same zone:

SELECT  *  
FROM    YourTable yt1
WHERE   CutOffDate > '2013-05-23 00:00:00.001'
        OR EXISTS
        (
        SELECT  *
        FROM    YourTable yt2
        WHERE   yt1.Zone = yt2.Zone
                AND yt2.status = 1
        )

If that's not what you looking for, please clarify your question. :)

Upvotes: 0

Related Questions