Post
Ask Question
ahmed salah

ahmed salah

  • 1.1k
  • 689
  • 43.6k

How to make query return only one role based on employee no from three

Oct 20 2023 6:26 PM

I need to make sql query return only one role from table dbo.F6000059 based on employee no but I face issue I don't know how to return correct role

I will return only one role from table dbo.F6000059 if employee no exist on columns REAN82 OR REAN83 OR REAN84 .

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

REQ

LM

DM

LDM

condition I will write :

if employee no exist on column REAN82 only then role will be REQ .

if employee no exist on column REAN83 only then role will be LM .

if employee no exist on column REAN84 only then role will be DM.

if employee no exist on column REAN84 AND column REAN83 only then role will be LDM.

scripts for table as below :

USE [Test]
GO
/****** Object:  Table [dbo].[F6000059]    Script Date: 20/10/2023 9:43:48 PM ******/
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)
GO

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


Answers (2)