I wrote some SQL code for a VB.net program that allowed for an item to be stored into a rack. Each rack spot had the capacity for one item and only one item could be inserted into it. My code selected the top empty rack and then filled it to be later checked out. Now the problem has changed so that you can put 2 items in each spot. I am not sure how to change my code so that I can allow for an extra item.
I have 2 tables
RackID, RackName, Active
The RackID is a primary key and is just 1,2,3,4....etc.etc.
The RackName is a description of the location; H01, H02, H03....etc.etc.
The Active is a bit that allows me to make the rack active or inactive for item storage.
columns: RackID, ItemNumber, NameIn, NameOut, TimeIn, TimeOut
RackID is the identifier between the 2 tables, is also 1,2,3,5....etc.etc.
ItemNumber is the distinct item, PLEASE NOTE IT IS A DATETIME
NameIn and NameOut are both int fields that tie into a name table
Time in/out are both time stamps
INSERT INTO table2 (RackID, ItemNumber, TimeIn, NameIN)
SELECT Top 1 RackID,
cast(@Date as smalldatetime),
cast (getdate() as smalldatetime),
Cast(@Name as int)
WHERE RackID NOT IN
left join table2 on table2 = table1
WHERE Active = 1
AND TimeOut IS NULL
AND NOT TimeIn IS NULL)
AND RackName LIKE 'H%'
ORDER BY LocID ASC
I would use a tally for this. I keep a view on my systems named cteTally so I can just use it whenever I need it. Here is the code for that.
create View [dbo].[cteTally] as WITH E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) select N from cteTally GO
Now to deal with your issue. I had to make some assumptions about some of the columns and such but this should at least get you close enough to tweak it into shape.
create table Rack ( RackID int identity primary key clustered , RackName varchar(25) not null , Active bit not null , NumSlots int not null ) create table RackItem ( ItemNumber datetime primary key clustered , RackID int not null foreign key references Rack(RackID) , NameIn int null , NameOut int null , TimeIn datetime null , TimeOut datetime null ) insert Rack select 'Rack1', 1, 2 union all select 'Rack2', 1, 2 union all select 'Rack3', 1, 2; insert RackItem select getdate(), 1, 1, 1, GETDATE(), null union all select dateadd(second, 1, getdate()), 1, 1, 1, GETDATE(), null union all select dateadd(second, 2, getdate()), 2, 1, 1, GETDATE(), null; select * from Rack r join RackItem ri on ri.RackID = r.RackID; with NumberedRackItems as ( select ri.*, ROW_NUMBER() over (PARTITION by RackID order by ItemNumber) as RowNum from RackItem ri ) select r.RackID , r.RackName , MAX(case when t.N = 1 then ri.ItemNumber end) as ItemNumber1 , MAX(case when t.N = 2 then ri.ItemNumber end) as ItemNumber2 from cteTally t join Rack r on t.N <= r.NumSlots left join NumberedRackItems ri on ri.RackID = r.RackID and ri.RowNum = t.N where r.Active = 1 group by r.RackID , r.RackName order by RackID;