How to query

Thread View: borland.public.interbase.sql

4 messages

[T] Back to Threads [V] Tree View [G] Group Articles

4 total messages Started by Hs Sat, 05 Jul 2008 13:44

#174

Author: Hs
Date: Sat, 05 Jul 2008 13:44

17 lines
1093 bytes

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.

Re: How to query

#175

Author: willr
Date: Sat, 05 Jul 2008 18:41

36 lines
1999 bytes

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

Re: How to query

#176

Author: Bill Todd [TeamB]
Date: Sat, 05 Jul 2008 19:12

12 lines
1334 bytes

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)

Re: How to query

#177

Author: Craig Stuntz TeamB
Date: Tue, 08 Jul 2008 13:19

8 lines
1495 bytes

	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

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads