This challenge is based on a problem discussed in the MSDN TSQL forum. The challenge is to create a query for a report that shows expiring material. There is a table with material that has expiry dates. Another table holds requests for the expiring material. You need to show how much material will be expiring because there are no requests in the queue for it.

Supply Table

SupplyID MaterialNumber ExpiryDate Quantity
-------- -------------- ---------- --------
1		 11111			10-12-2011 100
2		 11111			10-18-2011 700
3	     11111			01-15-2012 500
4		 22222			11-18-2011 250
5		 22222			05-01-2012 475
6		 33333			12-17-2011 200
7		 33333			04-01-2012 300

SupplyRequest Table

  
RequestID MaterialNumber RequiredDate Quantity
--------- -------------- ------------ --------
1		  11111			 10-01-2011	  50
2		  11111			 10-14-2011	  600
3		  22222			 10-17-2011	  400
4		  22222			 04-02-2012   125
5         33333			 12-22-2011	  175
6		  33333			 01-10-2012   400

MaterialNumber ExpiryDate Quantity
-------------- ---------- --------
11111          10-12-2011 50
11111          10-18-2011 100
11111          01-15-2012 500
22222          11-18-2011 0
22222		   05-01-2012 200
33333          12-17-2011 200
33333          04-01-2012 -275

1. If required quantity is greater than the quantity of the expiring material you can take the left over amount from the next expiring supply.
2. A request can be subtracted from a supply only if the request date is less than or equal to the supply expiry date.
3. A request must be subtracted from the supply with lowest supply expiry date that meets the previous condition.
4. A supply can become negative only if there are no future supplies for the given material.
5. For a given supply material number there will be no duplicate expiry dates in table Supply.
6. The output should by ordered by MaterialNumber, ExpiryDate.
7. No assumptions should be made about the order of the data in the two input tables.
8. Quantities in both input tables will always be greater than zero.

    
IF OBJECT_ID('TC74_Supply','U') IS NOT NULL
  DROP TABLE TC74_Supply
GO

CREATE TABLE TC74_Supply(
	SupplyID INT IDENTITY,
	MaterialNumber VARCHAR(5),
	ExpiryDate DATE,
	Quantity INT
)
GO
INSERT INTO TC74_Supply(MaterialNumber,ExpiryDate,Quantity)
SELECT '11111','10-12-2011',100 UNION ALL
SELECT '11111','10-18-2011',700 UNION ALL
SELECT '11111','01-15-2012',500 UNION ALL
SELECT '22222','11-18-2011',250 UNION ALL
SELECT '22222','05-01-2012',475 UNION ALL
SELECT '33333','12-17-2011',200 UNION ALL
SELECT '33333','04-01-2012',300

SELECT * FROM TC74_Supply
GO

IF OBJECT_ID('TC74_SupplyRequest','U') IS NOT NULL
  DROP TABLE TC74_SupplyRequest
GO

CREATE TABLE TC74_SupplyRequest(
	RequestID INT IDENTITY,
	MaterialNumber VARCHAR(5),
	RequiredDate DATE,
	Quantity INT
)
GO

INSERT INTO TC74_SupplyRequest(MaterialNumber,RequiredDate,Quantity)
SELECT '11111','10-01-2011',50 UNION ALL
SELECT '11111','10-14-2011',600 UNION ALL
SELECT '22222','10-17-2011',400 UNION ALL
SELECT '22222','04-02-2012',125 UNION ALL
SELECT '33333','12-22-2011',175 UNION ALL
SELECT '33333','01-10-2012',400 

SELECT * FROM TC74_SupplyRequest
GO

1. The solution should be a single query that starts with a "SELECT" or “;WITH”

1. Read the Submission Guidelines and make sure that your solution follows them.
2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
3. Your solution can use features from all versions up to and including SQL Server 2012.