IF OBJECT_ID('tempdb..#Cell') IS NOT NULL DROP TABLE #Cell;
IF OBJECT_ID('tempdb..#PaletType') IS NOT NULL DROP TABLE #PaletType;

CREATE TABLE #Cell(CellId int, Qty int);

INSERT INTO #Cell VALUES (1,305);
INSERT INTO #Cell VALUES (2,280);
INSERT INTO #Cell VALUES (3,250);
INSERT INTO #Cell VALUES (4,230);
INSERT INTO #Cell VALUES (5,210);

CREATE TABLE #PaletType(PaletTypeId int, Capacity int);

INSERT INTO #PaletType VALUES (1, 240);
INSERT INTO #PaletType VALUES (2, 50);
INSERT INTO #PaletType VALUES (3, 10);

GO

WITH cte(CellId,PaletTypeId,Capacity,Qty,PaletQuantity) AS (
	SELECT CellId,
           - 1 AS PaletTypeId,
           1000000 AS Capacity,
           Qty,
           0 AS PaletQuantity
    FROM #Cell
    UNION ALL
    SELECT CellId,
           PaletTypeId,
           Capacity,
           Qty,
           PaletQuantity
    FROM 
     (SELECT cte.CellId,
             p.PaletTypeId,
             p.Capacity,
             cte.Qty - (cte.Qty / p.Capacity) * p.Capacity AS Qty,
             cte.Qty / p.Capacity AS PaletQuantity,
             MAX(p.Capacity) OVER(PARTITION BY cte.CellId) AS max_cap
      FROM cte
      INNER JOIN #PaletType AS p
      ON (p.Capacity < cte.Capacity)) AS a
    WHERE a.Capacity = a.max_cap)
SELECT CellId,PaletTypeId,PaletQuantity
FROM cte
WHERE PaletQuantity > 0
ORDER BY CellId,PaletTypeId

IF OBJECT_ID('tempdb..#Cell') IS NOT NULL DROP TABLE #Cell;
IF OBJECT_ID('tempdb..#OrderDetail') IS NOT NULL DROP TABLE #OrderDetail;

CREATE TABLE #Cell(CellId int, GoodsId int, Qty int)

INSERT INTO #Cell VALUES (1, 1, 72)
INSERT INTO #Cell VALUES (2, 1, 72)
INSERT INTO #Cell VALUES (3, 1, 40)
INSERT INTO #Cell VALUES (11, 2, 240)
INSERT INTO #Cell VALUES (12, 2, 240)
INSERT INTO #Cell VALUES (13, 2, 80)
INSERT INTO #Cell VALUES (14, 2, 10)
INSERT INTO #Cell VALUES (21, 3, 30)
INSERT INTO #Cell VALUES (31, 4, 100)


CREATE TABLE #OrderDetail (OrderDetailId int, GoodsId int, Qty int)

INSERT INTO #OrderDetail VALUES (101, 1, 120)
INSERT INTO #OrderDetail VALUES (102, 2, 100)
INSERT INTO #OrderDetail VALUES (103, 3, 70)
INSERT INTO #OrderDetail VALUES (104, 4, 55)

GO

WITH cte(OrderDetailId,GoodsId,Qty,CellId,min_CellId,lvl,max_qty,cell_qty,used) AS (
	SELECT OrderDetailId,GoodsId,Qty,0 AS CellId,0 AS min_CellId,0 AS lvl,0 AS max_qty,0 AS cell_qty,
		  CAST(',' AS VARCHAR(MAX)) AS used
	FROM  #OrderDetail
	UNION ALL
	SELECT * FROM 
	(SELECT 
			OrderDetailId,
			cte.GoodsId,
			cte.Qty - cell.Qty AS Qty,
			cell.CellId,
			MIN(cell.CellId) OVER(PARTITION BY cte.GoodsID) AS min_CellId,
			cte.lvl + 1 AS lvl,
			MAX(cell.Qty) OVER(PARTITION BY cte.GoodsID) AS max_qty,
			cell.Qty AS cell_qty,
			cte.used + CAST(cell.CellId AS VARCHAR(MAX)) + ',' AS used
	 FROM cte
	 INNER JOIN #Cell AS cell
	 ON (cte.GoodsId = cell.GoodsId AND cte.Qty >= cell.Qty
		 AND cte.used NOT LIKE '%,' + CAST(cell.CellId AS VARCHAR(MAX)) + ',%')) AS a
	WHERE cell_qty = max_qty AND min_CellId = CellId)
SELECT OrderDetailId,CellId
FROM cte
WHERE CellId <> 0
ORDER BY cte.OrderDetailId,CellId