MySQL: possible to calculate the total inventory cost based on amount per received order? -


i calculate total inventory cost subtracting received orders till received order quantity matches inventory level. way have accurate cost of total inventory.

i have following table individual order receipt lines. order_receipt_article

id   order_receipt_id   article_id   quantity   price 1    1000               456          10         100 2    1001               456          5          120 3    1002               456          15         140 4    1003               456          20         100 5    1004               456          5          90

and table inventory invetory

id   article_id   level 1    456          43

now want total value of stock on hand can calculated like

(5 x 90) + (20 x 100) + (15 x 140) + (3 x 120) = 4910

i post i've tried have no idea start solve this.

/edit

i'm not asking basic sum() have total of 55 received units have 43 in stock. want know exact value of stock based on received units. looking @ first table oldest order (id 1) not used in calculation because within orders 2 t/m 5 have received total of 45.

this should solve issue, uses session variables keep running total of number of units processed. unusually couldn't work without temp table ordering wasn't working properly, way found solve issue.

drop table if exists order_receipt_article_test; create table order_receipt_article_test(id int unsigned, order_receipt_id int unsigned, article_id int unsigned, quantity int unsigned, price int unsigned);  drop table if exists inventory_test; create table inventory_test(id int unsigned, article_id int unsigned, `level` int unsigned);  insert order_receipt_article_test values     (1, 1000, 456, 10, 100),     (2, 1001, 456, 5, 120),     (3, 1002, 456, 15, 140),     (4, 1003, 456, 20, 100),     (5, 1004, 456, 5, 90);  insert inventory_test values     (1, 456, 43);  set @varticleid := 0; set @vcumulativequantity := 0;  drop temporary table if exists zsort;  create temporary table zsort select a.article_id, a.`level`, b.quantity, b.price, b.order_receipt_id      inventory_test left outer join     order_receipt_article_test b on a.article_id = b.article_id order a.article_id, b.order_receipt_id desc;  select article_id, sum(rowprice)     (     select @vcumulativequantity := if(article_id != @varticleid, `level`, @vcumulativequantity) levelreset, if(@vcumulativequantity < quantity, @vcumulativequantity * price, quantity * price) rowprice, @vcumulativequantity := if(@vcumulativequantity < quantity, 0, @vcumulativequantity - quantity) quantityadjust, @varticleid := article_id article_id zsort     ) group article_id;

regards,

james


Comments

Post a Comment

Popular posts from this blog

python - Operations inside variables -

this question has answer here: evaluating mathematical expression in string 12 answers i want make python script finds maximum possible output using 2 operations. problem says there syntax error @ 2nd last line, in b in there. how can fix it? x = 0 = int(input("1st number:")) c = int(input("2nd number:" )) e = int(input("3rd number:" )) in range(4): if == 0: b = "+" elif == 1: b = "-" elif == 2: b = "/" else: b = "*" j in range(4): if j == 0: d = "+" elif j == 1: d = "-" elif j == 2: d = "/" else: d = "*" k = b c d e print(k) if you're after variables e on single line, couldn...
Read more

Generic Map Parameter java -

i having lot of methods of type: public static list<eduusers> getdetails(class c, map<string, integer> params) { list<eduusers> details = null; session session = hibernateutil.getsessionfactory().opensession(); transaction tx = null; try { tx = session.begintransaction(); criteria cr = session.createcriteria(c); (map.entry<string, integer> entry : params.entryset()) { cr.add(restrictions.eq(entry.getkey(), entry.getvalue())); } details = cr.list(); tx.commit(); } catch (exception asd) { log.debug(asd.getmessage()); if (tx != null) { tx.commit(); } } { session.close(); } return details; } i trying have generic method them , have written far: public static <t> list<t> getdetails(class<t> c, class<t> b) { l...
Read more

arrays - What causes a java.lang.ArrayIndexOutOfBoundsException and how do I prevent it? -

what arrayindexoutofboundsexception mean , how rid of it? here code sample triggers exception: string[] name = {"tom", "dick", "harry"}; for(int = 0; i<=name.length; i++) { system.out.print(name[i] +'\n'); } your first port of call should documentation explains reasonably clearly: thrown indicate array has been accessed illegal index. index either negative or greater or equal size of array. so example: int[] array = new int[5]; int boom = array[10]; // throws exception as how avoid it... um, don't that. careful array indexes. one problem people run thinking arrays 1-indexed, e.g. int[] array = new int[5]; // ... populate array here ... (int index = 1; index <= array.length; index++) { system.out.println(array[index]); } that miss out first element (index 0) , throw exception when index 5. valid indexes here 0-4 inclusive. correct, idiomatic for statement here be: for (int index = 0; index <...
Read more