Can’t return role based on employee no from three columns?
-
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
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)
GODesired result is return only role REQ OR LM OR DM OR LDM based on employee no as image:
-
Your image is inconsistent. For example
RequestNo 12002 and RequestNo 12011 both have three NON NULL REAN columns, yet you have given them different roles.
Here is the solution, based on your conditions as written:
DROP TABLE IF EXISTS #F6000059;
CREATE TABLE #F6000059
(
RequestNo INT NULL
,REAN82 INT NULL
,REAN83 INT NULL
,REAN84 INT NULL
);
INSERT #F6000059
(
RequestNo
,REAN82
,REAN83
,REAN84
)
VALUES
(12001, 134618, NULL, NULL)
,(12002, 134618, 988144, 390144)
,(12003, NULL, 977133, NULL)
,(12004, 977133, 200312, 950188)
,(12005, 881099, 977133, 504122)
,(12006, 120442, NULL, 504122)
,(12007, 504122, 977133, 394421)
,(12008, 407133, NULL, 303144)
,(12009, 503144, 407133, 407133)
,(12010, 761300, 407133, 905154)
,(12011, 407133, 390222, 395222);
SELECT f.RequestNo
,f.REAN82
,f.REAN83
,f.REAN84
,Role = CASE
WHEN f.REAN82 IS NOT NULL THEN
'REQ'
WHEN f.REAN83 IS NOT NULL
AND f.REAN84 IS NOT NULL THEN
'LDM'
WHEN f.REAN83 IS NOT NULL THEN
'LM'
WHEN f.REAN83 IS NOT NULL THEN
'DM'
ELSE
'Not found'
END
FROM #F6000059 f
ORDER BY f.RequestNo;CASE logic is applied in the order it is written. So please adjust it to apply your conditions, whatever they may really be, in priority order.
Also, given you have >3,000 points, why are you not supplying your sample data in the form I have written it?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
i think it is wrong
this query below must return LDM
BECAUSE employee no 407133 exist on two columns rean83 and rean84
DROP TABLE IF EXISTS #F6000059;
CREATE TABLE #F6000059
(
RequestNo INT NULL
,REAN82 INT NULL
,REAN83 INT NULL
,REAN84 INT NULL
);
INSERT #F6000059
(
RequestNo
,REAN82
,REAN83
,REAN84
)
VALUES
(12001, 134618, NULL, NULL)
,(12002, 134618, 988144, 390144)
,(12003, NULL, 977133, NULL)
,(12004, 977133, 200312, 950188)
,(12005, 881099, 977133, 504122)
,(12006, 120442, NULL, 504122)
,(12007, 504122, 977133, 394421)
,(12008, 407133, NULL, 303144)
,(12009, 503144, 407133, 407133)
,(12010, 761300, 407133, 905154)
,(12011, 407133, 390222, 395222);
SELECT top 1 Role = CASE
WHEN f.REAN82 IS NOT NULL THEN
'REQ'
WHEN f.REAN83 IS NOT NULL
AND f.REAN84 IS NOT NULL THEN
'LDM'
WHEN f.REAN83 IS NOT NULL THEN
'LM'
WHEN f.REAN83 IS NOT NULL THEN
'DM'
ELSE
'Not found'
END
FROM #F6000059 f
where REAN82=407133 or REAN83=407133 or REAN84=407133
ORDER BY f.RequestNo;
-
drop TABLE if exists #F6000059;
go
CREATE TABLE #F6000059
(
[RequestNo] [int] NULL,
[REAN82] [int] NULL,
[REAN83] [int] NULL,
[REAN84] [int] NULL
);
INSERT #F6000059 ([RequestNo], [REAN82], [REAN83], [REAN84]) VALUES
(12001, 134618, NULL, NULL)
,(12002, 134618, 988144, 390144)
,(12003, NULL, 977133, NULL)
,(12004, 977133, 200312, 950188)
,(12005, 881099, 977133, 504122)
,(12006, 120442, NULL, 504122)
,(12007, 504122, 977133, 394421)
,(12008, 407133, NULL, 303144)
,(12009, 503144, 407133, 407133)
,(12010, 761300, 407133, 905154)
,(12011, 407133, 390222, 395222);
with lead_cte as (
select *,
coalesce([REAN84], [REAN83], [REAN82]) REAN,
lead(f.[RequestNo], 1, 2147483647) over (order by RequestNo) lead_RequestNo,
CASE WHEN f.REAN83=f.REAN84 THEN 'LDM'
WHEN f.REAN84 IS NOT NULL THEN 'DM'
WHEN f.REAN83 IS NOT NULL THEN 'LM'
WHEN f.REAN82 IS NOT NULL THEN 'REQ'
ELSE 'Not found' END decode
from #F6000059 f
where [REAN82] is null
or [REAN83] is null
or [REAN84] is null
or [REAN83]=[REAN84])
select *
from lead_cte l
cross apply (select f.RequestNo
from #F6000059 f
where f.RequestNo between l.RequestNo
and l.lead_RequestNo-1) x(full_RequestNo)
order by x.full_RequestNo;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- ahmed_elbarbary.2010 wrote:
i think it is wrong
this query below must return LDM
BECAUSE employee no 407133 exist on two columns rean83 and rean84
Ah, you mean the SAME employee number ... not just an employee number? That was an important piece of information.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Also my code assumes the RequestNo is UNIQUE NOT NULL and has appropriate sequential values. The DDL provided says the RequestNo is NULL-able
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply