0

I've been trying to implement a conditional Where and having problems. What I want to do is select only those customers that visited a beauty salon that had a haircut and also a manicure in the past year. The treatments don't have to be on the same day. e.g. If Susan had a manicure in the last 12 months, select all the times Susan had a haircut in the same time period. The table is structured so each visit is one row. e.g.

Customer     Treatment     Treatment_Date
Susan        Manicure      12 Jan 2013
Susan        Make-up       3 Feb 2013
Susan        Haircut       14 Feb 2013
Susan        Blow Dry      3 Mar 2013
Susan        Haircut       21 Apr 2013
Betty        Manicure      4 Jun 2013
Betty        Haircut       7 July 2013
Tara         Haircut       10 Aug 2013

So ideally I'd like to select Susan and Betty. Can this be done in one query or would it have to be broken up, like first select all customers that had a haircut, then from this select those that had a manicure.

Thanks in advance for your advice.

:)

canon
  • 40,609
  • 10
  • 73
  • 97
CodeLearner
  • 389
  • 2
  • 6
  • 14

4 Answers4

1

Another way to approach this

SELECT DISTINCT Customer
  FROM
(
  SELECT Customer,
         MAX(CASE WHEN Treatment = 'Manicure' THEN 1 ELSE 0 END) 
             OVER (PARTITION BY Customer) had_manicure,
         MAX(CASE WHEN Treatment = 'Haircut' THEN 1 ELSE 0 END) 
             OVER (PARTITION BY Customer) had_haircut
    FROM table1 t
   WHERE Treatment_Date >= DATEADD(month, -12, GETDATE())
) q
 WHERE had_manicure + had_haircut = 2

Output:

| CUSTOMER |
|----------|
|    Betty |

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
0

This is an example of a "set-within-sets" subquery. I like to solve these using group by and having:

select ct.Customer
from CustomerTreatment ct
where ct.Treatment_Date >= getdate() - 365
group by ct.Customer
having sum(case when ct.Treatment = 'Manicure' then 1 else 0 end) > 0 and
       sum(case when ct.Treatment = 'Haircut' then 1 else 0 end) > 0;

Each condition in the having clause counts the number of treatments of each type. Your conditions specify that each appears at least once, hence the > 0.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
Select Customer, Treatment, Treatment_Date From Treatments 
Where Customer In (Select Customer from Treatments Where Treatment = 'Manicure' And Treatment_Date >= Start_Date_For_The_Period_Goes_Here And Treatment_Date <= End_Date_For_The_Period_Goes_Here) 
And 
Customer In (
Select Customer from Treatments Where Treatment = 'Haircut' And Treatment_Date >= Start_Date_For_The_Period_Goes_Here And Treatment_Date <= End_Date_For_The_Period_Goes_Here
)

Other ways : You may use a between clause. You may also use self-joins.

In other news, I wouldn't call this a conditional Where statement. I would call it one, if the Where clause contained a Case When Then End phrase.

Fountain
  • 1
  • 1
0

You could do something like this

Select Customer,

 count(case when treatment = 'manicure' then 1 else 0 end) as Manicure,
 count(case when treatment = 'haircut' then 1 else 0 end) as haircut

from 

customerTreatment

where Treatment_date >= DATEADD(year, -1, GetDate())

group by Treatment

have count(Manicure) > 1 and count(haircut) > 1
user3277102
  • 76
  • 3