| View previous topic :: View next topic |
| Author |
Message |
mshowler
Newbie
Joined: 22 Jun 2011
Posts: 1
|
 Posted: Wed Jun 22, 2011 11:43 pm Post subject: Date Calculations |
 |
|
Hi - I am fairly new to ooBase, so I am still learning a lot of its functions.
I have two tables, a client table, which has the clients name and a date, and a second table, the prices table, that has a range of date periods and prices.
I want to create a query, that displays the clients name, the date, and the appropriate price, taken from the prices table.
The problem I have is that I don't know how to query the prices table in order to generate the correct price.
Can somebody please advise - but I will need it explained very precisely!
Thanks in advance for your help. |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Jun 23, 2011 2:33 am Post subject: |
|
|
| Code: | SELECT "AID", SUM( "P" ) AS "P", MAX( "D" ) AS "LD"
FROM "Resale_Prices"
WHERE "D" <= :paramInvoiceDate
GROUP BY "AID" |
AID means Article ID
P is the price
D is the date with LD being the latest date being <= invoice date
There is a unique index on Date and Article ID, so MAX(Date) always refers to one particular article at one particular date and the SUM(price) sums up only one particular price.
The query returns all existing prices for every article at the given time.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
