Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate lengths

Author  Topic 

Pirre0001
Starting Member

19 Posts

Posted - 2014-04-18 : 17:24:57
I'm having trouble figuring out how many lengths will fit on a drum, and how many drums it gets.

I have five lengths to be wound on drums (lengths may vary)

Ex: Table tblLength

Count---|qty----|Unit
---------------------
2 |2500 |meter
1 |3800 |meter
1 |4200 |meter
1 |6500 |meter
1 |8300 |meter


On every drum I only have a max capacity for 14,300 meters.

How can I use a smart SQL to calculate it like this:

To wrap on every drum:

1 x 8800 (2 x 2500 + 1 x 3800)
1 x 10700 (1 x 4200 + 1 x 6500)
1 x 8300 (1 x 8300)

No lengths may be cut.

Pirre0001
Starting Member

19 Posts

Posted - 2014-04-19 : 16:04:58
Someone?
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-19 : 16:26:42
What is the algorithm that you want?
On the first drum why don't you put the 4200 length as well to make 1300?




==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Pirre0001
Starting Member

19 Posts

Posted - 2014-04-19 : 17:56:35
I have a number of different lengths to be wound on a drum. Drum capacity is 14 300 meters. I want to figure out how many lengths it goes on every drum a 14 300 meters. The lengths I have are the ones from table tblLength as I showed above.

I would then fill the drum as much as possible. The lengths are joined so several lengths can be rolled up on the same drum.
I need a smart SQL for this...

quote:
Originally posted by nigelrivett

What is the algorithm that you want?
On the first drum why don't you put the 4200 length as well to make 1300?




==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-19 : 18:01:38
Can you give an example. The one you gave just seems to have random lengths on each drum.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Pirre0001
Starting Member

19 Posts

Posted - 2014-04-19 : 18:52:04
The example that I gave the first is what I mean. From Table tblLength are a number of lengths to be wound onto a drum with a capacity of 14,300 meters.
It will take these lengths and try to fill up as many drums are required.

In length

Count---|qty----|Unit
---------------------
2 |2500 |meter
1 |3800 |meter
1 |4200 |meter
1 |6500 |meter
1 |8300 |meter


Out length to drum.
1 x 8800 (2x2500 + 1x3800)
1 x 10700 (1x4200 + 1x6500)
1 x 8300 (1x8300)

This is not the optimum lengths to fill the drum with. It may be possible to take them in other arrangements. ..

quote:
Originally posted by nigelrivett

Can you give an example. The one you gave just seems to have random lengths on each drum.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-19 : 19:14:39
The first drum has 5500 free. Why does it not put a 4200 on that.
so
1 x 13000 (2x2500 + 1x3800 + 1x4200)
1 x 6500 (1x6500)
1 x 8300 (1x8300)


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-19 : 19:43:20
Here's one option

declare @tblLength table (num int, qty int, unit varchar(20))

insert @tblLength
values
(2,2500,''),
(1,3800,''),
(1,4200,''),
(1,6500,''),
(1,8300,'')

;with cte as
(
select i=1, qty, unit from @tblLength where num >= 1
union all
select i=i+1, t.qty, t.unit from @tblLength t, cte where t.qty=cte.qty and t.unit = cte.unit and t.num >= i+1
),
cte2 as
(
select *, id=row_number() over (order by qty desc) from cte
),
cte3 as
(
select j=1, qty, unit, id, tot=qty, drum=1 from cte2 where id = 1
union all
select j=j+1, cte2.qty, cte2.unit, cte2.id,
tot=case when cte3.tot+cte2.qty > 14300 then cte2.qty else cte3.tot+cte2.qty end,
drum=case when cte3.tot+cte2.qty > 14300 then cte3.drum + 1 else cte3.drum end
from cte2,cte3 where cte2.id = j+1
)
select drum, qty, unit from cte3


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Pirre0001
Starting Member

19 Posts

Posted - 2014-04-20 : 13:09:02
Thanks!

Can u tell me what the third view cte3 trying to accomplish. I may not cte3 to work and have not know what it does.

quote:
Originally posted by nigelrivett

Here's one option

declare @tblLength table (num int, qty int, unit varchar(20))

insert @tblLength
values
(2,2500,''),
(1,3800,''),
(1,4200,''),
(1,6500,''),
(1,8300,'')

;with cte as
(
select i=1, qty, unit from @tblLength where num >= 1
union all
select i=i+1, t.qty, t.unit from @tblLength t, cte where t.qty=cte.qty and t.unit = cte.unit and t.num >= i+1
),
cte2 as
(
select *, id=row_number() over (order by qty desc) from cte
),
cte3 as
(
select j=1, qty, unit, id, tot=qty, drum=1 from cte2 where id = 1
union all
select j=j+1, cte2.qty, cte2.unit, cte2.id,
tot=case when cte3.tot+cte2.qty > 14300 then cte2.qty else cte3.tot+cte2.qty end,
drum=case when cte3.tot+cte2.qty > 14300 then cte3.drum + 1 else cte3.drum end
from cte2,cte3 where cte2.id = j+1
)
select drum, qty, unit from cte3


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-20 : 14:25:31
Cte3 takes the first row and allocates it to drum 1. It then goes through the following rows allocating to the same drum until it's full then starts on the next. Keeps going until it runs out of lengths.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -