Subquery with subraction - challenging

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.

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" wrote in message
...
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.

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

"vanderghast" wrote:

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" wrote in message
...
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.

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 ?

"clalc" wrote:

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

"vanderghast" wrote:

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" wrote in message
...
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.