Re: sql question

If the Report is based month, one thing you could use is add_months eg
date between sysdate and add_months(sysdate,-1) Tom

<pberetta_at_my-deja.com> wrote in message news:823vi4$sjr$1_at_nnrp1.deja.com...
> Mick,
> If you can reasonably assume the report will only be run on or before
> a cutoff date, say the 25th of the month following the month you want
> to report on, then this should always work:
>
> select a.field1, sum(a.field2), b.field1,b.field2
> from table a, table b
> where a.field1= b.field3
> and TRUNC(a.transaction_date) = between
> TO_DATE(SUBSTR(TO_CHAR(TRUNC(sysdate - 25),'RRRRMMDD'),1,6)
> || '01','RRRRMMDD')
> and
> LAST_DAY(TO_DATE(SUBSTR(TO_CHAR(TRUNC(sysdate - 25),'RRRRMMDD'),1,6)
> || '01','RRRRMMDD'));
> group by b.generic_text,a.ACCOUNT_TYPE_CODE,b.long_name;
>
> I TRUNC()ed the transaction date to be sure all transactions on the
> last day of the month were included, without it you might miss some if
> the timestamp is > 00:00:00
>
> Hope this helps,
> 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 - 02:30:10 CET