[Solved] SQL Server inner Join with the same table

by

[ad_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

5

[ad_2]

solved SQL Server inner Join with the same table