--- In firebird-support@yahoogroups.com, "mspencewasunavailable"
<firebird@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "Muthu Annamalai"
> <pearlamerica@> wrote:
> >
> > I need help with arranging months in my stored procedure. See my

SP

> >
> > FOR SELECT FIRST 12 EXTRACT(MONTH FROM RECEIPTDATE),COUNT(*),SUM
> > (AMOUNT)
> > FROM RECEIPT
> > GROUP BY 1
> > INTO :PERIOD,:RECEIPTS,:SALES
> > DO SUSPEND;
> >
> > The stored procedure results are like this
> >
> > PERIOD
> > 1
> > 2
> > 8
> > 9
> > 10
> > 11
> > 12
> >
> > I need to sort year wise first and then month as follows
> >
> > PERIOD
> >
> > 10
> > 11
> > 12
> > 1
> > 2
> > 8
> > 9
> >
> > Any help is highly appreciated
> >
> > Thanks,
> >
> > Muthu
> >
>
> Haven't tried either of these, but:
>
> ORDER BY (CASE WHEN PERIOD >= 10 THEN PERIOD - 12 ELSE PERIOD END)
>
> should work if you always want to start with October (because, for
> example, that's the beginning of your fiscal year).
>
> More generally, if you just want to get the next twelve months and
> have an arbitrary start date, you could do this:
>
> ORDER BY EXTRACT(YEAR FROM RECEIPTDATE) * 100 + EXTRACT(MONTH FROM
> RECEIPTDATE)
>
> Michael D. Spence
> Mockingbird Data Systems, Inc.
>

I can't use your first solution as the period doesn't always starts
with oct, as endusers might have different fiscal year.

The Second solution, order by gives me the following error

"Invalid expression in the ORDER BY clause (not contained in either
an aggregate function or the GROUP BY clause)."

Thanks for your effort.

Muthu