CODEWITHSUNDEEP
c#sqlsql-server
CodeSniper
CodeSniper

Reputation: 493

Calculate discount price of net amount in sql server?

Hi guys i got stuck at very simple step i want to calculate Discounted price as net amount in sql server. I used the following sql statement:

SELECT ITEM_MODEL,
       ITEM_NAME,
       ITEM_DESCRIPTION,
       QUANTITY,
       RATE=RATE*(Discount/100),
       AMOUNT,
       DATE,
       Discount 
       FROM ITEM_DETAILS 
       ORDER BY ITEM_MODEL

1.It works fine but it has a problem because there will be no discount on every product so when there is no discount price i contains null and then null multiply with RATE and makes it null.What would be safe way of calculating discounted price in a database.

2.The condition should be there that when the Discount price is zero(0) the rate will be same as it is but when there is a value in discount column for the given product it should calculate it. I hope it should be clear to all.

Any help would be greatly praised?

Upvotes: 0

Views: 6744

Answers (4)

enapi
enapi

Reputation: 728

Use ISNULL function

SELECT ITEM_MODEL,
   ITEM_NAME,
   ITEM_DESCRIPTION,
   QUANTITY,
   RATE=RATE*(ISNULL(Discount, 100)/100),
   AMOUNT,
   DATE,
   Discount 
   FROM ITEM_DETAILS 
   ORDER BY ITEM_MODEL

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

A simple CASE should do;

SELECT ITEM_MODEL,
       ITEM_NAME,
       ITEM_DESCRIPTION,
       QUANTITY,
       RATE=CASE WHEN Discount IS NULL OR Discount=0 
            THEN RATE
            ELSE RATE*(Discount/100) END,
       AMOUNT,
       DATE,
       Discount 
       FROM ITEM_DETAILS 
       ORDER BY ITEM_MODEL

The case will basically check if discount is null or zero, and in that case use RATE as is, otherwise do the normal calculation.

Upvotes: 1

otaku
otaku

Reputation: 964

In SQL Server you can use the CASE condition as below:

CASE WHEN discount > 0 then 'Value1' ELSE '0' END

So the result will be as follow:

SELECT ITEM_MODEL,
       ITEM_NAME,
       ITEM_DESCRIPTION,
       QUANTITY,
       (CASE WHEN Discount > 0 THEN RATE*(Discount/100) ELSE 0 END) AS MyRate
       AMOUNT,
       DATE,
       Discount 
       FROM ITEM_DETAILS 
       ORDER BY ITEM_MODEL

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

Sounds like Case with Colaesce will solve your issues if I'm understanding your question correctly:

SELECT ITEM_MODEL,
       ITEM_NAME,
       ITEM_DESCRIPTION,
       QUANTITY,
       RATE=RATE*(case when coalesce(discount,0) = 0 then 1 else Discount/100 end),
       AMOUNT,
       DATE,
       Discount 
FROM ITEM_DETAILS 
ORDER BY ITEM_MODEL

Upvotes: 2

Related Questions