SQL Select When

Question

I need to find out the final profit based on the fact that some items have discount, but the discount is set between a date interval and only then is applied on a set of items. What's wrong here?

use Companie

select sum((Facturi_Emise.Cantitate)*C.Pret+(C.Pret*B.Adaos)/100)
when (Facturi_Emise.Data_Emitere BETWEEN A.Data_Start and A.Data_stop)

select sum(((Facturi_Emise.Cantitate)*C.Pret+(C.Pret*B.Adaos)/100)-
(C.Pret*A.Discount)/100)

from Facturi_Emise

LEFT JOIN

(Select Data_Start,Data_Stop,Id_Discount,Discount from Discount)as A

on Facturi_Emise.Id_Discount=A.Id_Discount

LEFT JOIN

(select Id_Produs,Adaos from Produse)as B

 on Facturi_Emise.Id_Produs=B.Id_Produs

LEFT JOIN

(Select Id_produs,Pret from Livrari)as C

on Produse.Id_Produs=C.Id_Produs

If I put Where it says that some of the columns are not bound. But in essence, Yes. That would be the condition, so I thought of. — Andrei-Paul, Jul 10 '14 at 11:29
I tried with If condition then select first sum...else select second sum...and still nothing. I even got some errors saying that Incorrect syntax near the keyword — Andrei-Paul, Jul 10 '14 at 11:33
how does your query with if looks like. In select it should be IF(condition, if_condition is true, if_condition_is_false). — Bojan Kovacevic, Jul 10 '14 at 11:37

Guffa · Accepted Answer · 2014-07-10T11:43:04.440

You can't use when by itself, it's a part of a case expression, which you incidentally can use to get the sum of a selecton of the records:

select
  sum(case
    when Facturi_Emise.Data_Emitere BETWEEN A.Data_Start and A.Data_stop then
      C.Pret * (Facturi_Emise.Cantitate + B.Adaos / 100)
    else
      0
  end) as RegularPrice,
  sum(case
    when Facturi_Emise.Data_Emitere BETWEEN A.Data_Start and A.Data_stop then
      0
    else
      C.Pret * (Facturi_Emise.Cantitate + (B.Adaos - A.Discount) / 100)
  end) as DiscountPrice
from ...

SQL Select When

1 Answers1