Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

I have a table like below

create table #mytable (vouno varchar(20), voudate date, procedure varchar(10), Amount decimal(18,3))

insert into #mytable values ('1001','01/01/2021','A', 100)
insert into #mytable values ('1001','01/01/2021','B', 200)
insert into #mytable values ('1001','01/01/2021','B', 200)

insert into #mytable values ('1001','02/01/2021','B', 200)
insert into #mytable values ('1001','02/01/2021','B', 200)

insert into #mytable values ('1001','03/01/2021','A', 100)
insert into #mytable values ('1001','03/01/2021','A', 100)

insert into #mytable values ('1002','01/01/2021','A', 100)
insert into #mytable values ('1002','01/01/2021','A', 100)

insert into #mytable values ('1002','02/01/2021','B', 200)
insert into #mytable values ('1002','02/01/2021','A', 100)


i need to select the record for the same date and voucher should not have amount for both procedure 'A' and 'B'
in the above data vouno 1001 and date '01/01/2021' have amount for procedure 'A' and 'B' and
vouno 1002 and date '02/01/2021' have amount for procedure 'A' and 'B'

i need that two records only

Thanks
Kasim

What I have tried:

Hi,

I have a table like below

create table #mytable (vouno varchar(20), voudate date, procedure varchar(10), Amount decimal(18,3))

insert into #mytable values ('1001','01/01/2021','A', 100)
insert into #mytable values ('1001','01/01/2021','B', 200)
insert into #mytable values ('1001','01/01/2021','B', 200)

insert into #mytable values ('1001','02/01/2021','B', 200)
insert into #mytable values ('1001','02/01/2021','B', 200)

insert into #mytable values ('1001','03/01/2021','A', 100)
insert into #mytable values ('1001','03/01/2021','A', 100)

insert into #mytable values ('1002','01/01/2021','A', 100)
insert into #mytable values ('1002','01/01/2021','A', 100)

insert into #mytable values ('1002','02/01/2021','B', 200)
insert into #mytable values ('1002','02/01/2021','A', 100)


i need to select the record for the same date and voucher should not have amount for both procedure 'A' and 'B'
in the above data vouno 1001 and date '01/01/2021' have amount for procedure 'A' and 'B' and
vouno 1002 and date '02/01/2021' have amount for procedure 'A' and 'B'

i need that two records only

Thanks
Kasim
Posted
Updated 4-Feb-21 1:37am

1 solution

Seems simple enough:
SQL
SELECT DISTINCT
    vouno,
    voudate
FROM
    #mytable As T
WHERE
    Exists(SELECT 1 FROM #mytable As A WHERE A.vouno = T.vouno And A.voudate = T.voudate And A.[procedure] = 'A')
And
    Exists(SELECT 1 FROM #mytable As B WHERE B.vouno = T.vouno And B.voudate = T.voudate And B.[procedure] = 'B')
;
Demo[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900