locked
Sql using OR condation RRS feed

  • Question

  • User-172769993 posted

    Hi lets assume i have below sample data

    Service_ID  Service_RNO Cust_Name   Agrement_ID Cust_ID Service_Date    Next_Service
    4                004    customer1   35           16     2016-01-03      2016-02-02
    5                005    customer1   35           16     2016-02-03      2016-03-01

    and if i have the below query that check if Next_Service is not in Service_Date

    SELECT  dbo.Service.Service_ID, 
            dbo.Service.Service_RNO, 
            dbo.Customer.Cust_Name,
            dbo.Service.Agrement_ID,
            dbo.Customer.Cust_ID,
            dbo.Service.Service_Date,
            dbo.Service.Next_Service 
    FROM dbo.Service 
    INNER JOIN dbo.Customer ON dbo.Service.Cust_ID = dbo.Customer.Cust_ID 
    WHERE  Next_Service between '2016-02-01' and '2016-02-06' 
    AND    Next_Service not in (
              SELECT Service_Date 
              FROM Service s
              WHERE s.Cust_ID = dbo.Customer.Cust_ID
    ) 
    ORDER BY Next_Service

    output of above query will be the second record

    Note that Service_Date values in most cases is entered same as Next_Service value in previous record for that customer but i have some cases where  Service_Date is entered after one or two days

    so i tried to use the blow query to solve the issue i have

    SELECT  dbo.Service.Service_ID, 
            dbo.Service.Service_RNO, 
            dbo.Customer.Cust_Name,
            dbo.Service.Agrement_ID,
            dbo.Customer.Cust_ID,
            dbo.Service.Service_Date,
            dbo.Service.Next_Service 
    FROM dbo.Service 
    INNER JOIN dbo.Customer ON dbo.Service.Cust_ID = dbo.Customer.Cust_ID 
    WHERE  Next_Service between '2016-02-01' and '2016-02-06' 
    AND    Next_Service not in (
              SELECT Service_Date
              FROM Service s
              WHERE s.Cust_ID = dbo.Customer.Cust_ID
    ) 
    or    Next_Service not in (
              SELECT Dateadd(d, -1, Service_Date) 
              FROM Service s
              WHERE s.Cust_ID = dbo.Customer.Cust_ID
    ) 
    ORDER BY Next_Service

    output of above query should be empty, but the output I get is the two records what is the issue?

    Friday, February 5, 2016 11:20 PM

Answers

  • User465171450 posted

    Have you tried grouping your or condition? That may alter the resultset. Without parentheses around the logic groupings, SQL Sever can sometimes do very unexpected things.

    SELECT  dbo.Service.Service_ID, 
            dbo.Service.Service_RNO, 
            dbo.Customer.Cust_Name,
            dbo.Service.Agrement_ID,
            dbo.Customer.Cust_ID,
            dbo.Service.Service_Date,
            dbo.Service.Next_Service 
    FROM dbo.Service 
    INNER JOIN dbo.Customer ON dbo.Service.Cust_ID = dbo.Customer.Cust_ID 
    WHERE  Next_Service between '2016-02-01' and '2016-02-06' 
    AND    (
    Next_Service not in ( SELECT Service_Date FROM Service s WHERE s.Cust_ID = dbo.Customer.Cust_ID ) or Next_Service not in ( SELECT Dateadd(d, -1, Service_Date) FROM Service s WHERE s.Cust_ID = dbo.Customer.Cust_ID )
    ) ORDER BY Next_Service

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 6, 2016 3:05 AM

All replies

  • User465171450 posted

    Have you tried grouping your or condition? That may alter the resultset. Without parentheses around the logic groupings, SQL Sever can sometimes do very unexpected things.

    SELECT  dbo.Service.Service_ID, 
            dbo.Service.Service_RNO, 
            dbo.Customer.Cust_Name,
            dbo.Service.Agrement_ID,
            dbo.Customer.Cust_ID,
            dbo.Service.Service_Date,
            dbo.Service.Next_Service 
    FROM dbo.Service 
    INNER JOIN dbo.Customer ON dbo.Service.Cust_ID = dbo.Customer.Cust_ID 
    WHERE  Next_Service between '2016-02-01' and '2016-02-06' 
    AND    (
    Next_Service not in ( SELECT Service_Date FROM Service s WHERE s.Cust_ID = dbo.Customer.Cust_ID ) or Next_Service not in ( SELECT Dateadd(d, -1, Service_Date) FROM Service s WHERE s.Cust_ID = dbo.Customer.Cust_ID )
    ) ORDER BY Next_Service

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 6, 2016 3:05 AM
  • User-158764254 posted

    based on how you've described your desired result, your extra condition added to your query should have been added as an AND rather than an OR.

    you dont want next service date to be the same as servicedate

    AND

    you also dont want next service date to be the same as servicedate-1

    Saturday, February 6, 2016 12:13 PM
  • User77042963 posted

    You should be able to rewrite your query without the two subqueries with a datetime range exclusion directly.

    Sunday, February 7, 2016 4:08 AM