Thursday, 26 January 2017

How to use CASE Statement in Update Table Statement in SQL Server

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

                    AND [Channel Type]='Retail')THEN 'UKI_Retail'

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

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts