OK, I'll try it. Thanks.

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

OK, I'm surprised to learn that your query actually works. But understanding your problem, I think it may be solvable by doing something similar to (you may have to adjust the parameters +- 1 day, to ascertain that all sums are within the given dates, and I'm not convinced the statement will prepare):

Select
t.attribute1, t.attribute2,
max(coalesce(select sum(t2.quantity)
from tbl_transactions t2
where t2.attribute1=t.attribute1 and t2.attribute2=t.attribute2
and t2.posted = t.posted-1
), 0)+
coalesce(select sum(t3.quantity)
from tbl_transactions t3
where t3.attribute1=t.attribute1 and t3.attribute2=t.attribute2
and t3.posted = t.posted
), 0)+
coalesce(select sum(t4.quantity)
from tbl_transactions t4
where t4.attribute1=t.attribute1 and t4.attribute2=t.attribute2
and t4.posted = t.posted+1
), 0))
From
tbl_transactions t
Where
t.posted between ? And ?
Group by
t.attribute1, t.attribute2

HTH,
Set

--- In firebird-support@yahoogroups.com, "Rick Debay" wrote:
> > 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.
>




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

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

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