0

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

  • It is possible to have/find another Emp_Status in the table beside the FT and PT ? – Sabin Bio Nov 21 '18 at 7:08
  • Yes, there might be RS values but i don't want it to be included in the final table.. I only want FT and PT. – Mor Nov 21 '18 at 7:11
1
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

  • thank you so much! However, Is there any way that I can process it withput defining the emp ID? I just want to process inside the whole table. – Mor Nov 21 '18 at 7:45
  • @mor , Yes , it could be, look at version 1, and remove the where clause. I will update the script – Sabin Bio Nov 21 '18 at 7:54
  • Thanks Sabin! It's working now with the Version1! Really appreciate it! thanks for your help. – Mor Nov 21 '18 at 7:57
  • Great ! my pleasure – Sabin Bio Nov 21 '18 at 7:59
  • 1
    @Mor Could you select Sabin's answer as the solution if it worked for you? – Randi Vertongen Nov 21 '18 at 21:16

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.