Untitled

How to achieve right result from a table list
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

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


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


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

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
    a.SupplyID,
    a.MaterialNumber,
    a.ExpiryDate,
    a.Quantity,
    a.test1,
    godis =(SELECT top 1 a.Quantity - c.Quantity
            FROM (SELECT
                    b.RequestID,
                    b.MaterialNumber,
                    b.RequiredDate,
                    b.Quantity,
                    test2 = DENSE_RANK() OVER(PARTITION BY b.MaterialNumber ORDER BY b.RequestID)
                  FROM TC74_SupplyRequest b) c
            WHERE MaterialNumber = c.MaterialNumber AND a.test1 = c.test2),
    a.lll
FROM
(
    SELECT
        SupplyID,
        MaterialNumber,
        ExpiryDate,
        Quantity,
        test1 = DENSE_RANK() OVER(PARTITION BY MaterialNumber ORDER BY SupplyID),
        lll = 321
     FROM
        TC74_Supply
) a

select *, Quantity - isnull(Demand, 0) Vasted
from TC74_Supply
outer Apply
(
  select sum (Quantity) Demand
    from TC74_SupplyRequest
   where TC74_Supply.MaterialNumber = TC74_SupplyRequest.MaterialNumber
     and TC74_Supply.ExpiryDate >= TC74_SupplyRequest.RequiredDate
     and TC74_SupplyRequest.RequiredDate >=
         (select isnull (max(a.ExpiryDate),
                            (select min(RequiredDate) from TC74_SupplyRequest))
            from TC74_Supply a
           where a.MaterialNumber = TC74_Supply.MaterialNumber
             and a.ExpiryDate < TC74_Supply.ExpiryDate)
) a