T-SQL Query a matrix table for free position

Question

I'm trying to build a query for a matrix table which has a schema like this:

X   | Y   | Z   | Disabled   | OccupiedId |
--------------------------------------------
1     1     1       0            NULL
1     2     1       0            NULL
1     3     1       1            NULL
1     4     1       0               1
1     5     1       0               2
1     6     1       0               3
1     7     1       0               4
1     1     2       0            NULL
1     2     2       0            NULL
1     3     2       0            NULL
1     4     2       0            NULL
1     5     2       0            NULL
1     6     2       0            NULL
1     7     2       0            NULL

I want to group for X, Z and find the first available position on Y. Available by all means is NOT Disabled and NOT Occupied.

In the example provided this query should return:

X   | Z   | FreeY
--------------------------------------------
 1     1     2
 1     2     7

The query should select the first free Y (or the last occupied Y) considering that each (X, Z) are filled starting from the end (MAX Y is constant)

I've tried different approach unsuccessfully :( Any suggestions is highly appreciated! Kind Regards, D.

Answer 1

SQL fiddle

 CREATE TABLE Coordinate
(  X int, Y int,Z int, Disabled bit, OccupiedId int)

INSERT INTO Coordinate VALUES (1,1,1, 1, NULL)
INSERT INTO Coordinate VALUES (1,1,2, 0, NULL)
INSERT INTO Coordinate VALUES (1,1,3, 0, NULL)
INSERT INTO Coordinate VALUES (1,1,4, 0, NULL)
INSERT INTO Coordinate VALUES (1,2,1, 0, NULL)
INSERT INTO Coordinate VALUES (1,2,2, 0, NULL)
INSERT INTO Coordinate VALUES (1,2,3, 0, 123)
INSERT INTO Coordinate VALUES (1,2,4, 0, NULL)
INSERT INTO Coordinate VALUES (1,2,5, 1, NULL)

SELECT X, Z, MIN(Y) AS FirstFreePosition
FROM Coordinate
WHERE Disabled = 0 AND OccupiedId IS NULL
GROUP BY X, Z

OR -- if you need the unavailable combinations too, then something like this:

SELECT X, Z, MIN(CASE 
                 WHEN Disabled = 1 OR OccupiedId IS NOT NULL 
                 THEN 1000 --a big number
                 ELSE Y END) AS FirstFreePosition
FROM Coordinate
GROUP BY X, Z

Answer 2

For your edit (disabled=bit column), this query shows lastOccupiedID as well as firstFreeY

  select x, z,
         max(case when disabled=1 or occupiedid is not null
             then Y else 0 end) lastOccupiedPosition,
         maX(case when disabled=0 AND occupiedid is null
             then Y else 0 end) firstFreeY
    from matrix
group by x, z
order by x, z;

---

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table matrix(
X int  , Y int  , Z int  , Disabled varchar(5)  , OccupiedId int );
insert matrix values
(1    , 1   , 1   , 'True'       , NULL      ),
(1    , 1   , 2   , 'False'      , NULL      ),
(1    , 1   , 3   , 'False'      , NULL      ),
(1    , 1   , 4   , 'False'      , NULL      ),
(1    , 2   , 1   , 'False'      , NULL      ),
(1    , 2   , 2   , 'False'      , NULL      ),
(1    , 2   , 3   , 'False'      , 123       ),
(1    , 2   , 4   , 'False'      , NULL      );

Query 1:

  select x, z,
         max(case when disabled='true' or occupiedid is not null
             then Y else 0 end) lastOccupiedPosition
    from matrix
group by x, z
order by x, z

Results:

| X | Z | LASTOCCUPIEDPOSITION |
--------------------------------
| 1 | 1 |                    1 |
| 1 | 2 |                    0 |
| 1 | 3 |                    2 |
| 1 | 4 |                    0 |