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.
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? |
|
|
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. |
|
|
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.
|
|
|
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. |
|
|
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 lengthCount---|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.
|
|
|
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.so1 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. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2014-04-19 : 19:43:20
|
Here's one optiondeclare @tblLength table (num int, qty int, unit varchar(20))insert @tblLengthvalues(2,2500,''),(1,3800,''),(1,4200,''),(1,6500,''),(1,8300,'');with cte as(select i=1, qty, unit from @tblLength where num >= 1union allselect 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 = 1union allselect 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 endfrom 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. |
|
|
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 optiondeclare @tblLength table (num int, qty int, unit varchar(20))insert @tblLengthvalues(2,2500,''),(1,3800,''),(1,4200,''),(1,6500,''),(1,8300,'');with cte as(select i=1, qty, unit from @tblLength where num >= 1union allselect 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 = 1union allselect 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 endfrom 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.
|
|
|
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. |
|
|
|
|
|
|
|