CODEWITHSUNDEEP
sqlsql-server
Arfat
Arfat

Reputation: 69

Compare two columns data case statements

written case statements to get result again i need to compare the result column with Job_Level column if Job_Level contains any one value present in result then it should return true else false in new column (ExpectedOutput)

select Email, Job_Levels,Answer,
case when answer = 'Assessor / Trainer' then 1
when answer = 'Administrator / Coordinator/ Assistant' then 2
 when answer = 'Adviser' then 3
  when answer = 'Apprentice' then 4
  when answer = 'Deputy / Vice Principal' then 5
  when answer = 'Chief Executive / Principal' then 6 
when answer = 'Technician' then 18
  when answer = 'Worker / Practitioner' then 19
 end as result  
 from TBL_CandidateInfo
 inner join tal_users
 on Userid = UserID_FK
  inner join [ABC_Migration].[dbo].[ABCSeeker]
 on email = susername
WHERE ProfileQuestion='What is your preferred job role / level?'

Current output

Email         Job_Levels    Answer                result
[email protected]   2,1,16        Assessor / Trainer    1
[email protected]   2,1,16        Teaching / Lecturing  16
[email protected]   12,16,18      Learner Suppor        12

expected output :

Email         Job_Levels    Answer               result  ExpectedOutput
[email protected]   2,1,16        Assessor / Trainer   1        True
[email protected]   2,1,16        Teaching / Lecturing 16       True
[email protected]   16,18         Learner Suppor       12       False

Upvotes: 2

Views: 67

Answers (3)

HoneyBadger
HoneyBadger

Reputation: 15150

Edit: While this answer has been accepted, please note there are caveats to this solution. For a more fool-proof method please see @GuidoG's answer.

I think you're looking for something like this:

SELECT  Email
,       Job_Levels
,       Answer
,       result
,       CASE
            WHEN Job_Levels LIKE '%' + CONVERT(VARCHAR(2), result) + '%' THEN 'true'
            ELSE 'False'
        END AS ExpectedOutput
FROM    (
            SELECT Email, Job_Levels,Answer,
            case 
                    WHEN answer = 'Assessor / Trainer' then 1
                    WHEN answer = 'Administrator / Coordinator/ Assistant' then 2
                    WHEN answer = 'Adviser' then 3
                    when answer = 'Apprentice' then 4
                    WHEN answer = 'Deputy / Vice Principal' then 5
                    WHEN answer = 'Chief Executive / Principal' then 6 
                    WHEN answer = 'Technician' then 18
                    WHEN answer = 'Worker / Practitioner' then 19
             end as result  
             from TBL_CandidateInfo
             inner join tal_users
             on Userid = UserID_FK
              inner join [ABC_Migration].[dbo].[ABCSeeker]
             on email = susername
            WHERE ProfileQuestion='What is your preferred job role / level?'
        ) T

Upvotes: 1

GuidoG
GuidoG

Reputation: 12059

one option is to use the CharIndexfunction

declare @Job_Levels varchar(100) = '2,1,16'

select case when charindex('16' + ',', @Job_Levels + ',') > 0 then 'true' else 'false' end

Note that I added an extra , just to make sure I dont get TRUE when looking for 1 and there is no 1 but there is 16.
In other words, the extra , is needed to make sure that looking for 1 does not returns true for the value 16, but only for the exact value 1

your query would look like this then

select t.Email,
       t.Job_Levels,
       t.Answer,
       t.result,
       case when charindex(t.result + ',', t.Job_Levels + ',') > 0 then 'true' else 'false' end as ExpectedOutput
from   ( select Email, Job_Levels,Answer,
                case when answer = 'Assessor / Trainer' then 1
                     when answer = 'Administrator / Coordinator/ Assistant' then 2
                     when answer = 'Adviser' then 3
                     when answer = 'Apprentice' then 4
                     when answer = 'Deputy / Vice Principal' then 5
                     when answer = 'Chief Executive / Principal' then 6 
                     when answer = 'Technician' then 18
                     when answer = 'Worker / Practitioner' then 19
                end as result
         from TBL_CandidateInfo
           inner join tal_users on Userid = UserID_FK
           inner join [ABC_Migration].[dbo].[ABCSeeker] on email = susername
         WHERE ProfileQuestion='What is your preferred job role / level?'
      ) t

Upvotes: 2

Afshin Rashidi
Afshin Rashidi

Reputation: 353

use this. note that if we dont use ',' before and after [co].[Job_Levels] result may be incorrect. for example IDs 11,113 both appears true result in LIKE %11% Statement but 113 is not correct result. but my query using LIKE %,11,% and returns false for ,113,

SELECT      co.*
            ,CASE WHEN Convert(varchar(MAX),',' + [co].[Job_Levels] + ',') Like '%,' + Convert(varchar(MAX),[co].[result]) + ',%' THEN
                1 --true
            ELSE 
                0 --false
            END as ExpectedOutput
FROM        (
                select Email, Job_Levels,Answer,
                case when answer = 'Assessor / Trainer' then 1
                when answer = 'Administrator / Coordinator/ Assistant' then 2
                 when answer = 'Adviser' then 3
                  when answer = 'Apprentice' then 4
                  when answer = 'Deputy / Vice Principal' then 5
                  when answer = 'Chief Executive / Principal' then 6 
                when answer = 'Technician' then 18
                  when answer = 'Worker / Practitioner' then 19
                 end as result  
                 from TBL_CandidateInfo
                 inner join tal_users
                 on Userid = UserID_FK
                  inner join [ABC_Migration].[dbo].[ABCSeeker]
                 on email = susername
                WHERE ProfileQuestion='What is your preferred job role / level?'
            ) co

Upvotes: 1

Related Questions