IF COL_LENGTH('dbo.asr_loc_mast', 'area_id') IS NULL
|
BEGIN
|
ALTER TABLE [dbo].[asr_loc_mast]
|
ADD [area_id] INT 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;
|
';
|
|
IF COL_LENGTH('dbo.asr_wrk_mast', 'area_id') IS NULL
|
BEGIN
|
ALTER TABLE [dbo].[asr_wrk_mast]
|
ADD [area_id] INT NULL;
|
END;
|
|
EXEC sys.sp_executesql N'
|
UPDATE m
|
SET area_id = CASE
|
WHEN m.io_type >= 100 THEN COALESCE(source_loc.area_id, target_loc.area_id)
|
ELSE COALESCE(target_loc.area_id, source_loc.area_id)
|
END
|
FROM dbo.asr_wrk_mast m
|
LEFT JOIN dbo.asr_loc_mast target_loc ON target_loc.loc_no = m.loc_no
|
LEFT JOIN dbo.asr_loc_mast source_loc ON source_loc.loc_no = m.source_loc_no
|
WHERE m.area_id IS NULL
|
AND CASE
|
WHEN m.io_type >= 100 THEN COALESCE(source_loc.area_id, target_loc.area_id)
|
ELSE COALESCE(target_loc.area_id, source_loc.area_id)
|
END IS NOT NULL;
|
';
|
|
IF OBJECT_ID('dbo.asr_wrk_mast_log', 'U') IS NOT NULL
|
BEGIN
|
IF COL_LENGTH('dbo.asr_wrk_mast_log', 'area_id') IS NULL
|
BEGIN
|
ALTER TABLE [dbo].[asr_wrk_mast_log]
|
ADD [area_id] INT NULL;
|
END;
|
|
EXEC sys.sp_executesql N'
|
UPDATE m
|
SET area_id = CASE
|
WHEN m.io_type >= 100 THEN COALESCE(source_loc.area_id, target_loc.area_id)
|
ELSE COALESCE(target_loc.area_id, source_loc.area_id)
|
END
|
FROM dbo.asr_wrk_mast_log m
|
LEFT JOIN dbo.asr_loc_mast target_loc ON target_loc.loc_no = m.loc_no
|
LEFT JOIN dbo.asr_loc_mast source_loc ON source_loc.loc_no = m.source_loc_no
|
WHERE m.area_id IS NULL
|
AND CASE
|
WHEN m.io_type >= 100 THEN COALESCE(source_loc.area_id, target_loc.area_id)
|
ELSE COALESCE(target_loc.area_id, source_loc.area_id)
|
END IS NOT NULL;
|
';
|
END;
|
