CODEWITHSUNDEEP
sqlsql-server
pete
pete

Reputation: 101

id where column has two conditions

I have a table of customers that join and leave a company 

ID ActiveFrom ActiveTo

I have for example a where clause that has 

where ActiveFrom <= '20170101' 
  and Activeto < '20170201'

but some times a customer has decided to re-join the company a few days later because of a deal.

example customer: 

ID ActiveFrom ActiveTo
1  2000-01-01 2017-01-03
1  2017-01-28 Null
2  2000-01-01 2017-01-06

I want to exclude the customers that do this, from the customers that leave the company but come back

so I want id 2 to be returned only

please help

Upvotes: 2

Views: 70

Answers (2)

A. Cristian Nogueira
A. Cristian Nogueira

Reputation: 735

You could use a subquery on WHERE statement:

SELECT *
FROM tablename
WHERE (SELECT id FROM tablename WHERE activeTo is null) <> id
GROUP By id

You can find more information here: https://www.techonthenet.com/sql_server/subqueries.php

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can try this.

SELECT * FROM Customer C1
WHERE C1.ActiveFrom <= '20170101' 
        AND C1.Activeto < '20170201'
        AND NOT EXISTS ( SELECT * FROM Customer C2
                WHERE C1.ID = C2.ID
                AND C2.ActiveTo IS NULL )

Upvotes: 1

Related Questions