Subject | Re: How to arrange Year wise and then Month Wise |
---|---|

Author | mspencewasunavailable |

Post date | 2007-04-08T02:39:28Z |

--- In firebird-support@yahoogroups.com, "Muthu Annamalai"

<pearlamerica@...> wrote:

clause:

FOR SELECT FIRST 12 EXTRACT(MONTH FROM RECEIPTDATE) as PERIOD,COUNT

(*),SUM (AMOUNT)

FROM RECEIPT GROUP BY PERIOD, EXTRACT(YEAR FROM RECEIPTDATE) * 100 +

EXTRACT(MONTH FROM RECEIPTDATE)

ORDER BY EXTRACT(YEAR FROM RECEIPTDATE) * 100 + EXTRACT(MONTH FROM

RECEIPTDATE)

<pearlamerica@...> wrote:

>my

> --- 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

> SP(*),SUM

> > >

> > > FOR SELECT FIRST 12 EXTRACT(MONTH FROM RECEIPTDATE),COUNT

> > > (AMOUNT)END)

> > > 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

> >for

> > should work if you always want to start with October (because,

> > example, that's the beginning of your fiscal year).and

> >

> > More generally, if you just want to get the next twelve months

> > have an arbitrary start date, you could do this:FROM

> >

> > ORDER BY EXTRACT(YEAR FROM RECEIPTDATE) * 100 + EXTRACT(MONTH

> > RECEIPTDATE)starts

> >

> > Michael D. Spence

> > Mockingbird Data Systems, Inc.

> >

>

> I can't use your first solution as the period doesn't always

> with oct, as endusers might have different fiscal year.either

>

> The Second solution, order by gives me the following error

>

> "Invalid expression in the ORDER BY clause (not contained in

> an aggregate function or the GROUP BY clause)."It just wants you to add the missing expression to the GROUP BY

>

> Thanks for your effort.

>

> Muthu

>

clause:

FOR SELECT FIRST 12 EXTRACT(MONTH FROM RECEIPTDATE) as PERIOD,COUNT

(*),SUM (AMOUNT)

FROM RECEIPT GROUP BY PERIOD, EXTRACT(YEAR FROM RECEIPTDATE) * 100 +

EXTRACT(MONTH FROM RECEIPTDATE)

ORDER BY EXTRACT(YEAR FROM RECEIPTDATE) * 100 + EXTRACT(MONTH FROM

RECEIPTDATE)