Timbo avatar image
Timbo asked

Update table with missing numbers

Hello. I have a table with 100 rows. Column 1 contains SampleID; column 2 slot number. Some samples have slot numbers, some haven't. Those that have slot numbers can hold any value between 1 and 100. Each value must be unique. This conforms to a 10 x 10 cryobox with each slot containing a sample vial. My question is how can I allocate samples without slots a unique slot number which is not already taken? The people who fill these boxes have their own ways, some starting at the bottom, others the top, some in the middle like solitaire, some random so the row order does not always correspond to the slot numbers. First time asker, please be gentle.![![alt text][1]][1] [1]: /storage/temp/4530-cryobox.png
cryobox.png (12.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Usman Butt avatar image
Usman Butt answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@Timbo It looks like you already have a way to zoom in on the particular CryoboxID and limit the number of samples to update (to fill the box), In this case all you can select the next batch of samples (based on their CryoboxID and Cryoslot is NULL condition) and update from the join. I will not use table var but will fill the slot numbers on the fly as needed (for specific CryoboxID), you can change it back to your variable if you want:

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
        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;
Please let me know is this helps.
1 Like 1 ·
Timbo avatar image Timbo commented ·
Brilliant answer but not quite what I am looking for! Sample by sample, I can calculate the next available slot, no problem. I don't think I explained myself very well. The graphic shows 13 samples placed randomly in the 10 x 10 grid. What I need is a stored procedure which takes a batch of from 1 to a maximum 87 new samples (100 less 13) and assigns available slots from top left. i.e. NewSample 1, slot 2; NewSample 2, slot 4;... NewSample 87, slot 100. Your solution is really clever but would involve updating samples one by one. Is it possible to do all samples in one update? I have a table of 87 new samples and a table of 87 available slots, just don't know how to join them. FYI, no system is used - people add samples to the grid as they see fit. They can place a sample anywhere. Wrong I know but there are hundreds of boxes and as they are held in -80 freezers, I don't think we can reorder them.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@Timbo This is not difficult to do, but please confirm a couple of points: - What is the version of the SQL Server you have? - What would you like the stored procedure to do? Do you want it to return you a set consisting of 2 columns (first column storing the SampleID and the second - deduced SlotNumber)? Or you would like the procedure to insert the rows to the table (based on the list of samples you pass as a parameter to the procedure) and then return the results of the select statement from this table (with samples and deduced slot numbers already inserted)? Or your table already has the records for all 100 samples and all you need is the update of the table filling the SlotNumber for all those records which have this value missing? With last scenario, you don't have to pass anything to the procedure (if all 100 records already exists and the update is all that is needed). In case if some other scenario is more suitable, please confirm how you would like to pass the the list of samples to the procedure? There are 3 popular ways (as a delimited list, as xml fragment or as the table valued type). Please clarify. Thank you.
0 Likes 0 ·
Timbo avatar image Timbo commented ·
Hi Oleg SQL Server 2012. My Sample table has a sample ID (ID_TEXT) as its primary key and CryoboxID as a foreign key. It also has a Cryoslot column (smallint). When new samples arrive they have no CryoboxID or Cryoslot until added to a box. When a box has 100 samples a new box is used. My stored procedure establishes how many slots are available in the cryobox and their respective slot numbers with row number i.e. Row 1, Slot 2; Row 2, Slot 4; etc. (held in a table variable). It then selects an equal number of new samples to the slots available (another table variable) but when selecting these samples, the ROW_NUMBER is based on the Samples table of which there are tens of thousands, so I don't get row numbers from 1 to n. I'm embarrassed to admit that I have wasted hours trying to join new samples to available slots and cannot update the sample table with one update statement.
0 Likes 0 ·
Usman Butt avatar image Usman Butt Timbo commented ·
So you procedure work on one cryoboxId at a time? If that is the case then see if the edited answer leads you to what you want. Otherwise, please post some dummy data and the required output.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@Timbo The script in my previous comment will update the samples for a specific box (change my script to assign the correct value, I used 1 as an example). Don't worry about top 100, it will work for any number of samples which are not taken care of yet. So if you already have 20 samples with Cryoslot filled in then the script will figure the next 80 samples to update to fill the box. If none of the samples belongs to the box yet then it will update all 100. Please let me know if you would like to have me to explain how the script works. I used the existing table to borrow the set of numbers, you can use it too, or, optionally, you can come up with the small tally table with consecutive numbers, such table proves to be extremely useful to work with SQL Server data.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.