how to get latest entry from a table for an item and do arithmatic operation on it?
- by I Like PHP
i have below tables
tbl_rcv_items
st_id | item_id |stock_opening_qnty |stock_received_qnty |stock_rcvd_date
14 1 0 70 2010-05-18
15 16 0 100 2010-05-06
16 10 0 59 2010-05-20
17 14 0 34 2010-05-20
20 1 70 5 2010-05-12
tbl_issu_items
issue_id refer_issue_id item_id item_qntt item_updated
51 1 1 5 2010-05-18 19:34:29
52 1 16 6 2010-05-18 19:34:29
53 1 10 7 2010-05-18 19:34:29
54 1 14 8 2010-05-18 19:34:29
75 7 1 12 2010-05-18 19:40:52
76 7 16 1 2010-05-18 19:40:52
77 7 10 1 2010-05-18 19:40:52
78 7 14 1 2010-05-18 19:40:52
79 8 1 3 2010-05-19 11:28:50
80 8 16 5 2010-05-19 11:28:50
81 8 10 6 2010-05-19 11:28:50
82 8 14 7 2010-05-19 11:28:51
87 10 1 2 2010-05-19 12:51:03
88 10 16 0 2010-05-19 12:51:03
89 10 10 0 2010-05-19 12:51:03
90 10 14 0 2010-05-19 12:51:03
91 14 1 1 2010-05-19 18:43:58
92 14 14 3 2010-05-19 18:43:58
tbl_item_detail
item_id item_name
1 shirt
2 belt
10 ball pen
14 vim powder
16 pant
NOW if i want total available quantity for each item till today using both table
total available quantity for an item =stock_opening_qnty+stock_received_qnty(LATEST ENTRY FROM (tbl_rcv_item) for that item id according to stock_rcvd_date) - SUM(item_qntt)
for eg:
if i want to know the available quantity for item_id=1 till today(25-05-2010) then it shoud be 70+5(latest entry for item_id till 25/5/2010)-23( issued till 25/5/2010)=52
i write below query ,
SELECT tri.item_id, tid.item_name, (tri.stock_opening_qnty + tri.stock_received_qnty) AS
totalRcvQntt, SUM( tii.item_qntt ) AS totalIsudQntt FROM tbl_rcv_items tri
JOIN tbl_issu_items tii ON tii.item_id = tri.item_id
JOIN tbl_item_detail tid ON tid.item_id=tri.item_id WHERE
tri.stock_rcvd_date <= CURDATE() GROUP BY (tri.item_id)
which results
Array
(
[0] => Array
(
[item_id] => 1
[item_name] => shirt
[totalRcvQntt] => 70
[totalIsudQntt] => 46
)
[1] => Array
(
[item_id] => 10
[item_name] => ball pen
[totalRcvQntt] => 59
[totalIsudQntt] => 16
)
[2] => Array
(
[item_id] => 14
[item_name] => vim powder
[totalRcvQntt] => 34
[totalIsudQntt] => 20
)
[3] => Array
(
[item_id] => 16
[item_name] => pant
[totalRcvQntt] => 100
[totalIsudQntt] => 17
)
)
in above result total isuse quantity for shirt(item_id=1) shoube be 23 whereas results reflects 46 bcoz there are two row regrading item_id=1 in tbl_rcv_items, i only need the latest one(means which stock_rcvd_date is less than tommorow)
please tell me where i doing mistake?? or rewrite the best query.
thanks a lot!
