Filter all in one Category except few

In my query below, I am filtering out atype.Category for anything not equal to 1. My issue is I have 5 Categorys I need to actually filter back in that are not = 1. I would like to keep filter to only pull back the ones =1 with the exception of ones where atype.Category has a name like '%Catch Up%'. These 5 unique ones that have the name like 'Catch Up' are in a Category = 2.

Any assistance is appreciated.
SELECT 
	r.ListName,
	CASE WHEN r.Type = 7 THEN 'Other Provider'  WHEN r.Type = 1 THEN 'Doctor'  ELSE 'Resource'  END, 
	'01/01/1900',
	f.ListName, 
	'Open Slot', 
	DATEDIFF(n,apptslot.Start, apptslot.Stop), 
	apptslot.Start, 
	atype.Category
		
FROM 
	ApptSlot apptslot 
	INNER JOIN Schedule s ON apptslot.ScheduleID = s.ScheduleID 
	INNER JOIN DoctorFacility f ON apptslot.FacilityID = f.DoctorFacilityID 
	INNER JOIN DoctorFacility r ON s.DoctorResourceID = r.DoctorFacilityID 
	LEFT JOIN AppointmentsAlloc aa ON apptslot.ApptSlotID = aa.ApptSlotID 
	LEFT JOIN ApptType atype ON aa.ApptTypeID = atype.ApptTypeID
	
WHERE 
	apptslot.Start  >= ISNULL('10/17/2011','1/1/1900') AND apptSlot.Start < dateadd(day,1,ISNULL('10/17/2011','1/1/3000')) 
	AND ISNULL(atype.Category,1) = 1 
	AND --Filter on Resource
	(
	('401' IS NOT NULL AND s.DoctorResourceID IN (401)) OR
	('401' IS NULL)
	)
	
GROUP BY 
	r.ListName,
	apptslot.Start, 
	apptslot.Stop, 
	f.ListName, 
	r.Type,
	atype.Category

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Replace:

      AND ISNULL(atype.Category,1) = 1

with:

      AND (ISNULL(atype.Category,1) = 1 OR atype.CategoryName LIKE '%Catch Up%')


You did not specify what the actual 'name' column is, so I had to guess.
0
 
Jeff SAuthor Commented:
Thanks!
0
 
Rajkumar GsSoftware EngineerCommented:
I am not quite clear with the column names.
You can try OR  ?

WHERE 
atype.Category <> 1 OR atype.Category like '%Catch Up%'

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Anthony PerkinsCommented:
Change this:
LEFT JOIN ApptType atype ON aa.ApptTypeID = atype.ApptTypeID

To:
LEFT JOIN ApptType atype ON aa.ApptTypeID = atype.ApptTypeID And (atype.Category = 1 OR atype.Category LIKE '%Catch Up%)

And remove any reference to atype in your WHERE clause.
0
 
Anthony PerkinsCommented:
Oops, I was way too slow.
0
 
Rajkumar GsSoftware EngineerCommented:
We are late :)
0
 
Jeff SAuthor Commented:
I apologize, I should have waited 15 minutes longer. I try to clear them out so I dont forget to reward points. I will try to not do that in future and I apolgize to those that offered help.
0
 
Anthony PerkinsCommented:
It is not a problem.  Use the solution that works best for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.