Re: sql question

Mick,
[Quoted]   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 Wed Dec 01 1999 - 21:14:00 CET