pape pape - 5 months ago 9
SQL Question

SQL - if or something else

When I execute a SQL query I get next:

select Item.No_, Entry.Quantity, MinMax.MaxQuantity, Entry.Location, Item.MainLocation
FROM Item
join Entry
on Item.No_ = Entry.[Item No_]
join MinMax
on Item.No_ = MinMax.Item No_


I want if Quantity from MainLocation is 30, to full in another location Quantity to MaxQuantity. That means

MainLocation:A1 has 30 Quantity,

but Location

A2 has 2

A3 has 12

A4 has 1

I want to full quantity to MaxQuantity, to take from A1 and I give A2, A3, A4 and reduce A1 .

I want next to get:

Answer
;with InitialQuery as 
(
    select  Item.No_, Entry.Quantity, MinMax.MaxQuantity, Entry.Location, Item.MainLocation
    from Item
    join Entry on Item.No_ = Entry.[Item No_]
    join MinMax on Item.No_ = MinMax.Item No_
)
, Sources as
(
    select * from InitialQuery
    where Location=MainLocation and Quantity=30
)
, Destinations as 
(
    select 
        i.*,
        i.MaxQuantity - i.Quantity 'Needed'
    from Sources s
    join InitialQuery i on i.No_=s.No_ and i.MainLocation=s.MainLocation and i.Location<>s.Location
)
select 
    s.No_,
    (s.Quantity - d.NoTransferred) 'Quantity',
    s.MaxQuantity,
    s.Location,
    s.MainLocation
from Sources s
join (
        select No_, MainLocation, sum(Needed) 'NoTransferred' from Destinations group by No_, MainLocation
    ) d on d.No_=s.No_ and d.MainLocation=s.MainLocation
union all
select 
    No_,
    MaxQuantity 'Quantity',
    MaxQuantity,
    Location,
    MainLocation
from Destinations
Comments