Help in query for exact records

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
September 10th, 2015 11:03pm

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 @Code6 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)


;with mycte as (select *,count(*) Over(partition by SEQ) cnt1 from #SEQ)
,mycte2 as (select *,count(*) Over(partition by SEQ) cnt2 from #SEQ
where (code=@code1 and CodeP=@CodeP1)
Or (code=@code2 and CodeP=@CodeP2)
or (code=@code3 and CodeP=@CodeP3)
or (code=@code4 and CodeP=@CodeP4)
or (code=@code5 and CodeP=@CodeP5)
or (code=@code6 and CodeP=@CodeP6))

select m1.SEQ,m1.Code,m1.CodeP from mycte m1 join mycte2 m2 on m1.SEQ=m2.SEQ and m1.Code=m2.code
where cnt1=4 and cnt2=4



drop table #SEQ

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 11:34pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics