"Check capacity availability in spaces" with SQL Server

Question

I have a warehouse which has squared spaces in racks to put my products which are of the same size and are in boxes.

There are spaces in racks that can vary in size and I can put a maximum of X boxed products. The size of the space can be variable. For that reason I have table Space where I have a column MaximumCapacity.

I am trying to map these spaces in a system to know if there's still capacity availability and address quickly the rack where my space can hold another boxed product. This could be represent the hierarchy of warehouse

+ Warehouse (1 warehouse)
  + Racks (5 000 racks)
     + Spaces (10 000 spaces)
         + Products (1 to N per space)

I've been trying to do a SQL Server 2008 view to get my spaces vs products.

I have a Space table with a column MaximumCapacity to specify how many products can be hold in there. I have another table called ProductLocation.

I am doing a left join to retrieve first my spaces and all its assignments or locations with products.

SELECT 
    s.ID, s.Code,
    s.MaximumCapacity,
    pl.ProductCode
FROM 
    Space s
LEFT JOIN 
    ProductLocation pl ON s.ID = pl.SpaceID

This is returning something like this

Code    - Maximum capacity - ProductCode
-----------------------------------------
SPACE 1 -         4        - PRODUCT1
SPACE 1 -         4        - PRODUCT2
SPACE 2 -         8        - PRODUCT3
SPACE 3 -         2        - PRODUCT4
SPACE 3 -         2        - PRODUCT5

This data will be rearranged in a better way in C#, but the point is, in order to avoid network traffic for retrieving spaces with no more room for a product, I just want to retrieve records with available capacity. I've been trying to figure out without success. In the example of results the maximum capacity of SPACE3 is 2 and has already 2 products there (PRODUCT4 and PRODUCT5).

How can I filter (WHERE) to avoid these 2 last rows based on the maximum capacity of the space to get finally this:

Code    - Maximum capacity - ProductCode
-----------------------------------------
SPACE 1 -         4        - PRODUCT1
SPACE 1 -         4        - PRODUCT2
SPACE 2 -         8        - PRODUCT3

I have a performance problem to retrieve all the unnecessary spaces with no more room.

Thanks in advance.

Answer 1

Please try this.

declare @Space table (ID int, Code varchar(20), MaximumCapacity int)
declare @ProductLocation table(SpaceID int, ProductCode varchar(20))

insert into @Space values
(1, 'Space 1', 4)
,(2, 'Space 2', 8)
,(3, 'Space 3', 2)
insert into @ProductLocation values
(1, 'Product1')
,(1, 'Product2')
,(2, 'Product3')
,(3, 'Product4')
,(3, 'Product5');

Count the number of product per space and exclude those who is less than the maximum.

with cte as(
SELECT s.ID,
  s.Code,
  s.MaximumCapacity,
  pl.ProductCode,
  count(pl.SpaceID) over(partition by pl.spaceid) as ProductCount
FROM @Space s
LEFT JOIN @ProductLocation pl ON s.ID = pl.SpaceID

)select ID, Code, MaximumCapacity, ProductCode from cte
Where ProductCount<MaximumCapacity