CODEWITHSUNDEEP
sql
Sathish
Sathish

Reputation: 25

SQL Server Where condition optimisation

Hi I am new to SQL Server 2008 , when i execute the below query in SQL Server its not displaying data from the source table. I believe the below query can be optimized to get the data correctly. If anyone helps me to do that, i will be very thankful to them.

SELECT *
FROM   Report
WHERE  ( ( Create_Date BETWEEN '10/10/2013 16:00:00' AND '10/16/2013 15:59:59' )
          OR ( Date_Resolved BETWEEN '10/10/2013 16:00:00' AND '10/16/2013 15:59:59' )
          OR ( ( ( Create_Date < '10/10/2013 16:00:00' )
                 AND ( Date_Resolved > '10/10/2013 16:00:00' ) )
                OR ( ( Create_Date < '10/10/2013 16:00:00' )
                     AND ( IsDate(Date_Resolved) IS NULL ) ) )
          OR ( ( ( Create_Date < '10/16/2013 15:59:59' )
                 AND ( Date_Resolved > '10/16/2013 15:59:59' ) )
                OR ( ( Create_Date < '10/16/2013 15:59:59' )
                     AND ( IsDate(Date_Resolved) IS NULL ) ) ) )

Upvotes: 0

Views: 82

Answers (1)

AdamL
AdamL

Reputation: 13161

This query is a mess. I removed unnecessary brackets, reorganized and merged conditions, formatted the sql and removed duplicated OR conditions (one says: Create_Date<'10/16/2013 15:59:59', second says Create_Date < '10/10/2013 16:00:00' and both say IsDate (Date_Resolved) IS NULL, so you can safely delete the former. And it's done two times :)).

Here's what you get, and it does exactly the same thing:

SELECT 
    *
FROM Report
WHERE  

Create_Date BETWEEN '10/10/2013 16:00:00' AND '10/16/2013 15:59:59' 
OR 
Date_Resolved BETWEEN '10/10/2013 16:00:00' AND '10/16/2013 15:59:59' 
OR ( 
    Create_Date < '10/16/2013 15:59:59' 
    AND 
    Date_Resolved > '10/16/2013 15:59:59' 
)
OR ( 
    Create_Date < '10/10/2013 16:00:00' 
    AND (
        Date_Resolved > '10/10/2013 16:00:00' 
        OR
        IsDate (Date_Resolved) IS NULL 
    )
)

I still don't know why it's not working, but it should be easier to figure this out now :). Btw, what does isDate() do, and what's your rdbms? Could you post sample data and a query on fiddle?

Upvotes: 2

Related Questions