Can't return role based on employee no from three columns?

Transact-SQL
, ,
1

I need to write a SQL Server query return only one role from table dbo.F6000059 based on employee no, but I face issue I don't know how to return the correct role.

I will return only one role from table dbo.F6000059 if "employee no" exists in columns REAN82 or REAN83 or REAN84.

And role name will return will be only one roles from below :

  • REQ represent by column REAN82
  • LM represent by column REAN83
  • DM represent by column REAN84
  • LDM if it exist on two columns REAN83 or REAN84

Condition I will write :

  • if "employee no" exists in column REAN82, role will be REQ
  • if "employee no" exists in column REAN83, role will be LM
  • if "employee no" exists in column REAN84, role will be DM
  • if "employee no" exists in columns REAN84 and REAN83, role will be LDM

Scripts for table as below :slight_smile:

USE [Test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[F6000059]
(
    [RequestNo] [int] NULL,
    [REAN82] [int] NULL,
    [REAN83] [int] NULL,
    [REAN84] [int] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12001, 134618, NULL, NULL)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12002, 134618, 988144, 390144)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12003, NULL, 977133, NULL)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12004, 977133, 200312, 950188)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12005, 881099, 977133, 504122)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12006, 120442, NULL, 504122)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12007, 504122, 977133, 394421)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12008, 407133, NULL, 303144)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12009, 503144, 407133, 407133)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12010, 761300, 407133, 905154)
INSERT [dbo].[F6000059] ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES (12011, 407133, 390222, 395222)
GO

Desired result is return only role REQ OR LM OR DM OR LDM based on employee no as image:

image

2

It is not entirely clear what you want but you should probably start by unpivoting the employee numbers.
Maybe something like:

WITH EmpNos
AS
(
	SELECT E.EmpNo
		,SUM(CASE WHEN R.RType = 'REAN82' THEN 1 ELSE 0 END) AS REAN82
		,SUM(CASE WHEN R.RType = 'REAN83' THEN 1 ELSE 0 END) AS REAN83
		,SUM(CASE WHEN R.RType = 'REAN84' THEN 1 ELSE 0 END) AS REAN84
	FROM dbo.F6000059 T
		CROSS APPLY (VALUES ('REAN82'),('REAN83'),('REAN84')) R (RType)
		CROSS APPLY
		(
			VALUES
			(
				CASE R.RType
					WHEN 'REAN82' THEN REAN82
					WHEN 'REAN83' THEN REAN83
					WHEN 'REAN84' THEN REAN84
				END
			)
		) E (EmpNo)
	WHERE E.EmpNo IS NOT NULL
	GROUP BY E.EmpNo
)
SELECT EmpNo
	,CASE
		WHEN REAN83 > 0 AND REAN84 > 0
		THEN 'LDM'
		WHEN REAN84 > 0
		THEN 'DM'
		WHEN REAN83 > 0
		THEN 'LM'
		WHEN REAN82 > 0
		THEN 'REQ'
		ELSE ''
	END AS [Role]
FROM EmpNos;

If you did want this output, then in future show what the results should be for the test data.