question

Looking at the picture, it seems like those people are using an application to put the SampleIDs in the slots? If yes, then I do not see any great difficulty to identify the slot. The application can get the grid reference easily? If I follow the excel format **Column as Letter and Row as number**, the slots are like A1, A2, A3....,B1,B2,B3.......J8,J9,J10, you could easily calculate the slot number as **Column Letter Custom value** + **Row number** where **Column Letter Custom value** can be customized as A=0, B=10, C=20....,J=90 So A1 = 0+1 = 1 A2 = 0+2 = 2 A3 = 0+3 = 3 A4 = 0+4 = 4 ..... B1 = 10+1 = 11 B2 = 10+2 = 12 B3 = 10+3 = 13 B4 = 10+4 = 14 ...... J8 = 90 + 8 = 98 J9 = 90 + 9 = 99 J10 = 90 + 10 = 100 So you will always get a unique slot value. All you need is to make sure that there no one can put two SampleIDs on the same slot. However, if you just wanted to find any unassigned slot in ascending order, then the following may help you Declare @AlreadyOccupiedSlots table ( SampleId varchar(10), SlotNumber int ) insert @AlreadyOccupiedSlots select 'a', 1 union all select 'b', 12 union all select 'c', 25 union all select 'd', 37; WITH TopHundredSlots AS ( select top (100) ROW_NUMBER() over(order by (select null)) SlotNumber from sys.all_columns c cross join sys.all_columns c1 ) select MIN(SlotNumber) SlotNumberVacant from TopHundredSlots t where not exists( select * from @AlreadyOccupiedSlots a where t.SlotNumber = a.SlotNumber ) **EDIT:** Declare @AlreadyOccupiedSlots table ( SlotNumber int ) insert @AlreadyOccupiedSlots select 1 union all select 2 union all select 12 union all select 15 union all select 23 union all select 32 union all select 34 union all select 35 union all select 41 union all select 43 union all select 53 union all select 65 union all select 79; declare @SlotsAvailable table ( SlotNumber int ); WITH TopHundredSlots AS ( select top (100) ROW_NUMBER() over(order by (select null)) SlotNumber from sys.all_columns c cross join sys.all_columns c1 ) insert @SlotsAvailable select * from TopHundredSlots where SlotNumber not in (select SlotNumber from @AlreadyOccupiedSlots) declare @Samples table ( SampleId varchar(10) ); WITH Top87Samples AS ( select top (87) 'Sample' + convert(varchar,(ROW_NUMBER() over(order by (select null)))) SampleNumber from sys.all_columns c cross join sys.all_columns c1 ) insert @Samples select * from Top87Samples select * from ( select *, ROW_NUMBER() over(order by convert(int,substring(SampleId, PATINDEX('%[0-9]%', SampleId), LEN(SampleId)))) SampleSorted from @Samples )s join ( select *, ROW_NUMBER() over(order by SlotNumber) SlotsSorted from @SlotsAvailable ) sa on s.SampleSorted = sa.SlotsSorted

declare @CryoboxID int = 1;
; with available as (select
    row_number() over (order by tally.number) n, tally.number Cryoslot 
    from master.dbo.spt_values tally left join dbo.SampleTable s
        on tally.number = s.Cryoslot and CryoboxID = @CryoboxID
    where 
        tally.number between 1 and 100 and tally.[type] = 'P' 
        and s.ID_Text is null /* get avaiable numbers only */
), 
samples as (
    select top 100
        row_number() over (order by ID_Text) n, CryoboxID, Cryoslot
        from dbo.SampleTable
        where CryoboxID is null and Cryoslot is null
)
    update s set CryoboxID = @CryoboxID, Cryoslot = a.Cryoslot
        from samples s inner join available a
            on s.n = a.n;