Hi, How I an get the expected output. I have to put values in Maximum six variable for CODE and CODEP. If my INPUT IN VARIABLES are less than six then I have to put BLANK SPACE ('') in variables.
Results should be based on the variables INPUT with exactly same rows. BUT #SEQ.SEQ should be same if match the Variables criteria.
create table #SEQ (SEQ char(4),Code char(4), CodeP char(4))
DECLARE @Code1 char(4)='CCCC'
DECLARE @Code2 char(4)='DDDD'
DECLARE @Code3 char(4)='EEEE'
DECLARE @Code4 char(4)='FFFF'
DECLARE @Code5 char(4)=''
DECLARE @Code5 char(4)=''
DECLARE @CodeP1 int='85'
DECLARE @CodeP2 int='0'
DECLARE @CodeP3 int='20'
DECLARE @CodeP4 int='90'
DECLARE @CodeP5 int=''
DECLARE @CodeP6 int=''
/* The Logic of the query as per variable should be:
(Code='CCCC' and CodeP is '85') and
(Code='DDDD' and CodeP is '0' ) and
(Code='EEEE' and CodeP is '20') and
(Code='FFFF' and CodeP is '90')
BUT #SEQ.SEQ should be same if match the above criteria
*/
insert into #SEQ values('01','AAAA',100)
insert into #SEQ values('01','BBBB',80)
insert into #SEQ values('01','CCCC',85) --This should not be in the result because all criteria of variable are matches but this record set has extra 2 rows of Code-AAAA and Code-BBBB.
insert into #SEQ values('01','DDDD',0)
insert into #SEQ values('01','EEEE',20)
insert into #SEQ values('01','FFFF',90)
insert into #SEQ values('02','CCCC',85)
insert into #SEQ values('02','DDDD',0)
insert into #SEQ values('02','FFFF',90)
insert into #SEQ values('03','CCCC',85)
insert into #SEQ values('03','DDDD',0)
insert into #SEQ values('03','EEEE',30)
insert into #SEQ values('03','FFFF',90)
insert into #SEQ values('04','CCCC',85) --This should be in the result because all criteria of variable are matches.
insert into #SEQ values('04','DDDD',0)
insert into #SEQ values('04','EEEE',20)
insert into #SEQ values('04','FFFF',90)
insert into #SEQ values('05','CCCC',85) --This should not be in the result because all criteria of variable are matches but this record set has extra 1 row of Code-AAAA.
insert into #SEQ values('05','DDDD',0)
insert into #SEQ values('05','EEEE',20)
insert into #SEQ values('05','FFFF',90)
insert into #SEQ values('05','AAAA',10)
--Need a following result from SELECT query
SEQ Code CodeP
04 CCCC 85
04 DDDD 0
04 EEEE 20
04 FFFF 90
- Edited by Khan_K 3 hours 47 minutes ago