user3697498 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.


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/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!

Answer Source

You can try something like this

select date, item,
       sum(case when date > getdate() and buy_sell = 'sell' then -Quantity
                else Quantity
           End) as SumQuantity
from YourTable
group by date, item
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download