🚀 go-pugleaf

Hs wrote:
> This is my table:
> 
> CREATE TABLE "BUY" 
> (
>   "CUSTOMER_ID" INTEGRER,
>   "WEIGHT" 	INTEGER,
>   "BUY_DATE"	DATE
> );
> 
> I want: SELECT "CUSTOMER_ID", SUM("WEIGHT" * CostA or CostB) TOTAL_COST 
>             FROM BUY GROUP BY "CUSTOMER_ID";
> 
> The problem: I should use CostA when "BUY_DATE" is before 2000/10/10 and CostB when it is after 2000/10/10.
> can I do it in one query? what is the best other way?
> 
> Thanks.

I think you have not normalized/designed the table properly. If the cost 
can change, it should be tracked at "purchase time".

Purchase time can mean many things... Unfortunately it is not even that 
easy as you may have discussed one price with a salesman, been quoted a 
different price by an assistant, and received it (or been invoiced at) 
at a different price. You might even be dealing with currency exchange.

I suggest that you rethink the problem if you don't want to have a query 
with many dates some time in the future. Alternatively you can use a 
stored procedure or a view plus a query... But why not solve the 
underlying problem?



-- 
Will R
PMC Consulting