> Have you actually tried preparing your statement

Yes, it works on FB 1.5.2.

What I'm trying to do is find out, over a given number of consecutive days in a given range, what was the maximum number of each type of item that was sold. So if, for example, exactly two widgets were sold every day in the month of March, the maximum activity over a three day period would be six widgets. If on the fifteenth they were suddenly popular and three were sold, then for the month of March the maximum would be seven.

Using your example, joining the query to itself would take care of the consecutive days. The posted date is not needed in the result set, so the 'not exists' logic can be left off.

I fell back to using a stored procedure since we're still on FB 1.5.

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Monday, March 16, 2009 1:27 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Calculating max N-consecutive-days activity

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.




------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links




Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.