RE: Oracle reports

From: Nicoll, Iain (Calanais) <iain.nicoll_at_calanais.com>
Date: Mon, 11 Jun 2001 15:11:28 -0700
Message-ID: <F001.00324B43.20010611151823@fatcity.com>

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: markliggayu_at_allweatherwindows.com

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: ListGuru_at_fatcity.com (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: iain.nicoll_at_calanais.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------