Subject | Re: Calculating max N-consecutive-days activity |
---|---|

Author | Svein Erling Tysvær |

Post date | 2009-03-16T17:27:20Z |

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

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.