Cheddar Cheddar - 1 year ago 119
SQL Question

SQL statement to select and store items into a shelf 2 items deep

I wrote some SQL code for a 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

Table 1

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.

Table 2 :is a log of all items checked in/out and who checked it in and out

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

I need help with the code to do the stored procedure. It needs to be able to loop through all the rack slots and then pick one to insert an item into. The constraints are that no more than 2 items can be inserted into one rack location, the program needs to put 1 item into every rack before it begins inserting the second one. So every rack location needs at least one item in it before the second one is added. Here is the code i used to do the stored procedure for just one item.

INSERT INTO table2 (RackID, ItemNumber, TimeIn, NameIN)
SELECT Top 1 RackID,
cast(@Date as smalldatetime),
cast (getdate() as smalldatetime),
Cast(@Name as int)
FROM table1
(SELECT table1.RackID
FROM table1
left join table2 on table2 = table1
WHERE Active = 1
AND RackName LIKE 'H%'

Answer Source

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

    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 N from cteTally

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
    , 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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download