CODEWITHSUNDEEP
sqlsql-server
Arfat
Arfat

Reputation: 69

Concatenate Two columns data into new column

Concatenate DesireMinSalary and DesireMaxSalary columns data into new column namely ConcateSalary and remove currency symbol,decimal points from Answer Column and then compare the values With ConcateSalary if same then reurn true else false

select  UserID_FK, DesireMinSalary,DesireMaxSalary,Answer,
case when answer = CONVERT(VARCHAR(2), DesireMinSalary)  then 'True'else 'False' end as reslut
from TAL_TBL_Candidate
 inner join tal_tbl_users
 on Userid = UserID_FK
  inner join [AocJobs_Migration].[dbo].[AoCJobsJobseekerProfiles]
 on email = susername
 WHERE ProfileQuestion='What is your desired annual salary?'

Current output:

+-----------+-----------------+-----------------+-------------------+--------+
| UserID_FK | DesireMinSalary | DesireMaxSalary | Answer            | result |
+-----------+-----------------+-----------------+-------------------+--------+
| 823       | 20000           | 24999           | £20,000 - £24,999 | true   |
+-----------+-----------------+-----------------+-------------------+--------+
| 557       | 12300           | 85000           | £30,000 - £40,000 |false   |
+-----------+-----------------+-----------------+-------------------+--------+

Expected Output:

+-----------+-----------------+-----------------+--------------+-------------------+--------+
| UserID_FK | DesireMinSalary | DesireMaxSalary | ConcatSalrye | Answer            | result |
+-----------+-----------------+-----------------+--------------+-------------------+--------+
| 823       | 20000           | 24999           | 20000-24999  | £20,000 - £24,999 | true   |
+-----------+-----------------+-----------------+--------------+-------------------+--------+
| 557       | 12300           | 85000           | 12300-85000  | £30,000 - £40,000 | false  |
+-----------+-----------------+-----------------+--------------+-------------------+--------+

Upvotes: 0

Views: 152

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

You can use some STRING to INT conversion to check MIN and MAX salary from Answer with column 'DesireMinSalary' & 'DesireMaxSalary' as below-

SELECT
..... 
CASE
    WHEN CAST(REPLACE(SUBSTRING(Answer,2,CHARINDEX(' - ',Answer,0)-2),',','') AS INT) = CAST(DesireMinSalary AS INT)
        AND CAST(REPLACE(SUBSTRING(Answer,(CHARINDEX(' - ',Answer,0))+4,LEN(Answer)),',','') AS INT) = CAST(DesireMaxSalary AS INT) 
        THEN 'True'
    ELSE 'False'
END AS reslut
....

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

You may try this. As you said your current output is already coming, then only need to add a column by combining 2 column values.

select  UserID_FK, DesireMinSalary,DesireMaxSalary, (cast(DesireMinSalary as varchar(15)) + '-' + cast(DesireMaxSalary as varchar(15)) as  ConcatSalrye ,   Answer,
case when answer = CONVERT(VARCHAR(2), DesireMinSalary)  then 'True'else 'False' end as reslut
from TAL_TBL_Candidate
 inner join tal_tbl_users
 on Userid = UserID_FK
  inner join [AocJobs_Migration].[dbo].[AoCJobsJobseekerProfiles]
 on email = susername
 WHERE ProfileQuestion='What is your desired annual salary?'

Upvotes: 1

Related Questions