Selecting all records for which the flag is not set
How do I find everything bookingNo
where the flag is hasPaid
not set to 0?
Can't get it to work. I must receive a booking 2-4-8 if the flag is hasPaid
not set to 1
Many thanks
if object_id('tempdb..#testFlag') is not null
drop table #testFlag
create table #testFlag (BookingNo int, HasPaid bit)
insert into #testFlag (BookingNo, HasPaid)
select 1, 0 union ALL
select 1, 1 union ALL
select 2, 0 union ALL
select 3, 0 union ALL
select 3, 1 union ALL
select 3, 0 union ALL
select 4, 0 union ALL
select 4, 0 union ALL
select 4, 0 union ALL
select 5, 0 union ALL
select 5, 1 union ALL
select 6, 0 union ALL
select 6, 1 union ALL
select 7, 0 union ALL
select 7, 1 union ALL
select 8, 0
+3
developer9969
source
to share
3 answers
You can use GROUP BY
with CASE
based HAVING
like this.
Query
SELECT BookingNo FROM #testFlag
GROUP BY BookingNo
HAVING SUM(CASE WHEN HasPaid = 1 THEN 1 ELSE 0 END) = 0
Output
BookingNo
2
4
8
+3
ughai
source
to share
select BookingNo
from #testFlag
except
select BookingNo
from #testFlag
where HasPaid = 1
Besides
If you want to use a group then
SELECT BookingNo FROM #testFlag
GROUP BY BookingNo
HAVING MAX(cast(HasPaid as int)) = 0;
+2
paparazzo
source
to share
I think the problem is with duplicate BookingNo. If you want to select all BookingNo where HasPaid is not 0, try this:
select distinct BookingNo from #testFlag t1 where HasPaid != 0
and not exists (select 1
from #testFlag t2 where HasPaid = 0 and t2.BookingNo = t1.BookingNo
)
If you want to select all BookingNo where HasPaid is not 1, try this:
select distinct BookingNo from #testFlag t1 where HasPaid != 1
and not exists (select 1
from #testFlag t2 where HasPaid = 1 and t2.BookingNo = t1.BookingNo
)
0
Inka
source
to share