Hi,

I would like to solve the following problem without using cursors...

I have 4 tables
batchtype
batch
item
itemcriteria

batchtype contains the type of possible batches
batch rows are instances of specific batchtypes
items are assigned to batches according to criteria detailed in
itemcriteria.
item table has a foreign key referencing batch primary key.
itemcriteria contains item primary key and criteria data

The twist is that each batch row is limited the number of items associated
with it. batches are of a certain type, and when a batch is "full" another
batch of the same type will be created.Different batches have different
capacities, but the set of capacities is finite and I have hard coded that.


I currently have a loop that looks at unassigned items and creates the
appropriate batch rows.  These batches then need "filling", then I go round
the loop until all items are assigned to batches.

I'm looking for a way to associate items with a batchs (capacity limited)
without using cursors.


Hope this is clear, and someone has an elegant efficient solution :-)

Thanks
Martin

Please post DDL and sample data:
http://www.aspfaq.com/etiquette.asp?id=5006

BatchTypes

ID  Capacity
1        3
2        3
3        3
4        3
10      2
11      2
12      2
13      2

Batch
ID BatchTypeID
1 1
2 2
3 3
4 4
5 10
6 11
7 12
8 13
9 1
10 13


ItemCriteria
ID BatchTypeID
1 1
2 1
3 1
4 1
5 13
6 13
7 13
8 13

Item
ID BatchID
1 1
2 1
3 1
4 9
5 8
6 8


Items 1-4 are batchtype 1 which has a capacity of 3, so the 1-3 go in batch
1, 4 goes in the next batch of that type 9
items 5-8 are batchtype 13, capacity 2, so 5-6 go in batch 8, 7-8 go in
batch 10

Thanks again
Martin


Show quoteHide quote

Martin,

Just FYI.  That is not really DDL.  The purpose of asking for DDL and such
is so we can simply run a script and have a test case to work with.  In
order to try out any possible solutions someone may have they would have to
manually create the tables and populate them with your data.  Most of us
don't have the time to do that extra step and it leaves room for error.  By
providing a script(s) you increase your chances tremendously of getting not
only a speedy reply but an accurate one.

Show quoteHide quote

Hi Andrew and Alejandro,

Thanks for your interest in this.  Point taken about the DDL.

Alejandro,  thanks very much for the sql, I will try it asap.

Thanks again
Martin

Martin,

Try this script. I am basing this soltion in calculating the rank of every
batch in its batch_type group, and the rank of every item in the batch_type
of its criteria.

use northwind
go

create table BatchTypes (
[ID] int,
Capacity int
)
go

create table Batch (
[ID] int,
BatchTypeID int
)
go

create table ItemCriteria (
[ID] int,
BatchTypeID int
)
go

create table Item (
[ID] int,
BatchID int
)
go

insert into BatchTypes values(1,        3)
insert into BatchTypes values(2,        3)
insert into BatchTypes values(3,        3)
insert into BatchTypes values(4,        3)
insert into BatchTypes values(10,      2)
insert into BatchTypes values(11,      2)
insert into BatchTypes values(12,      2)
insert into BatchTypes values(13,      2)
go

insert into Batch values(1, 1)
insert into Batch values(2, 2)
insert into Batch values(3, 3)
insert into Batch values(4, 4)
insert into Batch values(5, 10)
insert into Batch values(6, 11)
insert into Batch values(7, 12)
insert into Batch values(8, 13)
insert into Batch values(9, 1)
insert into Batch values(10, 13)
go

insert into ItemCriteria values(1, 1)
insert into ItemCriteria values(2, 1)
insert into ItemCriteria values(3, 1)
insert into ItemCriteria values(4, 1)
insert into ItemCriteria values(5, 13)
insert into ItemCriteria values(6, 13)
insert into ItemCriteria values(7, 13)
insert into ItemCriteria values(8, 13)
go

-- insert into Item values(1, 1)
-- insert into Item values(2, 1)
-- insert into Item values(3, 1)
-- insert into Item values(4, 9)
-- insert into Item values(5, 8)
-- insert into Item values(6, 8)

insert into Item values(1, null)
insert into Item values(2, null)
insert into Item values(3, null)
insert into Item values(4, null)
insert into Item values(5, null)
insert into Item values(6, null)
go

create view vw_v1
as
select
    b.BatchTypeID as batch_type_id,
    (select count(*) from batch as b1 where b1.BatchTypeID = b.BatchTypeID and
b1.[id] <= b.[id]) as batch_rank,
    b.[id] as batch_id,
    bt.capacity
from
    BatchTypes as bt
    inner join
    batch as b
    on bt.[id] = b.BatchTypeID
go

create view vw_v2
as
select
    ic.BatchTypeID item_batch_type_id,   
    (select count(*) from item i1 inner join itemcriteria as ic1 on i1.[id] =
ic1.[id] where ic1.BatchTypeID = ic.BatchTypeID and i1.[id] <= i.[id]) as
item_batch_type_rank,
    i.[id] as item_id
from
    item as i
    inner join
    itemcriteria as ic
    on i.[id] = ic.[id]
go

select
    v2.item_id,
    v1.batch_id
from
    vw_v1 as v1
    inner join
    vw_v2 as v2
    on v1.batch_type_id = v2.item_batch_type_id
where
    (((v2.item_batch_type_rank - 1) / v1.capacity) + 1) = v1.batch_rank
order by
    v2.item_id
go

drop view vw_v1, vw_v2
go

-- drop table BatchTypes, Batch, ItemCriteria, Item
go


AMB

Show quoteHide quote

Error converting data type nvarchar to int.
move records from table1 to table2 if does not exist in table3
Query Help
FIFO report
Performance Issues using Aggregate MAX function within a temp table