Sql-server – MS SQL: How to get Employee Status from two seperate tables

sql-server-2008

I am new to MS SQL so please bear with me.

I want to get the Company Code and Company Short Name of an employee DEPENDS on it's each employee status and stored it into one table.

If Emp_Status = FT, it will get Co_Code and Co_Shortname from Table 1. ELSE if Emp_Status = PT, it will get Co_Code and Co_Shortname from Table 2 where USERNAME is equals to each other.

I have two tables.

Table 1:
EmpID  |Co_Code  |Co_ShortName |Emp_Status    
WC11    |01          |AA1        |FT    
KC22    |02          |BB1        |PT    
MA33    |03          |CC1        |FT


Table 2:  
EmpID  |Co_Code |Co_ShortName |Emp_Status
WC11     |04          |DD1        |FT
KC22     |05          |EE1        |PT
MA33     |06          |FF1        |FT

Output will be

EmpID  |Co_Code  |Co_ShortName |Emp_Status
WC11    |01          |AA1        |FT
KC22    |05          |EE1        |PT
MA33    |03          |CC1        |FT

I am not sure if I need to use CASE or IF statement as I am getting other fields from table 1.

I know this isn't correct but this is what I have:

SELECT EmpID ,  CASE    
        WHEN Table1.EmpStatus = 'FT'    
        THEN (SELECT Co_Code from Table 1)         
        WHEN Table1.EmpStatus = 'PT'    
        THEN (SELECT Co_Code from Table 2 where Table1.EmpID = Table2.EmpID)

    END

Please help

Best Answer

create table #Table1
( EmpID CHAR(4),
  Co_Code CHAR(2),
  Co_ShortName CHAR(3),
  Emp_Status CHAR(2)
 )
INSERT INTO #Table1
    ([EmpID], [Co_Code], [Co_ShortName], [Emp_Status])
VALUES
    ('WC11', '01', 'AA1', 'FT'),
    ('KC22', '02', 'BB1', 'PT'),
    ('MA33', '03', 'CC1', 'FT');

CREATE TABLE #Table2
    (EmpID CHAR(4), 
     Co_Code CHAR(2), 
     Co_ShortName CHAR(3), 
     Emp_Status CHAR(2));

INSERT INTO #Table2
    ([EmpID], [Co_Code], [Co_ShortName], [Emp_Status])
VALUES
    ('WC11', '04', 'DD1', 'FT'),
    ('KC22', '05', 'EE1', 'PT'),
    ('MA33', '06', 'FF1', 'FT');
GO

Version1:

DECLARE @c_EmpID CHAR(4) = '';
DECLARE @c_Emp_Status CHAR(2) = '';

SET @c_EmpID = 'WC11'
--SET @c_EmpID = 'KC22'

SELECT 
        CASE WHEN T1.Emp_Status = 'FT' THEN T1.Co_Code 
             WHEN T1.Emp_Status = 'PT' THEN T2.Co_Code 
             ELSE 'N/A' 
        END AS Co_Code,
        CASE WHEN T1.Emp_Status = 'FT' THEN T1.Co_Shortname 
            WHEN T1.Emp_Status = 'PT' THEN T2.Co_Shortname 
            ELSE 'N/A' 
        END AS Co_Shortname
    FROM #Table1 AS T1
        LEFT JOIN #Table2 AS T2
            ON T1.EmpID = T2.EmpID
            --AND T2.Emp_Status = 'PT'
    --WHERE T1.EmpID = @c_EmpID

output:

Co_Code Co_Shortname
01      AA1

Version2: You can put the logic inside stored procedures:

CREATE PROCEDURE #usp_Co_FT
(
  @i_c_EmpID CHAR(4)
)
AS
BEGIN
        SELECT 
           T1.Co_Code ,T1.Co_Shortname
        FROM #Table1 AS T1 
        WHERE
            T1.EmpID = @i_c_EmpID
            AND T1.Emp_Status = 'FT'
END
GO
CREATE PROCEDURE #usp_Co_PT
(
  @i_c_EmpID CHAR(4)
)
AS
BEGIN
        SELECT 
           T2.Co_Code , T2.Co_Shortname
        FROM #Table2 AS T2
        WHERE
            T2.EmpID = @i_c_EmpID
            AND T2.Emp_Status ='PT'
END
GO


DECLARE @c_EmpID CHAR(4) = '';
DECLARE @c_Emp_Status CHAR(2) = '';

SET @c_EmpID = 'WC11'
--SET @c_EmpID = 'KC22'

SELECT @c_Emp_Status =  T1.Emp_Status FROM #Table1 AS T1 WHERE T1.EmpID = @c_EmpID

IF @c_Emp_Status = 'FT'
BEGIN   
    EXEC  #usp_Co_FT @i_c_EmpID = @c_EmpID
END
ELSE IF @c_Emp_Status = 'PT'
    BEGIN
        EXEC  #usp_Co_PT @i_c_EmpID = @c_EmpID
    END
GO

Version 3 with UNION ALL

DECLARE @c_EmpID CHAR(4) = 'WC11';
--SET @c_EmpID = 'KC22'


SELECT 
   T1.Co_Code ,T1.Co_Shortname
FROM #Table1 AS T1 
WHERE
    T1.EmpID = @c_EmpID
    AND T1.Emp_Status = 'FT'

UNION ALL  

SELECT 
   T2.Co_Code , T2.Co_Shortname
FROM #Table2 AS T2
WHERE
    T2.EmpID = @c_EmpID
    AND T2.Emp_Status ='PT'

dbfiddle here

Related Question