I am dealing with a Fact Table which has multiple row entries for each customer in respect of the Appointment Types which they have attended. Each row represents an Appointment, and there exists a Column which holds the Appointment Type. What is the best way to find out all those customers which have attended a specific subset of the Appointment Types e.g. X, Y and Z. So if a Customer attended only one appointment e.g. only X or Y or Z he/she will NOT be included; likewise if he/she has attended ten appointments - two of which are, say Y and Z only, he/she will also NOT be included. The customer can have many Appointment Types - not just X,Y and Z - but the requirement is simply that all three must exist for that customer. Kindly advise the best technique to identify the relevant customers in such scenario. Much obliged !
SELECT CustomerID
FROM dbo.table_name
WHERE AppointmentType IN ('X', 'Y', 'Z')
GROUP BY CustomerID
HAVING MAX(CASE WHEN AppointmentType = 'X' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN AppointmentType = 'Y' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN AppointmentType = 'Z' THEN 1 ELSE 0 END) = 1
CREATE TABLE #types(appt_type char(1) NOT NULL UNIQUE);
INSERT #types VALUES('X'),('Y'),('Z');
;WITH types_CTE AS (SELECT appt_type FROM #types)
SELECT a.CustomerID
FROM dbo.table_name A
INNER JOIN types_CTE B ON A.AppointmentType=B.appt_type
GROUP BY a.CustomerID
HAVING COUNT(DISTINCT A.AppointmentType)=(SELECT COUNT(*) FROM types_CTE)
The temp table (#types) is just to provide sample data for this example. The CTE is defined here to use the temp table, you can replace the CTE definition with any list of the values you want to test for. The advantage is that the remainder of the query does not have to hard-code the actual appointment types, all it needs to do is match the distinct count in the Customer table to the total count of types in the CTE.
This assumes your CTE will deliver unique rows, if it does not then you'd need to modify the HAVING clause to do (SELECT COUNT(DISTINCT column_name) FROM CTE). The example I posted has a UNIQUE constraint on the temp table, therefore COUNT(*) would suffice.