SQL Query aggregation and subqueries

Tom:

I have a table that initially stores information about items in a warehouse stored in different bins. Table look like this where manual inventories are done every 3 months. Effective date is the sysdate when record is inserted.

Inventory:

Item_no Qty Bin Effective_Date
AC006 10 DC001 2/1/2002
AC006 20 DC002 2/1/2002

AC006 100 DC001 5/1/2002
AC006 50 DC002 5/2/2002
AC006 30 DC003 5/3/2002
AC006 20 DC008 5/4/2002

I need to calculate two things:

1. Total Qty of an item in inventory in a given bin. which I basically did by taking qty of the item in inventory with the max(effective_Date)+total_received and stored in that bin since that max(effective_date) ? total shipped from that bin since that max(effective_date).

2. Total qty of an item in inventory. Here I need to look at the highest effective date for an item at a bin. Bascially I need to run a SQL statement that gives me 200(sum last 4 recrods) for total of an item ?AC006?. How do you formulate that statement and exclude all the previous inventories for that item that are out of date.

3. Also, let me say I have the following table. Would it still work where I basically stored the items in different bins.

Inventory:

Item_no Qty Bin Effective_Date
AC006 10 DC001 2/1/2002
AC006 20 DC002 2/1/2002

AC006 100 DC003 5/1/2002
AC006 50 DC004 5/2/2002

The answer here should be 150 and not 180.

Thank you,