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

Question

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.

Answer 1

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 :)