Re: sql question

Mick Cooke <bridgend-it_at_pavilion.co.uk> wrote in message news:8231hg$30fg$1_at_grind.server.pavilion.net...
> 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

Use this:

  a.transaction_date>=trunc(add_months(sysdate, -1), 'month')   and a.transaction_date<trunc(sysdate, 'month')

It's a bad idea to deal with a date as a string using unbeseeming TO_CHAR() and SUBSTR(), it will degrade performance. Using "months_between (sysdate, a.transaction_date) = 1" is absolutely wrong. Received on Fri Dec 03 1999 - 17:09:18 CET