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.
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 |
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
