I am using a spring / hibernate 3 stack.

I have 2 issues.... but they are somewhat related so I decided to throw them into 1 post:

Background:
I have the following table (or something similar)
GROUP_ID - PK
EFFECTIVE_DATE - PK
UPDATE_DATE -PK
TYPE - PK
AMOUNT

As you can see, it is a table with a composite key. I have all my domain objects mapped out properly with the table so there is no issue there.

1. Either through HQL or Criteria.... how do I get a record(with a known GROUP_ID and TYPE) with the most recent EFFECTIVE_DATE and most recent UPDATE_DATE.


2. Given an EFFECTIVE_DATE range (ie july 1 to july 10), how can I find the average AMOUNT per day... with the following things to keep in mind:
a: There will not be a record for every day. For example, July 5 does not have a record, so it has to use the last known amount which may have been July 3.
b: There are multiple instances of the same effective day, but for the average calculation I must use the latest amount for that day which can be determined by the latest UPDATE_DATE

It may be the case where I have to grab a set of data and manually filter out the data for my calculation or there may be some hibernate magic I can do. In either case, any suggestions or strategies would be most welcome.

Thanks in advance.