Re: sql question

Mick,

   Personally, I'll use TOMHAG's solution in the future; its compact, easy to follow and works 100% of the time, mine works (subject to the caveat that it should not be run after the 28th of any month) but uses an awful lot of code to accomplish the goal, the other two have major problems. MONTHS_BETWEEN returns a fractional value for dates not exactly one month apart, so 1 = MONTHS_BETWEEN() will only return those dates exactly one month old. ADD_MONTHS adds or subtracts an exact month, so if you ran SELECT ADD_MONTHS(sysdate,-1) from DUAL today it would return 02-NOV-99. Formatting the date 'YYMM' eliminates the need to calculate first and last day, and takes care of the trunc() issue all in one fell swoop.
Regards,
Paul

In article <8231hg$30fg$1_at_grind.server.pavilion.net>,   "Mick Cooke" <bridgend-it_at_pavilion.co.uk> wrote:
> I am having trouble with date function in a query.
> I want to generate a query that will sumall transactions for the
previous
> month.
>
> e.g.
> select a.field1, sum(a.field2), b.field1,b.field2
> from table a, table b
> where a.field1= b.field3
> and a.transaction_date = [this is the problem bit] month -1
> group by b.generic_text,a.ACCOUNT_TYPE_CODE,b.long_name
>
> this runs ok when selecting specific number of days e.g
>
> select a.field1, sum(a.field2), b.field1,b.field2
> from table a, table b
> where a.field1= b.field3
> and a.transaction_date = between trunc(sysdate) and trunc(sysdate -1)
> group by b.generic_text,a.ACCOUNT_TYPE_CODE,b.long_name
>
> I'm sure this is an easy one for someone
>
> thankyou in anticipation and appreciation
>
> Mickc
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 02 1999 - 12:02:48 CET