How to query

[Hs]

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.

[willr]

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

[Bill Todd [TeamB]]

Hs wrote:

> can I do it in one query?

No. If the cost is dependent on the BUY_DATE it should be stored in the
BUY table. Since it is not the best solution is probably a stored
procedure. I assume the costs and their effective dates are stored in
another table in the database so the stored procedure could locate the
price for each record as it is being processed.

--
Bill Todd (TeamB)

[Craig Stuntz [TeamB]]

I think this probably could be done with CASE, but I'm in agreement
with Bill that the real issue here is a metadata design problem.

--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt
your DBs! Open Edition users, get 6.0.1.6 from http://mers.com