user3697498 - 1 year ago 79
SQL Question

# Checking quantities for future dates and returning shorts

I have two tables Inventory and Orders in SQL Server.

Say Inventory reflects the current and future inventory for all orders placed.

Inventory

``````Item       Quantity
Shoes      100
Coats      200
Socks      -300
``````

In this case, the company is short 300 socks based on the table.

Orders reflects both items we buy from manufacturers and items we sell.

``````Item      Quantity      Ship_ArrivalDate      Buy_Sell
Shoes     300           10/27/2016            Sell
Socks     500           10/29/2016            Sell
``````

So if today is 10/24/2016, I actually have 0 shoes today. I have 200 socks on 10/27/2016 and then be short 300 socks on 10/29/2016 because Inventory reflects all future transactions. And after the 300 sell and 800 buy of shoes, I have 100 in inventory. But that means I will be short on 10/27/2016 for shoes.

I want to build a table that reflects all future dates and checks for days that I am going to be short.

So the table would end up looking like:

Output

``````Date         Item        Quantity_Short
10/27/2016   Shoes       -400
10/29/2016   Socks       -300
``````

I am confused on how to check for each date and really don't know where to start. I understand how to check for 1 date in isolation, but how do you essentially "loop" through all future dates and save the dates and quantities into an output table?

Maybe writing a function that takes in a date and does the math then use apply?

Any starting advice is appreciated. Thanks!

``````select date, item,