SQL Server inner Join with the same table

Question

I have a stock table that figure outs my stock list distributed on our company stores storages and on main storage. Now I want to analyze that if I have critical stock at any store (that means less then 5 items) and enough resource in main storage I have to ship item to that stores with a query. I have another subject that I may add new item to main storage, so my query must remind me to sent at least 5 pieces to each store. Also if I opened a new store (I will insert a new row with NULL model, NULL stock to that table) it must list me the needed stock for first day.

Thanks.

(By the way I want to do that in SQL Server 2014 for a personal development not production. I haven't coded any program for last 12 years).

Update : 28/08/2017 10:42

Sorry guys if i couldnt explained my question well. I updated my referance table like that : dbo.stock

Referance Table And query output like that : Query Output

So - I want to check if all stores have company production in their storage, And if company opened a new store , i have to find it and have to ship 5 pieces from all products to it.

Thanks.

Answer 1

List of critical model per store and main resources in stock if any or cero

select a.sloc, a.model, a.stock critical, isnull(b.stock,0) 'Main Resources'
from stock a left join stock b on a.model=b.model and b.sloc='Main'
where a.stock<5

For the last part of your request, I'm not sure what is needed for a new opened store.

EDIT

This procedure does at least what you need, is not optimal, but you can work it up later.

begin transaction

declare @initialStock int=5
declare @stockDecrease int=0

insert into stock (sloc, model, stock)
    select (select a.sloc
from stock a 
where a.stock is null) n, m.model, @initialStock
from stock m where m.sloc='Main'  
set @stockDecrease= @@ROWCOUNT/4*@initialStock

delete stock where stock is null

update stock 
set stock=stock-@stockDecrease
where sloc='Main' 

if exists (select 1 from stock where stock<0 and sloc='Main')
    rollback
else
    commit