CODEWITHSUNDEEP
sqlsql-serverif-statementcalculated-columns
Carl
Carl

Reputation: 101

Calculated column in SQL with IF-ELIF-ELSE condition

I need help on how to calculate this calculated column in SQL-code. I have seen earlier in the forum how you calculate based on adding columns together, or just giving a standard value. But in my case, I would like to add a column that could show different pre-set values based on the values of another column. It is just like a normal IF-ELIF-ELSE-statement.

In my case, I have a database, a table, that logs every change one of the users do in the system. Whenever a change is made, the users employee-number is logged to the table and not the employee's name. Since we spend a lot of time going back to these logs, we spend a lot of time looking up which employer-number belong to who. I would like this to be done directly in the query. I have a list of all the employee-numbers and their corresponding names.

My basic SQL-skill only tell me how to import table/columns and create one, but with the IF-statement I am completely lost, below I have performed some pseudo-code so you might understand what I am looking for. A.Emp_Num is the column with the employee numbers and Emp_Name a created column with 'null' as content.

SELECT A.EMP_NUM, A.COLUMN1, A.COLUMN2, 'Null' AS EMP_NAME

FROM LOGGINGTABLE A

IF A.EMP_NUM=='1234' THEN EMP_NAME='MONICA'

ELIF A.EMP_NUM=='5678' THEN EMP_NAME='JOE'

Upvotes: 1

Views: 9948

Answers (2)

Arunprasanth K V
Arunprasanth K V

Reputation: 21931

Try something like below

Note : Maintain relation between EMP_NAME and EMP_NUM in future it will be usefull. if the employee count is more then we can't hard code the values like below so maintain relationship between them 

SELECT CAST(
             CASE 
                  WHEN EMP_NUM =1234 
                     THEN 'MONICA' 
                  WHEN  EMP_NUM =5678
                  then 'JOE' 
                  .
                  .
                  .
             END AS nvarchar(1000)) as EMPNAME,EMP_NUM, COLUMN1, COLUMN2
FROM LOGGINGTABLE

OR

SELECT 
             CASE 
                  WHEN EMP_NUM =1234 
                     THEN 'MONICA' 
                  WHEN  EMP_NUM =5678
                  then 'JOE' 
                  .
                  .
                  .
              END as EMPNAME,EMP_NUM, COLUMN1, COLUMN2
FROM LOGGINGTABL

Suggestion: I suggest you to add a new column named EMP_NAME in your table then save these values in that column and in future when ever you insert EMP_NUM that time insert EMP_NAME too then it will be easy to maintain

Upvotes: 2

ThePravinDeshmukh
ThePravinDeshmukh

Reputation: 1913

OMG, You have employee table in place? Just join it

SELECT A.EMP_NUM, A.COLUMN1, A.COLUMN2, E.EMP_NAME
FROM LOGGINGTABLE A
INNER JOIN EMPLOYEETABLE E on A.EMP_NUM = E.EMP_NUM

EDIT: In case you have entries which are not mapped to user and you don't want to miss them

SELECT A.EMP_NUM, A.COLUMN1, A.COLUMN2, isnull(E.EMP_NAME,'No User Linked') as EMP_NAME
FROM LOGGINGTABLE A
LEFT OUTER JOIN EMPLOYEETABLE E on A.EMP_NUM = E.EMP_NUM

EDIT: If you want to get text-documented employees in table

BULK INSERT dbo.temp 
FROM 'c:\temp\file.txt'
WITH 
  (
     ROWTERMINATOR ='\n'
  )

Then

Insert into EMPLOYEETABLE
select LEFT(column1, CHARINDEX(' ', column1+ ' ') - 1),
RIGHT(column1, len(column1)-CHARINDEX(' ', column1+ ' ') - 1)
from dbo.temp

You may have to revise it, Let me know any difficulties doing so.

Upvotes: 0

Related Questions