title: product view sort by priority
body: <p>i am working on production order view, where i have list of product with Qty and hour targets. User will be able to select which batch ordes should go live and then base on some prioritites, user should get result about which batch will be build on which line. From start each line will have same hour target limit which is 150 pieces or 8 working hours.From my point of view the good way should be using recursion (union all) and go line by line and feed them by product base on priority (from 1 to n). If all lines will be full (doesn't matter if limit is achieved or working time), recursion will continue but rest rows will be highlighted as Status (1 in limit, 2 out of limit). Or simple recursion is not enought for this and should be used some &quot;knapsack&quot;? Thank you for your advice</p>
  <pre><code>declare @Linelimit int;
  declare @WorkingTime int;
  set @Linelimit = 150;
  set @WorkingTime = 8;
  
  drop table #avaliableOrders
  
  create table #avaliableOrders
  (
      Batch nvarchar(50),
      Product nvarchar(50),
      Qty int,
      HourTarget decimal(4,2)
  );
  
  insert into #avaliableOrders(Batch,Product,Qty,HourTarget) 
  values
  ('B002801','BikeA','20','0.69'),
  ('B002801','BikeA','1','0.03'),
  ('B002801','BikeA','1','0.03'),
  ('B002801','BikeA','7','0.24'),
  ('B002804','BikeA','1','0.03'),
  ('B002804','BikeA','3','0.1'),
  ('B0028010','BikeA','20','0.69'),
  ('B0028010','BikeA','1','0.03'),
  ('B0028010','BikeA','4','0.14'),
  ('B0028010','BikeA','2','0.07'),
  ('B0028010','BikeA','3','0.1'),
  ('B0028010','BikeA','1','0.03'),
  ('B0028010','BikeA','13','0.45'),
  ('B0029002','BikeA','2','0.07'),
  ('B0029002','BikeA','3','0.1'),
  ('B0029002','BikeA','1','0.03'),
  ('B0029002','BikeA','7','0.24'),
  ('B0029002','BikeA','9','0.31'),
  ('B0029002','BikeA','1','0.03'),
  ('B0029002','BikeA','3','0.1'),
  ('B002802','BikeB','30','0.68'),
  ('B002802','BikeB','1','0.02'),
  ('B002803','BikeC','2','0.05'),
  ('B002803','BikeC','580','13.18'),
  ('B002805','BikeD','24','0.49'),
  ('B002805','BikeD','120','2.45'),
  ('B002806','BikeE','3','0.17'),
  ('B002806','BikeE','1','0.06'),
  ('B002807','BikeE','1','0.06'),
  ('B002808','BikeF','244','13.56'),
  ('B002809','BikeF','10','0.56'),
  ('B0029001','BikeG','2','0.05'),
  ('B0029001','BikeG','2','0.05'),
  ('B0029001','BikeG','1','0.03'),
  ('B0029001','BikeG','6','0.16'),
  ('B0029001','BikeG','10','0.27'),
  ('B0029001','BikeG','2','0.05'),
  ('B0029001','BikeG','24','0.65'),
  ('B0029001','BikeG','40','1.08'),
  ('B0029001','BikeG','6','0.16'),
  ('B0029001','BikeG','1','0.03'),
  ('B0029001','BikeG','15','0.41'),
  ('B0029003','BikeG','3','0.08'),
  ('B0029003','BikeG','3','0.08'),
  ('B0029003','BikeG','3','0.08'),
  ('B0029003','BikeG','50','1.35'),
  ('B0029003','BikeG','2','0.05'),
  ('B0029003','BikeG','1','0.03'),
  ('B0029003','BikeG','1','0.03'),
  ('B0029003','BikeG','1','0.03'),
  ('B0029003','BikeG','1','0.03'),
  ('B0029003','BikeG','1','0.03'),
  ('B0029003','BikeG','50','1.35'),
  ('B0029003','BikeG','2','0.05'),
  ('B0029003','BikeG','2','0.05'),
  ('B0029003','BikeG','10','0.27'),
  ('B0029003','BikeG','2','0.05'),
  ('B0029003','BikeG','1','0.03'),
  ('B0029003','BikeG','3','0.08'),
  ('B0029003','BikeG','4','0.11'),
  ('B0029003','BikeG','1','0.03'),
  ('B0029003','BikeG','1','0.03'),
  ('B0029003','BikeG','12','0.32')
  
  
  create table #priorityOrders
  (
      Product nvarchar(50),
      Line nvarchar(50),
      Priority int
  );
  
  insert into #priorityOrders(Product,Line,Priority) 
  values
  ('Line1','BikeA','1'),
  ('Line1','BikeB','2'),
  ('Line1','BikeC','3'),
  ('Line1','BikeG','4'),
  ('Line2','BikeD','1'),
  ('Line2','BikeF','2'),
  ('Line2','BikeB','3'),
  ('Line3','BikeE','1'),
  ('Line3','BikeC','2'),
  ('Line3','BikeA','3'),
  ('Line4','BikeB','1'),
  ('Line4','BikeH','3'),
  ('Line4','BikeF','4'),
  ('Line4','BikeG','4')
  </code></pre>
  <p>edit:
  if query go by simple recursion then result will looks like this, and looks that one line is overloaded and B0029003 should be moved to line 4 (and then is reason why i am see chance to use it for knapsack)
  <a href="https://i.stack.imgur.com/I92Wl.png" rel="nofollow noreferrer">example of query result </a></p>