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;
|
';
|
