December 1, 2015 at 10:37 am
Hi All
I have an order table with order number and size of paper length.
I need to find out the different combination of orders that would add the corresponding size to give me the combined length.
My final goal is to find the different order combinations which would give me a combined length of less than say X.
Order Number Size
100 15
200 18
201 34
204 26
206 31
207 11
209 21
............ ....
.... ...
301 14
I need the combination of order number where combined length would be less than say 50
Thanks in advance
Regards
BJ
December 1, 2015 at 7:13 pm
December 3, 2015 at 4:16 am
If the maximum length of pakage could be reliably determined as for the test data case you can try static code
create table #orders (
n int,
l int
);
insert #orders(n,l) values
(100, 15),
(200, 18),
(201, 34),
(204, 26),
(206, 31),
(207, 11),
(209, 21),
(301, 14)
;
select t1.n,t1.l, t2.n,t2.l, t3.n,t3.l, t4.n,t4.l,
total=t1.l+isnull(t2.l,0)+isnull(t3.l,0)+isnull(t4.l,0)
from #orders t1
left join #orders t2 on t2.n != t1.n
and t1.l+t2.l <= 50
left join #orders t3 on t3.n not in(t2.n, t1.n )
and t1.l+t2.l+t3.l <= 50
left join #orders t4 on t4.n not in(t3.n, t2.n, t1.n)
and t1.l+t2.l+t3.l+t4.l <= 50
where t1.l <= 50
order by t1.n,t2.n,t3.n,t4.n;
Otherwise you may opt to recursion or dynamic sql.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply