Update a table variable without duplicating data in T-SQL

user_13142173 0
Viewed 66

I have 2 table variables @Items and @Locations. Both tables contain a volume column for items and locations.

I am trying to retrieve locations with the available volume capacity per item. I would like the location to be excluded from the results if it has no more volume capacity to hold any other item. The goal is to update @Items with available locations based on the volume and ignore the location if it can't store any more items.

Here is the T-SQL that I have written for this scenario:

DECLARE @Items TABLE
(
    Id INT,
    Reference NVARCHAR(255),
    Volume DECIMAL(18, 4),
    IdLocation INT
)

DECLARE @Locations TABLE
(
    Id INT,
    Reference NVARCHAR(255),
    Volume DECIMAL(18, 4)
)

INSERT INTO @Locations (Id, Reference, Volume)
    SELECT 100, 'Location 1', 50000
    UNION SELECT 101, 'Location 2', 100000
    UNION SELECT 102, 'Location 3', 300000

INSERT INTO @Items (Id, Reference, Volume)
    SELECT 1, 'Item 1', 50000
    UNION SELECT 2, 'Item 2', 50000
    UNION SELECT 3, 'Item 3', 100000
    UNION SELECT 4, 'Item 4', 100000
    UNION SELECT 5, 'Item 5', 300000
    UNION SELECT 6, 'Item 6', 300000
    UNION SELECT 7, 'Item 7', 300000

UPDATE I
SET I.IdLocation = (SELECT TOP 1 L.Id 
                    FROM @Locations L
                    WHERE L.Volume >= I.Volume)
FROM @Items I

SELECT *
FROM @Items

The results which I get:

NotExpectedResults

The results which I expect to get:

ExpectedResults

If anyone has a solution to this problem I would be very grateful.

temp-tables tsql sql-server sql
1 Answers

There is probably a clever set-based way to do this involving window functions and a running total of allocated volume. Personally when having to allocate stock like this I often find myself resorting to a loop e.g.

declare @Count int;

-- How many items are there to update?
select @Count = count(*) from @Items where IdLocation is null

while exists (select 1 from @Items where IdLocation is null) begin
  UPDATE I SET
    I.IdLocation = (
      SELECT TOP 1 L.Id 
      FROM @Locations L
      WHERE (L.Volume - coalesce((select sum(I1.Volume) from @Items I1 where I1.IdLocation = L.id),0)) >= I.Volume
    )
  FROM @Items I
  where I.id in (select top 1 id from @Items where IdLocation is null);

  -- Did we update any items? If not exit the loop as we have allocated all space.
  if @Count = (select count(*) from @Items where IdLocation is null) break;

  -- If so, update the new count for the next loop
  select @Count = count(*) from @Items where IdLocation is null;
end

SELECT *
FROM @Items;
user_11274280
Related