How to select a quantity of rows from one table based on quantities from another

Question

So with this select statement (excluding joins and where clauses)

SELECT [ProductNo], (mf_btol.OrderedBoxes - mf_btol.ActualBoxes) as [Remaining], [DespatchOrderNo]

This gives a list of remaining products and quantity of boxes required to complete an order.

What I want to do is select and display from a separate stock table an amount of box records based on how many of each product is required so I will be able to create a list of boxes to pick for the order.

I think I need to be using select top... and use order by to prioritize the boxes to be picked but can't imagine how to get the quantities in there.

Here is some made up data, so the results of the select above would be along the lines of this

ProductNo     Remaining      DespatchOrderNo
845121         3             6194
989222         2             6194

What I am wanting is

DOTNo     ProductNo      DespatchOrderNo
123456    845121         6194
123457    845121         6194
123458    845121         6194
124444    989222         6194
124445    989222         6194

So its basically creating a list of individual boxes on a stock table (unique by dot number) required to forfill the orders from the first select statement for multiple products.

Answer 1

Create sample data (You should have done that part yourself)

CREATE TABLE a
(
ProductNo int,
OrderedBoxes int,
ActualBoxes int,
DespatchOrderNo int
)

INSERT INTO a VALUES
(845121, 5,2, 6194),
(989222, 3,1, 6194)

CREATE TABLE b
(
ProductNo int,
DOTNo int
)

INSERT INTO b VALUES
(845121, 123453),
(845121, 123454),
(845121, 123455),
(989222, 124442),
(989222, 124443)

Update:

Assuming it is really sql server 2000, I think (though can't test) a dynamic sql solution should work. something like this (tested on my 2012 version, don't think it's anything 2000 can't do)

DECLARE @Sql varchar(8000) = ''
SELECT @Sql = @Sql +
       'SELECT * FROM (SELECT TOP '+ CAST((OrderedBoxes - ActualBoxes) as varchar) + ' DOTNo, a.ProductNo, DespatchOrderNo 
       FROM a 
       INNER JOIN b ON(a.ProductNo = b.ProductNo)
       WHERE a.ProductNo = '+ CAST(a.ProductNo as varchar) +'
       ORDER BY DOTNo) iq UNION ALL ' 
FROM a 

SET @Sql = LEFT(@Sql, LEN(@Sql) - 10) -- remove the last union all

EXEC(@Sql)

For 2008 version or higher (and you really should upgrade your sql since 2000 is no longer supported by Microsoft), Here is one solution with cte and ROW_NUMBER:

WITH CTE AS (
SELECT OrderedBoxes - ActualBoxes As Remaining, 
       DOTNo, a.ProductNo, 
       DespatchOrderNo, 
       ROW_NUMBER() OVER(PARTITION BY a.ProductNo ORDER BY DOTNo) RN
FROM a INNER JOIN
b ON(a.ProductNo = b.ProductNo)
) 

SELECT DOTNo, ProductNo, DespatchOrderNo
FROM CTE
WHERE RN <= Remaining

Output:

DOTNo       ProductNo   DespatchOrderNo
----------- ----------- ---------------
123453      845121      6194
123454      845121      6194
123455      845121      6194
124442      989222      6194
124443      989222      6194