CODEWITHSUNDEEP
sqlsql-servert-sql
Anna
Anna

Reputation: 464

Declare a list of countries and render values depending on the country

I have two questions. I have a large table for a marketing campaign. I want to create a table that renders tariff numbers for Buyer Countries. AS I have large and quarterly changing lists of countries, I want to put them into a declare / Set statement. How do you have to use string values in the Declare statement? Is there a better way? I tried all sorts of things, with commas , single quotes, no quotes in this list: ( ' ”AU” , ”GB” , ”NZ” ').

Is there a better way while using more Dynamic SQL in a Select statement? This is what I tried so far:

DECLARE @tariff_1  varchar(100)
SET @tariff_1 = ( ' ”AU” , ”GB” ,  ”NZ” ')      
DECLARE @tariff_2  varchar(100)
SET @tariff_2 = ( '"GB" , "US" , "GB" ')
DECLARE @tariff_3  varchar(100)
SET @tariff_3 = ( '"CH" , "US" , "JE" ')    

SELECT
         a.[Product_1] 
        ,a.[Product_2] 
        ,a.[Product_3] 
        ,a.[Product_4] 
        ,a.[Product_5] 
    
        ,CASE WHEN a.[Product_1]  IN (@tariff_1)  THEN '3'    ELSE '999' END AS 'Buyer_country1'
        ,CASE WHEN a.[Product_2]  IN (@tariff_2)  THEN '3.5'   ELSE '999' END AS 'Buyer_country2'
        ,CASE WHEN a.[Product_3]  IN (@tariff_2)  THEN '4'      ELSE '999' END AS 'Buyer_country3'
            FROM  [IDW_stage_DEV].[dbo].[table] a

The table looks as below:

Product_1 Product_2 Product_3 Product_4 Product_5 Buyer_country1 Buyer_country2
-------------------------------------------------------------------------------
AU        GB        NULL      NULL      NULL      999            999
GB        US        JE        NULL      NULL      999            999
AU        GB        US        NULL      NULL      999            999

Additionally, and dependent on the result above, I also need to create a variable, that would give me the country with the highest tariff value.

Can you do that without breaking up the select query? How would that work?

Upvotes: 0

Views: 206

Answers (1)

Dale K
Dale K

Reputation: 27249

Use a table varaible:

DECLARE @tariff_1 TABLE (Code VARCHAR(100));
INSERT INTO @tariff_1 (Code) VALUES ('AU'), ('GB'), ('NZ');     
DECLARE @tariff_2 TABLE (Code VARCHAR(100));
INSERT INTO @tariff_2 (Code) VALUES ('GB'), ('US'), ('GB');
DECLARE @tariff_3 TABLE (Code VARCHAR(100));
INSERT INTO @tariff_3 (Code) VALUES ('CH'), ('US'), ('JE'); 

SELECT
    a.[Product_1] 
    , a.[Product_2] 
    , a.[Product_3] 
    , a.[Product_4] 
    , a.[Product_5] 

    , CASE WHEN a.[Product_1] IN (SELECT Code from @tariff_1) THEN '3' ELSE '999' END AS 'Buyer_country1'
    , CASE WHEN a.[Product_2] IN (SELECT Code from @tariff_2) THEN '3.5' ELSE '999' END AS 'Buyer_country2'
    , CASE WHEN a.[Product_3] IN (SELECT Code from @tariff_2) THEN '4' ELSE '999' END AS 'Buyer_country3'
FROM [IDW_stage_DEV].[dbo].[table] a

Upvotes: 3

Related Questions