Prev: DatePart
Next: 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" <clalc(a)discussions.microsoft.com> 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: DatePart
Next: AVG BETWEEN DAYS