CODEWITHSUNDEEP
sql
RockandGrohl
RockandGrohl

Reputation: 33

I need to select clients who have multiple records in one column

I'm tasked with selecting all clients who have traveled in 2017 and 2018, but not 2019.

I've tried something like the below but it doesn't work. I'm pretty much a novice at SQL, VBA is more my thing.

I could say where tour date is less than 2019-01-01, but this may only result in customers that have booked once for a tour before 2019, I need to select people that are traveling at least once in 2017 and then at least once in 2018, but not in 2019.

select clientid from cust

    where tour_date between '2017-01-01' and '2017-12-31'
    and tour_date between '2018-01-01' and '2018-12-31'
    and tour_date < '2019-01-01'

Any help would be appreciated, thanks.

Upvotes: 1

Views: 911

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

If you want clients and not the details of their trips, then use aggregation. I would phrase this as:

SELECT clientid
FROM cust
GROUP BY clientid
HAVING SUM(CASE WHEN tour_date >= '2017-01-01' AND tour_date < '2018-01-01' THEN 1 END) > 0 AND
       SUM(CASE WHEN tour_date >= '2018-01-01' AND tour_date < '2019-01-01' THEN 1 END) > 0 AND
       SUM(CASE WHEN tour_date >= '2019-01-01' AND tour_date < '2020-01-01' THEN 1 END) = 0 ;

Each condition in the HAVING clause is counting the number of tours during the year in question. The comparison > 0 says there is at least one; the = 0 says there are none during that year.

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT clientid
FROM
(
    SELECT clientid,YEAR(tour_date) YR
    FROM cust
    WHERE YEAR(tour_date) IN (2017,2018)
)A
GROUP BY clientid
HAVING COUNT(DISTINCT YR) > 1

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522299

We could also try using aggregation here:

SELECT clientid
FROM cust
GROUP BY clientid
HAVING
    COUNT(CASE WHEN tour_date BETWEEN '2017-01-01' AND '2017-12-31' THEN 1 END) > 0 AND
    COUNT(CASE WHEN tour_date BETWEEN '2018-01-01' AND '2018-12-31' THEN 1 END) > 0 AND
    COUNT(CASE WHEN tour_date < '2019-01-01' THEN 1 END) > 0;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

Use correlated subquery with exists and not exists

select clientid from cust a
where exist
  (select 1 from cust b where a.clientid=b.clientid and tour_date  between '2017-01-01' and '2017-12-31')
and exist 
  (select 1 from cust b where a.clientid=b.clientid and tour_date  between '2018-01-01' and '2018-12-31')
and not exists
   (select 1 from cust b where a.clientid=b.clientid and tour_date  between '2019-01-01' and '2019-12-31')

Upvotes: 3

Related Questions