return value of stored procedure based on different rules

Question

I am writing a stored procedure to get the values based on the following tables.

I am filling an apartment flats according to the nationality of the buyers.

The stored procedure has to return next non-occupied flat based on the following rules:

• If a new entry comes, I need to suggest the non-occupied flat which is next to the occupied-flat having the same nationality of new person

• If no match found for above condition, then allocate first flat in a floor where no flats were occupied

• If no match found for above condition, then allocate a flat which is having at-least two empty flats on both sides

• If no match found for above condition, then allocate the first flat which is empty according to the sort order

NOTE:

• Each flat is identified by combination of Floor & Flat No

• Sort order for flat and floors while searching should be from 1 To n

SAMPLE INPUT: NAME: RANDY NATIONALITY: BRAZIL

SAMPLE OUTPUT: FLOOR:1 FLAT NO: 4 (w.r.t. to the attached image)

Answer 1

The key is to create columns for each of your criteria, i.e. one column for if the next door flat owner has the same nationality, a column for if the floor is empty.

You can then take all your criteria and place them within the order by of a ROW_NUMBER() function to get the flats in the order you defined. The key part in the below query is this:

RowNumber = ROW_NUMBER() OVER(ORDER BY PrevIsNationalityMatch DESC, 
                                        NextIsNationalityMatch DESC, 
                                        EmptyFloor DESC, 
                                        EmptyFlatsEitherSide DESC,
                                        Floor, 
                                        FlatNo)

The four columns (PrevIsNationalityMatch, NextIsNationalityMatch, EmptyFloor', 'EmptyFlatsEitherSide), are all bit fields, so if a row exists where the previous flat is owned by someone of the same nationality this will always be ranked one by the ROW_NUMBER function, otherwise it looks for if the next flat is owned by someone of the same nationality (I added this rule as it seemed logical but it could easily be removed by removing it from the order by), and so on and so on until it is left just sorting by floor and flat no.

DECLARE @NewOwnerNationality VARCHAR(20) = 'BRAZIL';
WITH FlatOwnerNationality AS
(   SELECT  FlatMaster.Floor, 
            FlatMaster.FlatNo, 
            FlatMaster.IsOccupied,
            IsNationalityMatch = CASE WHEN OwnerMaster.OwnerNationality = @NewOwnerNationality THEN 1 ELSE 0 END
    FROM    FlatMaster
            LEFT JOIN OwnerMaster
                ON OwnerMaster.OwnerName = FlatMaster.OwnerName
), Flats AS
(   SELECT  FlatMaster.Floor,
            FlatMaster.FlatNo,
            FlatMaster.IsOccupied,
            EmptyFlatsEitherSide = CASE WHEN PrevFlat.IsOccupied = 'NO' AND NextFlat.IsOccupied  = 'NO' THEN 1 ELSE 0 END,
            EmptyFloor = CASE WHEN COUNT(CASE WHEN FlatMaster.IsOccupied = 'YES' THEN 1 END) OVER(PARTITION BY FlatMaster.Floor) = 0 THEN 1 ELSE 0 END,
            PrevIsNationalityMatch = ISNULL(PrevFlat.IsNationalityMatch, 0),
            NextIsNationalityMatch = ISNULL(NextFlat.IsNationalityMatch, 0)
    FROM    FlatMaster
            LEFT JOIN FlatOwnerNationality PrevFlat
                ON PrevFlat.Floor = FlatMaster.Floor
                AND PrevFlat.FlatNo = FlatMaster.FlatNo - 1
            LEFT JOIN FlatOwnerNationality NextFlat
                ON NextFlat.Floor = FlatMaster.Floor
                AND NextFlat.FlatNo = FlatMaster.FlatNo + 1
), RankedFlats AS
(   SELECT  *,
            RowNumber = ROW_NUMBER() OVER(ORDER BY PrevIsNationalityMatch DESC, 
                                                    NextIsNationalityMatch DESC, 
                                                    EmptyFloor DESC, 
                                                    EmptyFlatsEitherSide DESC,
                                                    Floor, 
                                                    FlatNo)
    FROM    Flats
    WHERE   IsOccupied = 'NO'
)
SELECT  Floor,
        FlatNo,
        MatchedOn = CASE WHEN PrevIsNationalityMatch = 1 THEN 'First Flat after same nationality owner'
                        WHEN NextIsNationalityMatch = 1 THEN 'First Flat before same nationality owner'
                        WHEN EmptyFloor = 1 THEN 'No Nationality Match, placed on empty floor'
                        WHEN EmptyFlatsEitherSide = 1 THEN 'Next flat with empty flats either side'
                        ELSE 'First Available Flat'
                    END
