I have two tables Inventory and Orders in SQL Server.
Say Inventory reflects the current and future inventory for all orders placed.
In this case, the company is short 300 socks based on the table.
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/27/2016 Buy
Socks 500 10/29/2016 Sell
Shoes 800 10/30/2016 Buy
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:
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!