IF COL_LENGTH('dbo.asr_loc_mast', 'area_id') IS NULL
BEGIN
    ALTER TABLE [dbo].[asr_loc_mast]
        ADD [area_id] INT NULL;
END;

IF COL_LENGTH('dbo.asr_loc_mast', 'loc_alias') IS NULL
BEGIN
    ALTER TABLE [dbo].[asr_loc_mast]
        ADD [loc_alias] NVARCHAR(32) NULL;
END;

EXEC sys.sp_executesql N'
UPDATE [dbo].[asr_loc_mast]
SET [area_id] =
    CASE
        WHEN [crn_no] BETWEEN 1 AND 6 THEN 1
        WHEN [crn_no] BETWEEN 7 AND 18 THEN 2
        WHEN [crn_no] BETWEEN 19 AND 28 THEN 3
        ELSE [area_id]
    END
WHERE [area_id] IS NULL
  AND [crn_no] BETWEEN 1 AND 28;
';

EXEC sys.sp_executesql N'
;WITH area_min AS (
    SELECT [area_id], MIN([row1]) AS [min_row]
    FROM [dbo].[asr_loc_mast]
    WHERE [area_id] IN (1, 2, 3)
      AND [row1] IS NOT NULL
    GROUP BY [area_id]
)
UPDATE lm
SET [loc_alias] =
    CASE lm.[area_id]
        WHEN 1 THEN N''A-''
        WHEN 2 THEN N''B-''
        WHEN 3 THEN N''C-''
    END
    + RIGHT(''00'' + CAST(lm.[row1] - area_min.[min_row] + 1 AS VARCHAR(10)), 2)
    + RIGHT(''000'' + CAST(lm.[bay1] AS VARCHAR(10)), 3)
    + RIGHT(''00'' + CAST(lm.[lev1] AS VARCHAR(10)), 2)
FROM [dbo].[asr_loc_mast] lm
INNER JOIN area_min ON lm.[area_id] = area_min.[area_id]
WHERE lm.[area_id] IN (1, 2, 3)
  AND lm.[row1] IS NOT NULL
  AND lm.[bay1] IS NOT NULL
  AND lm.[lev1] IS NOT NULL;
';