CODEWITHSUNDEEP
sql-serversql-server-2008case
Cladback
Cladback

Reputation: 157

Case Statement On Two Conditions

Can i add these both together to work as one, the user can select valid for country 1 or 2 and BOTH so i need these to work together if possible and when user selects both i need it to show both measurements

I CANNOT CREATE A NEW TABLE so i need essentially column that will populate based on what country and unit price base will be sufficient

,CASE 
    WHEN bd.Validforcountry = 1 and BD.UnitpriceBase = 1
        THEN '100 ml'
    WHEN bd.Validforcountry = 1 and BD.UnitpriceBase = 2
        THEN '1 l'
    WHEN bd.Validforcountry = 1 and BD.UnitpriceBase = 3
        THEN '100 g'
    WHEN bd.Validforcountry = 1 and  BD.UnitpriceBase = 4
        THEN '1 Kg'
    WHEN bd.Validforcountry = 1 and BD.UnitpriceBase = 5
        THEN '750 ml'
    ELSE BD.UnitpriceBase
    END AS 'Unit price Declaration'


,CASE 
    WHEN bd.Validforcountry = 2 and BD.UnitpriceBase = 1
        THEN '100 ml'
    WHEN bd.Validforcountry = 2 and BD.UnitpriceBase = 2
        THEN '1 l'
    WHEN bd.Validforcountry = 2 and BD.UnitpriceBase = 3
        THEN '100 g'
    WHEN bd.Validforcountry = 2 and  BD.UnitpriceBase = 4
        THEN '1 Kg'
    WHEN bd.Validforcountry = 2 and BD.UnitpriceBase = 5
        THEN '750 ml'
    ELSE BD.UnitpriceBase
    END AS 'Unit price Declaration'

Upvotes: 0

Views: 1161

Answers (1)

xlecoustillier
xlecoustillier

Reputation: 16351

Instead of creating complex CASE WHEN, just create a new table, so you can select the correct value with a simple JOIN. Something like this:

CREATE TABLE UnitPriceDeclaration
(
    Country int,
    UnitPriceBase int,
    UnitPriceDeclaration varchar(20),
    PRIMARY KEY (Country, UnitPriceBase)
);

This way, you can write your query like this (dummy joins, I don't know anything about your other tables):

SELECT t1.someField, 
       t2.someOtherField, 
       t1.UnitPriceBase, 
       t2.Country, 
       upd.UnitPriceDeclaration
FROM oneTable t1,
INNER JOIN anotherTable t2 ON ...
INNER JOIN UnitPriceDeclaration upd ON upd.Country = t2.Country AND upd.UnitPriceBase = t1.UnitPriceBase -- maybe LEFT JOIN
WHERE...

This allows you to keep flexibility and add/change as many countries, UPB or UPD as you want, without anything to change other than data. Using CASE WHEN as you do, you would have to change your code each time something changes (potentially in more than one place).

Your edit advocates for this solution. See all those repeated values ?

So let's say you want to display two UPD, one for UK and one for IRE. You just have to do something like this:

SELECT t1.someField, 
       t2.someOtherField, 
       t1.UnitPriceBase, 
       t2.Country, 
       upduk.UnitPriceDeclaration as UPD_UK,
       updire.UnitPriceDeclaration as UPD_IRE
FROM oneTable t1,
INNER JOIN UnitPriceDeclaration upduk ON upd.UnitPriceBase = t1.UnitPriceBase AND upd.Country = 1
INNER JOIN UnitPriceDeclaration updire ON upd.UnitPriceBase = t1.UnitPriceBase AND upd.Country = 2
WHERE...

Upvotes: 1

Related Questions