Calculate how many days the stock was at a site with SQL-CodePudding

I'm trying to calculate how many days the stock for an item has been sitting at a site.

There are two tables: Stock table shows the items and stock currently on hand and Receipts table show the dates when the site has received stock and quantity.

I want to do a left outer join to see all the items in the Stock table and only the rows from the Receipts table with the date where there is still stock left from.

Stock

| Item  |Current Stock| Value |
|-------|-------------|-------|
|Blade  |8            |$40    |
|Table  |15           |$100   |
|Screen |3            |$30    |

Receipts

| Item  |Receipt Date| Quantity|
|-------|------------|---------|
|Blade  |1/3/2020    |  20     |
|Blade  |12/10/2021  |  10     |
|Blade  |1/5/2022    |  5      | 
|Table  |3/4/2020    |  10     |
|Table  |5/1/2021    |  7      |
|Table  |7/10/2021   |  5      |
|Table  |8/1/2021    |  5      |

Dates are in mm/dd/yyyy format. Assuming the current date here is 2/1/2022.

Desired Results

| Item  |Current Stock| Value |Receipt Date|Age in Days|
|-------|-------------|-------|------------|-----------|
|Blade  |8            |$40    |12/10/2021  |53         |  
|Table  |15           |$100   |5/1/2021    |276        |
|Screen |3            |$30    |            |           |

Logic:

| Item  |Receipt Date  | Quantity|Running Sum|Running Sum-Current Stock|
|-------|--------------|---------|-----------|-------------------------|
|Blade  |1/3/2020      |  20     |35         |27                       | 
|Blade  |**12/10/2021**|  10     |15         |7                        |
|Blade  |1/5/2022      |  5      |5          |0                        |

For example: Currently there are 8 units of Blades in stock. The lastest receipt (on 1/5/2022) was 5 units. So there are still 3 units remaining from the 12/10/2021 receipt date. I want to see the first receipt date where the (Running Sum-Current Stock) is greater than 0. This is based on FIFO (First In First Out)

Thanks in advance.

CodePudding user response：

You didn't mention the name of DBMS. My answer is for SQL Server. For other DBMS you need to change datediff() function.

Schema and insert statements:

 create table Stock(Item    varchar(50), Current_Stock  int, Value int);
 insert into Stock values('Blade'   ,8  ,40);
 insert into Stock values('Table'   ,15 ,100);
 insert into Stock values('Screen'  ,3  ,30);
 
 create table Receipts(Item varchar(50), Receipt_Date date, Quantity int);
 insert into Receipts values('Blade','1/3/2020',    20);
 insert into Receipts values('Blade','12/10/2021',  10);
 insert into Receipts values('Blade','1/5/2022',    5);
 insert into Receipts values('Table','3/4/2020',    10);
 insert into Receipts values('Table','5/1/2021',    7);
 insert into Receipts values('Table','7/10/2021',   5);
 insert into Receipts values('Table','8/1/2021',    5);

Query:

 with Recepts_with_runningtotal_qty as
 (
    select *, sum(Quantity)over(partition by Item order by Receipt_Date desc) running_total_qty from Receipts
 ),
 current_stock as
 (
    select *, (select max(Receipt_Date) from Recepts_with_runningtotal_qty r where r.running_total_qty>s.current_stock and s.Item=r.Item)Receipt_Date
    from Stock s
 )
 select *,datediff(day, Receipt_Date,'2/1/2022')Age_in_Days from current_stock

Output:

Item	Current_Stock	Value	Receipt_Date	Age_in_Days
Blade	8	40	2021-12-10	53
Table	15	100	2021-05-01	276
Screen	3	30	null	null

db<>fiddle here

For Oracle you can use below query:

 with Recepts_with_runningtotal_qty as
 (
    select Item , Receipt_Date, Quantity, sum(Quantity)over(partition by Item order by Receipt_Date desc) running_total_qty from Receipts
 ),
 current_stock as
 (
    select Item , Current_Stock, Value, (select max(Receipt_Date) from Recepts_with_runningtotal_qty r where r.running_total_qty>s.current_stock and s.Item=r.Item)Receipt_Date
    from Stock s
 )
 select Item, Current_Stock, Value, Receipt_Date,(to_date('1 Feb 2022','DD MM YY')-Receipt_Date)Age_in_Days from current_stock

Output:

ITEM	CURRENT_STOCK	VALUE	RECEIPT_DATE	AGE_IN_DAYS
Blade	8	40	10-DEC-21	53
Table	15	100	01-MAY-21	276
Screen	3	30	null	null

Query 2: without using common table expression

select Item, Current_Stock, Value, Receipt_Date,(to_date('1 Feb 2022','DD MM YY')-Receipt_Date)Age_in_Days
     from
     (
       select Item, Current_Stock, Value, Receipt_Date,Quantity, Running_Total_Qty,
       row_number()over(partition by Item order by Receipt_Date desc)rn
       from
       (
         Select S.Item, S.Current_Stock, S.Value, R.Receipt_date, R.Quantity,
         sum(Quantity)over(partition by R.Item order by Receipt_Date desc) running_total_qty
         From Stock S 
         Left outer join Receipts R On (S.Item = R.Item)
       )
       where Running_Total_Qty>= Current_Stock or Running_Total_Qty is null
     )
     where rn=1

Output:

ITEM	CURRENT_STOCK	VALUE	RECEIPT_DATE	AGE_IN_DAYS
Blade	8	40	10-DEC-21	53
Screen	3	30	null	null
Table	15	100	01-MAY-21	276

db<>fiddle here

CodePudding user response：

You could declare variable with your current date, or use GETDATE() - DECLARE @Today AS DATE SET @Today = GETDATE or some other date if you need.

And then, you can use DATEDIFF, like that:

SELECT DATEDIFF(day, @Today, Receipt Date) AS date_diff_days

After that just perform left outer join it should work fine. Have fun :)