Using not in SQL query

Question

How can I make this query to use Not in condition not in the whole table but limit the condition for each customer

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-01-01' AND '2016-02-01'
    AND Next_Service NOT IN (SELECT Service_Date from Service)
ORDER BY 
    Next_Service

For example let's say I have this data

Service_ID  Service_RNO Cust_Name   Agrement_ID Cust_ID Service_Date    Next_Service
1                 1       customer1    35         15     2016-01-01     2016-01-31
2                 2       customer1    35         15     2016-01-31     2016-03-2
3                 3       customer2    12         21     2016-01-31     2016-03-2

out put of this query will be empty since the next service is existed in the service_date

The query should give customer 2 rows of data since Next_Service not existed in service_date for that customer

Zohar Peled · Accepted Answer · 2016-02-04 07:36:43Z

3

Assuming I understand the question:

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-01-01' and '2016-02-01' 
AND    Next_Service not in (
          SELECT Service_Date 
          FROM Service s
          WHERE s.Cust_ID = dbo.Customer.Cust_ID
) 
ORDER BY Next_Service

answered Feb 4, 2016 at 7:36

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using not in SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related