Case Expression Returns Wrong Values During Percentage Calculation

I am currently working on a supermarket database design where I have to have retrieve the price of the product after discount. There are 2 types of discount: Direct Discount, Quan

Solution 1:

I see multiple issues with your CASE statement.

First, I think you are misusing the % wildcard. It represents any number of any characters at the place it is located. For example, '%DD' means anything as long as the last two characters are 'DD'. Adding the NOT just flips it. Therefore, Product.discount_cd NOT LIKE '%DD' will evaluate true as long as the Product.discount_cd does not end in 'DD', which is all of the codes. When you add in the AND discount_percentage IS NOT NULL, the result is, any record that has a discount_percentage will return with the first THEN statement. This includes P001 and P005.

Second, I think you are misusing the ISNULL() function. It's unnecessary to use it on discount_percentageas you have IS NOT NULL as a criterion. Also, if quantity refers to the product quantity, I do not think you should return a 1 when quantity is null.

Presuming that "DD" stands for "Direct Discount", I don't understand why you have records with a "DD" code and no discount_percentage. That being said, I think the below revised CASE statement will meet your needs.

CASEWHEN
        Product.discount_cd LIKE'DD%'--Any code beginning with 'DD'AND discount_percentage ISNOTNULL--Must have discount percentageTHEN (unitPrice * ISNULL(quantity,0)) - (unitPrice * ISNULL(quantity,0) * discount_percentage)
    WHEN
        Promotion_Type.discount_cd LIKE'QB%'--Any code beginning with 'QB'AND discount_amount ISNOTNULL--Must have discount amountTHEN unitPrice * ISNULL(quantity,0) - discount_amount
    ELSE0--No valid discountENDAS reduce

Solution 2:

Check this first condition,

casewhen Product.discount_cd NOTLIKE'%DD'AND discount_percentange ISNOTNULLTHEN (unitPrice*ISNULL(discount_percentange,1))*ISNULL(quantity,1)

The unit discount amount should be deducted from unitPrice.

casewhen Product.discount_cd NOTLIKE'%DD'AND discount_percentange ISNOTNULLTHEN (unitPrice-(unitPrice*ISNULL(discount_percentange,1)))*ISNULL(quantity,1)