Get Store Value using FIFO T-SQL

Question

I have table that contain all of my product details. and I want to get store value by FIFO Method,

All Purchases

If my stock is 3 sql give me 9000,means (3*3000)

If my stock is 5 sql give me 15000,means (5*3000)

If my stock is 6 sql give me 17000,means (5*3000)+(1*2000)

If my stock is 15 sql give me 36000,means (5*3000)+(8*2000)+(2*2500)

Thanks in advance

Which version of SQL Server are you using? Also, please remove the `MySQL` tag. — sgeddes, Sep 21 '14 at 22:18
possible duplicate of [How to implement FIFO in sql](http://stackoverflow.com/questions/25152990/how-to-implement-fifo-in-sql) — Mihai, Sep 21 '14 at 23:04
this is a good article : https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/ — Pouria Sharif, Oct 24 '15 at 16:06

Laurence · Accepted Answer · 2014-09-21T22:19:59.763

You can use a numbers table to transform the source data into 1 row per product item and then add them up:

-- generate numbers 1 to 10000
;with n as (
    select top 10000
        row_number() over(order by t1.number) as n
    from
        master..spt_values t1
            cross join
        master..spt_values t2    
), x as (
-- explode source table into one price row for each item
    select
        row_number() over (order by p.[date]) rn,
        p.price
    from
        product p
            inner join
        n
            on p.amount >= n.n
) select
    sum(price)
from
    x
where
    rn <= @stock;

This doesn't detect @stock being too big. If amount can bigger than 10,000 you'll need to look at generating more numbers.

Example SQLFiddle

is there any simple way? cause my source table contain 5000 rows and i need to execute this query for 12000 product — Pouria Sharif, Sep 21 '14 at 22:50

Get Store Value using FIFO T-SQL

1 Answers1