MS SQL Server CASE Statement usage in Update Table Statement
USE TAMATAM
GO--Truncating and updating the data into Main table from multiple Sub tables :
TRUNCATE Table Tbl_Sales_By_Regions_Final
INSERT INTO Tbl_Sales_By_Regions_Final
SELECT * from (
SELECT * from Tbl_Sales_Region_North
UNION ALL
SELECT*from Tbl_Sales_Region_East
UNION ALL
SELECT*from Tbl_Sales_Region_South
)AS T3
--Update Statement with usage of Case Statement to update a Table Field:
Update Tbl_Sales_By_Regions_Final SET [Slaes_Region]=
CASE
WHEN ([Slaes_Region] IN ('INDIA','USA','UK') AND [Channel Type]='Global') THEN [Slaes_Region]
ELSE
CASE
WHEN ([Slaes_Region] IN ('UKI_North','UKI_East','UKI_South')
USE TAMATAM
GO--Truncating and updating the data into Main table from multiple Sub tables :
TRUNCATE Table Tbl_Sales_By_Regions_Final
INSERT INTO Tbl_Sales_By_Regions_Final
SELECT * from (
SELECT * from Tbl_Sales_Region_North
UNION ALL
SELECT*from Tbl_Sales_Region_East
UNION ALL
SELECT*from Tbl_Sales_Region_South
)AS T3
--Update Statement with usage of Case Statement to update a Table Field:
Update Tbl_Sales_By_Regions_Final SET [Slaes_Region]=
CASE
WHEN ([Slaes_Region] IN ('INDIA','USA','UK') AND [Channel Type]='Global') THEN [Slaes_Region]
ELSE
CASE
WHEN ([Slaes_Region] IN ('UKI_North','UKI_East','UKI_South')
AND [Channel Type]='Retail')THEN 'UKI_Retail'
WHEN ([Slaes_Region] IN ('UKI_North','UKI_East','UKI_South')
WHEN ([Slaes_Region] IN ('UKI_North','UKI_East','UKI_South')
AND [Channel Type]='Distri')THEN 'UKI_Distri'
ELSE 'Global_Region'
END
END
--An Update Statement with usage of Case Statement to update a Variable :
Declare @Cur_Wk as Varchar(50)=NULL,@Prev_FY_Wk as Varchar(50)=NULL,@Cur_FY AS INT=NULL;
SET @Cur_FY = (Select Max([Fiscal Year]) as Cur_FY From [Tbl_Calendar] )
SET @Cur_Wk = (Select Max(Week_Id) From [Tbl_Calendar] )
WHERE [Fiscal Year]=@Cur_FY)
Set @Prev_FY_Wk=CAST((LEFT(@Cur_Wk,4)-1) AS VARCHAR(20))+CAST(RIGHT(@Cur_Wk,LEN(@Cur_Wk)-4)AS VARCHAR(20))
SET @Prev_FY_Wk =
ELSE 'Global_Region'
END
END
--An Update Statement with usage of Case Statement to update a Variable :
Declare @Cur_Wk as Varchar(50)=NULL,@Prev_FY_Wk as Varchar(50)=NULL,@Cur_FY AS INT=NULL;
SET @Cur_FY = (Select Max([Fiscal Year]) as Cur_FY From [Tbl_Calendar] )
SET @Cur_Wk = (Select Max(Week_Id) From [Tbl_Calendar] )
WHERE [Fiscal Year]=@Cur_FY)
Set @Prev_FY_Wk=CAST((LEFT(@Cur_Wk,4)-1) AS VARCHAR(20))+CAST(RIGHT(@Cur_Wk,LEN(@Cur_Wk)-4)AS VARCHAR(20))
SET @Prev_FY_Wk =
CASE
WHEN Left(@Prev_FY_Wk,6) IN ('201607','201608','201609') THEN CAST((@Prev_FY_Wk+1) AS INT)
ELSE
CAST(@Prev_FY_Wk AS INT)
END;
WHEN Left(@Prev_FY_Wk,6) IN ('201607','201608','201609') THEN CAST((@Prev_FY_Wk+1) AS INT)
ELSE
CAST(@Prev_FY_Wk AS INT)
END;
-------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.