[Solved] SQL Server inner Join with the same table

by


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

5

solved SQL Server inner Join with the same table