Subquery with subraction - challenging

[clalc]

Sorry I repost my question because I think my previous post got burried.
I have to subtract a value from one table and calculate the result in
another table. Here are tables and desired values:

Table Demand
Item Qty Date
aaa 10 03-mar-2010
aaa 7 05-mar-2010
aaa 5 12-mar-2010
ccc 8 06-mar-2010
ccc 12 09-mar-2010

Table Stock
Item Qty
aaa 12
bbb 9
ccc 6

the result should be table Net_Demand
Item Qty Date (how to get column Qty and left from
Stock table values)
aaa 0 03-mar-2010 (10-12, left 2)
aaa 5 05-mar-20 (7-2, left 0)
aaa 5 12-mar-2010 (5-0, left 0)
ccc 2 06-mar-2010 (8-6, left 0)
ccc 12 09-mar-2010 (12-0, left 0)

Here is SQL that was suggested to solve it, but it does not give all the
results as above:
SELECT Demand.Item, Demand.Date, (SELECT IIF((Stock.[Qty] -
Sum([XX].[QTY])) >0,0, (Stock.[Qty] - Sum([XX].[QTY]))*-1) FROM
Demand AS [XX] WHERE [XX].Date <= Demand.Date AND [XX].Item
= Demand.Item) AS ToOrder
FROM Demand LEFT JOIN Stock ON Demand.Item = Stock.Item
ORDER BY Demand.Item, Demand.Date;
Would you have any suggestions ? Please help.

 

[vanderghast]

Your statement is almost right, but fails to handle the third case, because
there are three cases: the stock exceeds the cumulated demand; the actual
demand is the first one to exceed the stock; a previous demand has exceed
the stock.

And given that I prefer join to sub-queries, I would suggest:


SELECT a.item,
a.date,
SUM(p.qty) AS upToExcludingThisOne,
LAST(a.qty) +SUM(p.qty) AS upToIncludingThisOne,
iif( LAST(stock.qty) > upToIncludingThisOne, 0,
iif( LAST(stock.qty) > upToExcludingThisOne, upToIncludingThisOne -
LAST(stock.qty),
LAST(a.qty)
)
) AS netDemand

FROM (demand AS a LEFT JOIN stock ON a.item = stock.item)
LEFT JOIN demand AS p ON p.item = a.item AND p.date < a.date

GROUP BY a.item, a.date




Vanderghast, Access MVP

 

[clalc]

if I run the sql, system asks me for a.date, stock.qty and p.date as if it
did not read everything together...

Your statement is almost right, but fails to handle the third case, because
there are three cases: the stock exceeds the cumulated demand; the actual
demand is the first one to exceed the stock; a previous demand has exceed
the stock.

And given that I prefer join to sub-queries, I would suggest:


SELECT a.item,
a.date,
SUM(p.qty) AS upToExcludingThisOne,
LAST(a.qty) +SUM(p.qty) AS upToIncludingThisOne,
iif( LAST(stock.qty) > upToIncludingThisOne, 0,
iif( LAST(stock.qty) > upToExcludingThisOne, upToIncludingThisOne -
LAST(stock.qty),
LAST(a.qty)
)
) AS netDemand

FROM (demand AS a LEFT JOIN stock ON a.item = stock.item)
LEFT JOIN demand AS p ON p.item = a.item AND p.date < a.date

GROUP BY a.item, a.date




Vanderghast, Access MVP

Sorry I repost my question because I think my previous post got burried.
I have to subtract a value from one table and calculate the result in
another table. Here are tables and desired values:

Table Demand
Item Qty Date
aaa 10 03-mar-2010
aaa 7 05-mar-2010
aaa 5 12-mar-2010
ccc 8 06-mar-2010
ccc 12 09-mar-2010

Table Stock
Item Qty
aaa 12
bbb 9
ccc 6

