How to add categories based on two different variables in SQL Query in SFMC

Question

I'm trying to create a data extension that extracts subscriber key and clicks based on specific criteria. Below is the script:

select c.[JobID]
     , c.[SubscriberKey]
     , c.[URL]
     , j.[EmailName]
     , c.[LinkName]
from [_Click] c
INNER JOIN [_Job] j ON j.[JobID] = c.[JobID]
WHERE j.[EmailName] like '%Onboarding email%'
OR j.[EmailName] IN ('Onboarding3', 'Onboarding4')
AND c.[URL] like '%review%'
OR c.[URL] like '%action%'
OR c.[LinkName] like '%B&I%'

This gets me the data that I need which is great. But, I want to add an additional column to my data extension named: Category

And I want it to give a category based on the above filters (two different columns), i.e., if URL has 'review', then the category is Review. If URL has 'action', then the category is Action. And if the Link name has 'B&I', then the category is 'B&I'.

Can someone give me tips on how to go about this? Thank you

Answer 1

You can use SQL Case Expression to return a value based on a condition.

Try this:

SELECT 
  c.[JobID] ,
  c.[SubscriberKey] ,
  c.[URL] ,
  j.[EmailName] ,
  c.[LinkName],
  CASE
    WHEN c.[URL] like '%review%' THEN 'review'
    WHEN c.[URL] like '%action%' THEN 'action'
    WHEN c.[LinkName] like '%B&I%' THEN 'B&I'
    ELSE 'something else'
  END AS Category
FROM [_Click] c
INNER JOIN [_Job] j ON j.[JobID] = c.[JobID]
WHERE j.[EmailName] like '%Onboarding email%'
  OR j.[EmailName] IN ('Onboarding3',
                       'Onboarding4')
  AND c.[URL] like '%review%'
  OR c.[URL] like '%action%'
  OR c.[LinkName] like '%B&I%'

Note: I haven't tested on it.