Allocate 0 or 1

[wexas45]

Good Morning All,

Test Table

Booking Ref No: Booking Ref No 1
L01 1
L01 0
L01 0
L02 1
L03 1
L04 1
L05 1
L04 0

Can anyone kindly assit me on this. I have column on one of my tables with duplicate booking ref no. I need to create a new column which will allocate 1 or 0 to that booking ref. 1 if it's a unique booking ref no: and 0 if that booking ref no: is a duplicate.

Thanks in advance

 

[kristof]

Hi,

insert into [SandBox].[dbo].[UniqueBookingRef]
select
'L01'
,
CASE
WHEN count([ref]) > 0 THEN 0
ELSE 1
END
from [SandBox].[dbo].[UniqueBookingRef]
WHERE ref = 'L01'

The first time you enter L01, you will get 1, any new attempts will get 0

 

[wexas45]

thanks for your reply, is there way that i can do this without inserting into a new table?

 

[kristof]

It's logical to decide that 0 or 1 value at inserting, but it doesn't really matter.

Essentially this is the most important part:

select
CASE
WHEN count([ref]) > 0 THEN 0
ELSE 1
END
from [SandBox].[dbo].[UniqueBookingRef]
WHERE ref = 'L01'

That's the logic that determines if it's unique or not.