Have you actually tried preparing your statement, Rick? I would expect it to give an error, at least that's what I think I normally receive when I try a subselect when using GROUP BY (and it makes me have to think about alternative ways to do whatever I am trying to do).

With a recent Firebird version, I would guess you could try something like this:

with TmpSum(attribute1, attribute2, posted, quantity)
as
(Select
attribute1, attribute2, posted, sum(quantity)
from tbl_transactions
where posted between :FromDate and :ToDate
group by 1, 2, 3)

select ts.attribute1, ts.attribute2, ts.posted, ts.quantity
from TmpSum MaxTs
join TmpSum ts on MaxTs.attribute1 = ts.attribute1
and MaxTs.attribute2 = ts.attribute2
and ts.posted between MaxTs.posted - 7 and MaxTs.posted + 7
where not exists(
select null
from TmpSum ts2
where MaxTs.attribute1 = ts2.attribute1
and MaxTs.attribute2 = ts2.attribute2
and (MaxTs.quantity < ts2.quantity
or (MaxTs.quantity = ts2.quantity
and MaxTs.posted < ts2.posted)))

What this should do, is to return the sum for all days the last seven days before and after the day with the maximum quantity (within your specified date range). If the maximum quantity is equal for several days, I chose the last of these.

I hope I understood the problem you're actually trying to solve and that I didn't answer a question you didn't ask.

Set

--- In firebird-support@yahoogroups.com, "Rick Debay" wrote:
>
> Given activity over a period of days, I'd like to determine the maximum
> activity over a given number of consecutive days.
> The following SQL gives the maximum over one day, but I can't think how
> to do it over more than one.
> Well, I can but it's ugly and involves repeating the select statement,
> adding a 'skip' for each additional day. And then that wouldn't give
> consecutive days.
>
> Select
> t.attribute1, t.attribute2
> (select first 1 sum(t.quantity)
> from tbl_transactions t2
> where t2.attribute1=t.attribute 1 and t2.attribute2=t.attribute2
> and t2.posted between min(t.posted) And max(t.posted)
> group by t2.posted
> order by sum(t.quantity) desc
> )
> From
> tbl_transactions t
> Where
> t.posted between ? And ?
> Group by
> t.attribute1, t.attribute2
>
> The problem I'm trying to solve, is determining appropriate safety
> stock.