OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Date Calculations

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
mshowler
Newbie
Newbie


Joined: 22 Jun 2011
Posts: 1

PostPosted: Wed Jun 22, 2011 11:43 pm    Post subject: Date Calculations Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Jun 23, 2011 2:33 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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