princevezt princevezt - 4 months ago 23
SQL Question

FIFO on inventory stocks

I have a query that returns all the available stocks on the database. I
need to make a stored procedure to get the itemCode, batchNo, quantity, price of specific item that the users entered.

----------------------------------------------
| id | itemCode | batchNo | availQty | price |
----------------------------------------------
| 1 | item_1 | 07292016| 5 | 5.50 |
| 2 | item_1 | 07312016| 10 | 5.50 |
| 3 | item_1 | 08012016| 2 | 6.00 |


My problem is that, if the users entered 6 quantity to be purchased, how can I get the first 2 lines of result to get the total quantity of 6?

Result must be:

07292016 --- 5
07312016 --- 1

Answer

I doubt this is efficient and the query is horid, but the following will give you what you want:

-- Create Test Data
create table #Items
(
    id int not null primary key,
    itemCode varchar(30) not null,
    batchNumber varchar(30) not null,
    availQty int not null,
    price smallmoney not null
);

insert into #Items
values
    (1, 'item_1', '07292016', 5, 5.50),
    (2, 'item_1', '07312016', 10, 5.50),
    (3, 'item_1', '08012016', 2, 6.00)

select 
    *
from
    #Items
;

-- Set up required parameters
declare 
    @requiredItemCode varchar(30) = 'item_1',
    @requiredQty int = 6

-- The query to get the required result
select
    i.*,
    case
        when
            @requiredQty - 
            isnull(
                (
                    select
                        sum(availQty)
                    from
                        #Items i2
                    where
                        i2.itemCode = i.itemCode
                        and i2.id < i.Id
                ),
                0) < i.availQty
        then
            @requiredQty - 
            isnull(
                (
                    select
                        sum(availQty)
                    from
                        #Items i2
                    where
                        i2.itemCode = i.itemCode
                        and i2.id < i.Id
                ),
                0)
        else
            i.availQty
    end as qtyToTake
from 
    #Items i
where   
    i.ItemCode = @requiredItemCode
    and 
    case
        when
            @requiredQty - 
            isnull(
                (
                    select
                        sum(availQty)
                    from
                        #Items i2
                    where
                        i2.itemCode = i.itemCode
                        and i2.id < i.Id
                ),
                0) < i.availQty
        then
            @requiredQty - 
            isnull(
                (
                    select
                        sum(availQty)
                    from
                        #Items i2
                    where
                        i2.itemCode = i.itemCode
                        and i2.id < i.Id
                ),
                0)
        else
            i.availQty
    end > 0

-- Clean up test data
drop table #Items

Output:

id          itemCode batchNumber availQty    price
----------- -------- ----------- ----------- ---------------------
1           item_1   07292016    5           5.50
2           item_1   07312016    10          5.50
3           item_1   08012016    2           6.00

(3 row(s) affected)

id          itemCode batchNumber availQty    price                 qtyToTake
----------- -------- ----------- ----------- --------------------- -----------
1           item_1   07292016    5           5.50                  5
2           item_1   07312016    10          5.50                  1

(2 row(s) affected)
Comments