the result should be table Net_Demand
Item Qty Date (how to get column Qty and left from
Stock table values)
aaa 0 03-mar-2010 (10-12, left 2)
aaa 5 05-mar-20 (7-2, left 0)
aaa 5 12-mar-2010 (5-0, left 0)
ccc 2 06-mar-2010 (8-6, left 0)
ccc 12 09-mar-2010 (12-0, left 0)

Here is SQL that was suggested to solve it, but it does not give all the
results as above:
SELECT Demand.Item, Demand.Date, (SELECT IIF((Stock.[Qty] -
Sum([XX].[QTY])) >0,0, (Stock.[Qty] - Sum([XX].[QTY]))*-1) FROM
Demand AS [XX] WHERE [XX].Date <= Demand.Date AND [XX].Item
= Demand.Item) AS ToOrder
FROM Demand LEFT JOIN Stock ON Demand.Item = Stock.Item
ORDER BY Demand.Item, Demand.Date;
Would you have any suggestions ? Please help.

 

[clalc]

Never mind my previous post, I mislabelled columns. The result of the query
you suggested is:
item date upToExcludingThisOne UptoIncludingThisOne netDemand
aaa 03/02/2010
10
aaa 10/02/2010 10 17
5
aaa 15/02/2010 17 22
5
ccc 05/02/2010
8
ccc 08/02/2010 8 20
12

If you compare results of this query with required results, it is almost the
same, except the first number should be 0 and the dates got shifted around.
Any suggestion for that ?

if I run the sql, system asks me for a.date, stock.qty and p.date as if it
did not read everything together...

Your statement is almost right, but fails to handle the third case, because
there are three cases: the stock exceeds the cumulated demand; the actual
demand is the first one to exceed the stock; a previous demand has exceed
the stock.

And given that I prefer join to sub-queries, I would suggest:


SELECT a.item,
a.date,
SUM(p.qty) AS upToExcludingThisOne,
LAST(a.qty) +SUM(p.qty) AS upToIncludingThisOne,
iif( LAST(stock.qty) > upToIncludingThisOne, 0,
iif( LAST(stock.qty) > upToExcludingThisOne, upToIncludingThisOne -
LAST(stock.qty),
LAST(a.qty)
)
) AS netDemand

FROM (demand AS a LEFT JOIN stock ON a.item = stock.item)
LEFT JOIN demand AS p ON p.item = a.item AND p.date < a.date

GROUP BY a.item, a.date




Vanderghast, Access MVP

Sorry I repost my question because I think my previous post got burried.
I have to subtract a value from one table and calculate the result in
another table. Here are tables and desired values:

Table Demand
Item Qty Date
aaa 10 03-mar-2010
aaa 7 05-mar-2010
aaa 5 12-mar-2010
ccc 8 06-mar-2010
ccc 12 09-mar-2010

Table Stock
Item Qty
aaa 12
bbb 9
ccc 6

the result should be table Net_Demand
Item Qty Date (how to get column Qty and left from
Stock table values)
aaa 0 03-mar-2010 (10-12, left 2)
aaa 5 05-mar-20 (7-2, left 0)
aaa 5 12-mar-2010 (5-0, left 0)
ccc 2 06-mar-2010 (8-6, left 0)
ccc 12 09-mar-2010 (12-0, left 0)

Here is SQL that was suggested to solve it, but it does not give all the
results as above:
SELECT Demand.Item, Demand.Date, (SELECT IIF((Stock.[Qty] -
Sum([XX].[QTY])) >0,0, (Stock.[Qty] - Sum([XX].[QTY]))*-1) FROM
Demand AS [XX] WHERE [XX].Date <= Demand.Date AND [XX].Item
= Demand.Item) AS ToOrder
FROM Demand LEFT JOIN Stock ON Demand.Item = Stock.Item
ORDER BY Demand.Item, Demand.Date;
Would you have any suggestions ? Please help.

 

[clalc]

Thank you so very much. You are my absolute hero. The query indeed delivers
what it supposed to.