Re: sql question

On Wed, 1 Dec 1999, Mick Cooke 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
>
>
>
>

Try this:

   select a.field1, sum(a.field2), b.field1, b.field2      from table a, table b
    where a.field1= b.field3

• and to_char(a.transaction_date,'yymm') =
• to_char(add_months(sysdate,-1),'yymm')

    group by b.generic_text,a.ACCOUNT_TYPE_CODE,b.long_name /

Tommy
Email: tomhag_at_rsv.se Received on Wed Dec 01 1999 - 14:49:37 CET