Thursday, January 26, 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 ; 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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog