Allocate 0 or 1

wexas45 · Apr 1, 2010

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 · Apr 1, 2010

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 · Apr 1, 2010

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

kristof · Apr 1, 2010

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.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Allocate 0 or 1

wexas45

MIS

kristof

Programmer

wexas45

MIS

kristof

Programmer

Similar threads

Part and Inventory Search

Sponsor