FROM    RankedFlats
WHERE   RowNumber = 1;

Brazil Example - Floor 1, Flat 4

England Example - Floor 1, Flat 2

Spain Example - Floor 2, Flat 1

EDIT

DECLARE @NewOwnerNationality VARCHAR(20) = 'BRAZIL';

WITH FlatOwnerNationality AS
(   SELECT  FlatMaster.Floor, 
            FlatMaster.FlatNo, 
            FlatMaster.IsOccupied,
            IsNationalityMatch = CASE WHEN OwnerMaster.OwnerNationality = @NewOwnerNationality THEN 1 ELSE 0 END
    FROM    FlatMaster
            LEFT JOIN OwnerMaster
                ON OwnerMaster.OwnerName = FlatMaster.OwnerName
), Flats AS
(   SELECT  FlatMaster.Floor,
            FlatMaster.FlatNo,
            FlatMaster.IsOccupied,
            EmptyFlatsEitherSide = CASE WHEN PrevFlat.IsOccupied = 'NO' AND NextFlat.IsOccupied  = 'NO' AND PrevFlat2.IsOccupied = 'NO' AND NextFlat2.IsOccupied  = 'NO' THEN 1 ELSE 0 END,
            EmptyFloor = CASE WHEN COUNT(CASE WHEN FlatMaster.IsOccupied = 'YES' THEN 1 END) OVER(PARTITION BY FlatMaster.Floor) = 0 THEN 1 ELSE 0 END,
            PrevIsNationalityMatch = ISNULL(PrevFlat.IsNationalityMatch, 0),
            NextIsNationalityMatch = ISNULL(NextFlat.IsNationalityMatch, 0)
    FROM    FlatMaster
            LEFT JOIN FlatOwnerNationality PrevFlat
                ON PrevFlat.Floor = FlatMaster.Floor
                AND PrevFlat.FlatNo = FlatMaster.FlatNo - 1
            LEFT JOIN FlatOwnerNationality NextFlat
                ON NextFlat.Floor = FlatMaster.Floor
                AND NextFlat.FlatNo = FlatMaster.FlatNo + 1
            LEFT JOIN FlatMaster PrevFlat2
                ON PrevFlat2.Floor = FlatMaster.Floor
                AND PrevFlat2.FlatNo = FlatMaster.FlatNo - 2
            LEFT JOIN FlatMaster NextFlat2
                ON NextFlat2.Floor = FlatMaster.Floor
                AND NextFlat2.FlatNo = FlatMaster.FlatNo + 2

), RankedFlats AS
(   SELECT  *,
            RowNumber = ROW_NUMBER() OVER(ORDER BY PrevIsNationalityMatch DESC, 
                                                    NextIsNationalityMatch DESC, 
                                                    EmptyFloor DESC, 
                                                    EmptyFlatsEitherSide DESC,
                                                    Floor, 
                                                    FlatNo)
    FROM    Flats
    WHERE   IsOccupied = 'NO'
)
SELECT  Floor,
        FlatNo,
        MatchedOn = CASE WHEN PrevIsNationalityMatch = 1 THEN 'First Flat after same nationality owner'
                        WHEN NextIsNationalityMatch = 1 THEN 'First Flat before same nationality owner'
                        WHEN EmptyFloor = 1 THEN 'No Nationality Match, placed on empty floor'
                        WHEN EmptyFlatsEitherSide = 1 THEN 'Next flat with empty flats either side'
                        ELSE 'First Available Flat'
                    END
FROM    RankedFlats
WHERE   RowNumber = 1;