Systems Engineering and RDBMS

A simple inventory query

Posted by decipherinfosys on April 30, 2008

This is one of the common requirements so we thought we would put this out there on the blog as a solution.  At one of our clients, they had running inventory list of the items in their warehouse.  This inventory list was stored in a table which for the sake of brevity had these attributes that we were interested in:

CREATE TABLE INVN_ITEM
(
ITEM_NBR        VARCHAR2(20),
ITEM_QTY        NUMBER(10),
BIN_NBR            VARCHAR2(10),
DATE_CREATED    DATE
)
/

And here is a sample of how the data looked like:

item_nbr item_qty bin_nbr date_created
——– ——– ——- ————-
Item_1         10  D1        02/01/2008
Item_1         20  D2        02/01/2008
Item_1        100  D1        04/01/2008
Item_1         50  D2        04/02/2008
Item_1         30  D3        04/03/2008
Item_1         20  D8        04/04/2008
Item_2         10  D1        02/01/2008
Item_2         20  D2        02/01/2008
Item_2         77  D1        04/01/2008
Item_2         32  D2        04/02/2008
Item_2         52  D3        04/03/2008
Item_2         33  D8        04/04/2008

Whenever a new record was created in this table, the running total of the item’s quantity was input into the item_qty column based on an item and the bin_nbr that it was placed in.  Example: If Item_1 is present in bins D1, D2, D3 and D8, when a new record got created on say April 1st 2008 for item_1 in bin D1, the new 80 quantity got added to the existing 20 in that bin for that item and the record was created with 100.

What the report was supposed to show was the total quantity as of any given run for an item regardless of the bin numbers.

There are a lot of ways to do this.  We will show some of the approaches.

Approach #1: This approach uses the analytic function first_value() in Oracle.  Using that function and by ordering on the date_created in a desc fashion and by partitioning on the item_nbr and bin_nbr, one can select the max value for the item per bin and then apply the grouping at the top of that.

SQL> select item_nbr, sum(item_qty)
2    from (
3  select distinct item_nbr, bin_nbr,
4         first_value(item_qty) over(partition by item_nbr, bin_nbr
5                                       order by date_created desc) item_qty
6    from invn_item
7    )
8   group by item_nbr
9  /

ITEM_NBR   SUM(QTY)
——– ———-
Item_1        200
Item_2        194

Approach #2: This will work in Oracle as well as SQL Server:  This approach uses the MAX() function and a co-related sub-query:

SQL> select item_nbr, sum(item_qty)
2    from invn_item
3   where date_created = ( select max(date_created)
4                              from invn_item it2
5                             where it2.item_nbr = invn_item.item_nbr
6                               and it2.bin_nbr = invn_item.bin_nbr )
7   group by item_nbr
8  /

ITEM_NBR   SUM(QTY)
——– ———-
Item_1        200
Item_2        194

Another approach is to use the row_number() function (this is also available in SQL Server 2005) and partition on the item_nbr and bin_nbr and order by date_created desc and then pick up the first record in the outer query and do a grouping on it.  It will prevent the usage of the DISTINCT clause as well that was used in Approach #1 above.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: