Change sql statement depending on value

Hi,

I have an sql query looking like this:
SELECT cm.ClubMemberID, cm.subscriptionTypeID FROM dbo.ClubMember AS cm

Open in new window


Now I would like to expand the query by adding a WHERE clause. But I would like to alter the where clause depending of the subscriptionTypeID. So, for example, if subscriptionTypeID = 1 then the query would look like this:
SELECT cm.ClubMemberID, cm.subscriptionTypeID FROM dbo.ClubMember AS cm
WHERE cm.Stopdate > '2018-01-01'

Open in new window


and if subscriptionTypeID = 2 then
SELECT cm.ClubMemberID, cm.subscriptionTypeID FROM dbo.ClubMember AS cm
WHERE cm.Stopdate < '2018-01-01'

Open in new window


How can I achieve this scenario?

Thanks for help!

Peter
Peter NordbergIT ManagerAsked:
Who is Participating?
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

Please try the below query

SELECT cm.ClubMemberID, cm.subscriptionTypeID FROM dbo.ClubMember AS cm
WHERE 
case when subscriptionTypeID = 1 then
case when cm.Stopdate > '2018-01-01' then 1 else 0 end
else 
case when cm.Stopdate < '2018-01-01' then 1 else 0 end
end =1

Open in new window

0
 
Dorababu MSenior Software EngineerCommented:
Try this way some modification required

SELECT cm.ClubMemberID, cm.subscriptionTypeID FROM dbo.ClubMember AS cm
WHERE CM.subscriptionTypeID 
WHEN 1 THEN cm.Stopdate >  '2018-01-01' 
WHEN 2 THEN cm.Stopdate <  '2018-01-01'

Open in new window

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.