You could try something like
select item,
sum(decode(sign(transaction_date - trunc(sysdate,'Month'),-1,0,
0,nvl(value,0),
1,nvl(value,0)) mtd,
sum(decode(sign(transaction_date - trunc(sysdate,'Year'),-1,0,
0,nvl(value,0),
1,nvl(value,0)) ytd
from transactions_table
group by item
The sign should work out whether the transaction date is greater than start
of month or start of years and if not add 0 to the total. The nvls are just
in case you might have any null values lurking about. You may be able to do
it better in reports but I don't have it to hand right now.
HTH
Iain Nicoll
-----Original Message-----
Sent: 11 June 2001 21:20
To: Multiple recipients of list ORACLE-L
I am creating a report based on a couple tables. The report should show the
total amount sold for the current month and another field for total amount
sold Year To Date.
How can I created the report so that the month to date value for a
particular item is adjacent to the total amount for Year to date. Please
see below for desired reports output.
Item # Item Description Amount Sold (Current Month)
Amount Sold (YTD)
54654 Door jams 20,245.00
465,475.00
46545 Windows Casing 1500.00
124,445.57
Does anyone know what reference material I should purchase for oracle
reports?
Thanks,
Mark
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Liggayu
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain (Calanais)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
