Prev: DatePartNext: AVG BETWEEN DAYS From: clalc on 4 Jun 2010 15:52 I have to loop through the data from one table (demand) and calculate total supply expected depending on the data from the other tables, here is example and expected result: Table A - (supply batches) Item Quantity a 10 b 8 c 12 d 20 Currently in stock C Item Quantity a 0 b 4 c 2 d 0 Table B - (demand) Item Date Quantity a 04-jun-2010 2 a 05-jun-2010 4 a 06-jun-2010 7 a 07-jun-2010 4 a 08-jun-2010 1 b 05-jun-2010 4 b 06-jun-2010 5 b 07-jun-2010 3 b 08-jun-2010 2 b 09-jun-2010 1 d 03-jun-2010 5 d 04-jun-2010 7 d 05-jun-2010 5 d 07-jun-2010 6 d 08-jun-2010 10 Expected result: Item Date Expected supply (currently in stock - demand) a 04-jun-2010 10 (0-2=-2, we need 10 - no part of batch can be obtained, 10-2=8) a 05-jun-2010 0 (8-4=4) a 06-jun-2010 10 (4-7=-3, we need 10 and we're left with 10-3=7) a 07-jun-2010 0 (7-4=3) a 08-jun-2010 0 (3-1=2) b 05-jun-2010 8 (4-4=0, we need 8 and we're left with 8-0=8) b 06-jun-2010 0 (8-5=3) b 07-jun-2010 8 (3-3=0, we need 8 and we're left with 8-0=8) b 08-jun-2010 0 (8-2=6) b 09-jun-2010 0 (6-1=5) d 03-jun-2010 20 (0-5=-5, we need 20 and we're left with 20-5=15) d 04-jun-2010 0 (15-7=8) d 05-jun-2010 0 (8-5=3) d 07-jun-2010 20 (3-6=-3, we need 20 and we're left with 20-3=17) d 08-jun-2010 0 (17-10=7) I don't mind to do it in couple of steps. Could anybody point me in the right direction ? Thank you. From: vanderghast on 7 Jun 2010 09:34 Seems that the following would do (assuming the data is as supplied it, it that it was not an over simplification): SELECT r.product, r.datestamp, LAST(r.quantity) , (LAST(m.quantity) - (SUM(s.quantity) - LAST(i.quantity)) MOD LAST(m.quantity) ) MOD LAST(m.quantity) AS qty FROM ( tableb AS r INNER JOIN tableb AS s ON r.product = s.product AND r.dateStamp >= s.dateStamp ) INNER JOIN currentlyInStock AS i ON r.product = i.product ) INNER JOIN tableA AS m ON r.product = m.product GROUP BY r.product, r.dateStamp ORDER BY r.product, r.dateStamp The expression (LAST(m.quantity) - (SUM(s.quantity) - LAST(i.quantity)) MOD LAST(m.quantity) ) MOD LAST(m.quantity) does all the job. Note that for a given product, LAST(m.quantity) is the value from tableA. So, for illustration, for *product B*, that values is 8, and for product B, that is reducible to : (8 - (SUM(s.quantity) - LAST(i.quantity)) MOD 8)) MOD 8 About LAST(i.quantity), again, for a given product, that is the constant from table currently in stock C, so, for product B, that is 4, ie. the initial in stock value. ( 8 - ((SUM(s.quantity) - 4) MOD 8 ) MOD 8 Now, for a given product, and a given dateStamp (GROUP BY r.product, r.dateStamp ), the expression SUM(s.quantity) returns the cumulative request, up to that date, for the given product. b 05-jun-2010 4 : ( 8 - (( 4 - 4) MOD 8 ) MOD 8 = 8 MOD 8 = 0*. b 06-jun-2010 5 : (8 - ((5+4 - 4) MOD 8) MOD 8 = (8-5) MOD 8 = 3 b 07-jun-2010 3 : ( 8 - ((3+5+4 - 4) MOD 8) MOD 8 = 8 MOD 8 = 0* b 08-jun-2010 2 : ( 8 - (2+3+5+4 - 4) MOD 8) MOD 8 = (8-2) MOD 8 = 6** b 09-jun-2010 1 : ( 8-(1+2+5+4 - 4) MOD 8) MOD 8 = (8-3) MOD 8 = 5** *: you use 8 instead of 0, but technically, it is useless to bring a new batch, and to not use it at all. Sounds that 0 is more appropriate, isn't it? **: you typed 0 as result, but the arithmetic expression you supplied returns the announced value (not zero). Vanderghast, Access MVP "clalc" wrote in message news:F82195E9-B168-4D4F-88A9-C1695289C286(a)microsoft.com...>I have to loop through the data from one table (demand) and calculate total > supply expected depending on the data from the other tables, here is > example > and expected result: > Table A - (supply batches) > Item Quantity > a 10 > b 8 > c 12 > d 20 > Currently in stock C > Item Quantity > a 0 > b 4 > c 2 > d 0 > Table B - (demand) > Item Date Quantity > a 04-jun-2010 2 > a 05-jun-2010 4 > a 06-jun-2010 7 > a 07-jun-2010 4 > a 08-jun-2010 1 > b 05-jun-2010 4 > b 06-jun-2010 5 > b 07-jun-2010 3 > b 08-jun-2010 2 > b 09-jun-2010 1 > d 03-jun-2010 5 > d 04-jun-2010 7 > d 05-jun-2010 5 > d 07-jun-2010 6 > d 08-jun-2010 10 > > Expected result: > Item Date Expected supply (currently in stock - demand) > a 04-jun-2010 10 (0-2=-2, we need 10 - no part of batch can be > obtained, 10-2=8) > a 05-jun-2010 0 (8-4=4) > a 06-jun-2010 10 (4-7=-3, we need 10 and we're left with 10-3=7) > a 07-jun-2010 0 (7-4=3) > a 08-jun-2010 0 (3-1=2) > b 05-jun-2010 8 (4-4=0, we need 8 and we're left with 8-0=8) > b 06-jun-2010 0 (8-5=3) > b 07-jun-2010 8 (3-3=0, we need 8 and we're left with 8-0=8) > b 08-jun-2010 0 (8-2=6) > b 09-jun-2010 0 (6-1=5) > d 03-jun-2010 20 (0-5=-5, we need 20 and we're left with 20-5=15) > d 04-jun-2010 0 (15-7=8) > d 05-jun-2010 0 (8-5=3) > d 07-jun-2010 20 (3-6=-3, we need 20 and we're left with 20-3=17) > d 08-jun-2010 0 (17-10=7) > I don't mind to do it in couple of steps. Could anybody point me in the > right direction ? > Thank you.  |  Pages: 1 Prev: DatePartNext: AVG BETWEEN DAYS