Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help with getting sales data from previous years..

379746Mar 30 2010 — edited Mar 30 2010
Hi,
I have a package that I am using to get sales data from previous years in a: sales this month, b: sales YTD (which is sales from first of the fiscal year JUL01 to the day the report is run), and c: last years sales which is fiscal year sales for the last fiscal year.
Cases this year comes out correct in every case because the date is picked directly from p_start_date and p_end_date.
But I need help in getting the YTD and last year cases so that if I run the program, say, from 01-FEB-2010 to 28-FEB-2010, YTD cases are calculated from 01-JUL-2009 thru 28-JUL-2010 and last year cases are calculated from 01-JUL-2008 thru 30-JUN-2009
Any thoughts on how I should approach this?
Thank you all.
A/A
CREATE OR REPLACE PACKAGE BODY APPS.Nfpcsales_Private_Label_Pkg IS
   PROCEDURE nfpcsales_private_label_proc (
      p_start_date DATE,
      p_end_date DATE,
      p_last_year_start_date DATE,
      p_this_year_start_date DATE) IS
      l_cases_ytd          NUMBER;
      l_dlr_ytd            NUMBER;
      l_cases_last_year    NUMBER;
      l_dlr_last_year      NUMBER;
      l_cases_this_month   NUMBER;
      l_dlr_this_month     NUMBER;
----
For cursor (here the cursor is defned)
---
--AND    TRUNC (rcta.trx_date) >= p_this_year_start_date
            --  AND    TRUNC (rcta.trx_date) <= TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1) ) );
---
--

Here I get cases this month
--AND    TRUNC (rcta.trx_date) >= p_start_date
               AND    TRUNC (rcta.trx_date) <= p_end_date;
---
Here I get cases YTD
--
    -- AND    TRUNC (rcta.trx_date) >= p_this_year_start_date
            --  AND    TRUNC (rcta.trx_date) <= TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1) ) );
----
and here I want to get last year cases
--
             -- AND    TRUNC (rcta.trx_date) >= p_last_year_start_date
             --  AND    TRUNC (rcta.trx_date) <= TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -13) ) );
--
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 27 2010
Added on Mar 30 2010
3 comments
970 views