Allocate 0 or 1
Allocate 0 or 1
(OP)
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
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
Talk To Other Members
RE: Allocate 0 or 1
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
RE: Allocate 0 or 1
RE: Allocate 0 or 1
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.