If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Subquery with subraction - challenging
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. |
#3
|
|||
|
|||
Subquery with subraction - challenging
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. |
#4
|
|||
|
|||
Subquery with subraction - challenging
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. |
Thread Tools | |
Display Modes | |
|
|