Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Answered: Stored procedure to calculate monthly sales by year and quarter to date!

    Hello all,

    I have a view VIEW_sale to populate the following data:

    Invoive# | AmountSale | Invoive_date
    ---------------------------------------------------
    1 | 12333.78 | 2015-03-01 00:00:00
    2 | 656546.9 | 2015-01-25 00:00:00
    3 | 435353 | 2015-03-02 00:00:00
    4 | 788.43 | 2015-02-28 00:00:00
    5 | 767.3434 | 2015-02-04 00:00:00
    6 | 54435.45| 2015-04-18 00:00:00
    7 | 454554.1| 2015-04-20 00:00:00
    8 | 22222| 2014-01-28 00:00:00
    9 | 77777| 2013-12-05 00:00:00
    10 | 66666| 2014-07-02 00:00:00

    I try to write a Oracle stored procedure but I am not familiar on Oracle PL/SQL from my SQL server stored procedure. Here is my PL/SQL code:

    create or replace PROCEDURE MONTHLY_SALES AS
    BEGIN
    v_dtNow DATE;
    v_Today DATE;
    v_dtFrom DATE;
    v_dtThru DATE;
    BEGIN

    v_dtNow := SYSTIMESTAMP ;
    v_Today := UTILS.CONVERT_TO_DATE(UTILS.CONVERT_TO_VARCHAR2(v_ dtNow,4000,p_style=>112),p_style=>112) ;
    v_dtThru := utils.dateadd('MS', -3, utils.dateadd('DAY', 1, v_Today)) ;
    v_dtFrom := utils.dateadd('DAY', 1 - utils.day_(v_Today), v_Today) ;

    ---------------------------------------------------------------------------------------
    -- finally, make query for months
    ---------------------------------------------------------------------------------------
    SELECT utils.dateadd('D', utils.datediff('D', 0, INV_DATE) - utils.datepart('D', INV_DATE) + 1, 0) ,
    utils.datename('MONTH', utils.dateadd('D', utils.datediff('D', 0, INV_DATE) - utils.datepart('D', INV_DATE) + 1, 0)) MONTH ,
    SUM(Gross_Sale) MonthlyGrossSale
    FROM VIEW_Sale
    GROUP BY utils.dateadd('D', utils.datediff('D', 0, INV_DATE) - utils.datepart('D', INV_DATE) + 1, 0);
    END;
    END MONTHLY_GS_RC;

    ================================================== ==================================
    I actually translated my SQL Server stored procedure to Oracle using Oracle Developer tool, then compiled it and get the error: Error(3,14): PLS-00103: Encountered the symbol "DATE" when expecting one of the following: := . ( @ % ;

    Any Oracle code expertise, please help me to correct above error or get me a better PL/SQL code. I need an Oracle store procedure to calculate the monthly amount sales and quarter to date sales.

    Here are my expected results:
    year | month | monthlyGrossSale
    --------------------------------------------------------------------------------
    2013-12-01 00:00:00.000 | December | 77777
    2014-01-01 00:00:00.000 | January | 22222
    2014-07-01 00:00:00.000 | July | 66666
    2015-01-01 00:00:00.000 | January | 656546.875
    2015-02-01 00:00:00.000 | February | 1555.77337646484
    2015-03-01 00:00:00.000 | March | 447686.780273438
    2015-04-01 00:00:00.000 | April | 508989.54296875

    and also how to calculate QTD too. Above just sample data and of course there are almost months from Jan to Dec for different years to calculate in real data. I really appreciate your time. Thank in advance.
    Last edited by avt2k6; 04-22-15 at 11:41.

  2. Best Answer
    Posted by avt2k6

    "Hi,

    I use DISTINCT INV_DATE and SUM(Amount) OVER (PARTITION BY (INV_DATE) to solve my issue for monthly sale. Thank anyway."


  3. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1) Unless you have Oracle 12c procedures in Oracle dont return resultsets in the same way as SqlServer
    2) You can just do the select and group by trunc(invoice_date,'MM') to summarise by month
    3) If you have lots of procedures to migrate over why not use Oracle SQLDeveloper which will migrate the SQLServer schema for you. Just remember that Oracle doesnt work in the same way as SQLServer so you need to learn how to things the Oracle way (locking for example can be different depending on how SQLServer is configured)

    Alan

  4. #3
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Hi Alan,

    Thank for your input. I do not have a lot of stored procedures to convert between Oracle and SQL Server. I understand what your explanation if I have time to walk thru. Anyway, I need PL/SQL expertise for correcting the following PL/SQL syntax:

    create or replace PROCEDURE MONTHLY_SALES AS
    BEGIN
    v_dtNow DATE;
    v_Today DATE;
    v_dtFrom DATE;
    v_dtThru DATE;
    BEGIN

    v_dtNow := SYSTIMESTAMP ;
    v_Today := UTILS.CONVERT_TO_DATE(UTILS.CONVERT_TO_VARCHAR2(v_ dtNow,4000,p_style=>112),p_style=>112) ;
    v_dtThru := utils.dateadd('MS', -3, utils.dateadd('DAY', 1, v_Today)) ;
    v_dtFrom := utils.dateadd('DAY', 1 - utils.day_(v_Today), v_Today) ;

    ---------------------------------------------------------------------------------------
    -- finally, make query for months
    ---------------------------------------------------------------------------------------
    SELECT utils.dateadd('D', utils.datediff('D', 0, INV_DATE) - utils.datepart('D', INV_DATE) + 1, 0) ,
    utils.datename('MONTH', utils.dateadd('D', utils.datediff('D', 0, INV_DATE) - utils.datepart('D', INV_DATE) + 1, 0)) MONTH ,
    SUM(Gross_Sale) MonthlyGrossSale
    FROM VIEW_Sale
    GROUP BY utils.dateadd('D', utils.datediff('D', 0, INV_DATE) - utils.datepart('D', INV_DATE) + 1, 0);
    END;
    END MONTHLY_GS_RC;

  5. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1) If you have to use a procedure then if its 12c lookup implicit resultsets otherwise lookup pipeline functions
    2) If your summarising by calender month then use the group by I mentioned before, avoid using user defined packages as Oracle built in functions work a lot quicker.
    3) To summarise by quarter use trunc(invoice_date,'Q')

    Alan

  6. #5
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Thank you for your input. I am not back end PL/SQL coding debugger, so I have some issue to correct the syntax. Even though I used the Oracle Developer tool to get converting between T-SQL to PL/SQL but that tool was not perfect.

    Here is the Oracle Developer tool to convert it to PL/SQL for my T-SQL stored procedure as following:

    CREATE OR REPLACE PROCEDURE MONTHLY_GS_RC( cv_1 OUT SYS_REFCURSOR )
    AS
    v_dtNow DATE;
    v_Today DATE;
    v_dtFrom DATE;
    v_dtThru DATE;
    BEGIN
    v_dtNow := SYSTIMESTAMP ;
    v_Today := UTILS.CONVERT_TO_DATE(UTILS.CONVERT_TO_VARCHAR2(v_ dtNow,4000,p_style=>112),p_style=>112) ;
    v_dtThru := utils.dateadd('MS', -3, utils.dateadd('DAY', 1, v_Today)) ;
    v_dtFrom := utils.dateadd('DAY', 1 - utils.day_(v_Today), v_Today) ;

    OPEN cv_1 FOR SELECT utils.dateadd('D', utils.datediff('D', 0, INV_DATE) - utils.datepart('D', INV_DATE) + 1, 0) , utils.datename('MONTH', utils.dateadd('D', utils.datediff('D', 0, INV_DATE) - utils.datepart('D', INV_DATE) + 1, 0)) MONTH , SUM(AMOUNT) MonthlySales FROM VIEW_GS_RC
    GROUP BY utils.dateadd('D', utils.datediff('D', 0, INV_DATE) - utils.datepart('D', INV_DATE) + 1, 0) ;
    END;

    When I compile it and then it produced these errors

    Error(9,3): PL/SQL: Statement ignored
    Error(9,15): PLS-00201: identifier 'UTILS.CONVERT_TO_DATE' must be declared
    Error(10,3): PL/SQL: Statement ignored
    Error(10,15): PLS-00201: identifier 'UTILS.DATEADD' must be declared
    Error(11,3): PL/SQL: Statement ignored
    Error(11,15): PLS-00201: identifier 'UTILS.DATEADD' must be declared
    Error(13,17): PL/SQL: SQL Statement ignored
    Error(14,26): PL/SQL: ORA-00904: "UTILS"."DATEADD": invalid identifier

    In my view data, there are three main fields: INVOICE NUMBER, AMOUNT and INVOICE DATE. I need the monthly sale calculation based on INVOICE DATE for SUM (AMOUNT) each month. Are you familiar with Oracle PL/SQL DATE syntax. If so, please correct above errors. Thanks.

  7. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Like I said earlier do a group by trunc(invoice_date,'MM') instead of your current group by. Unfortunately I dont know what your doing with your variables as they seem to serve no purpose. Probably the best thing is to try and learn Oracle SQL and PLSQL as it should be fairly straightforward if you know sql server. PLSQL is just like BASIC and is very simple see the manual at http://docs.oracle.com/cd/E11882_01/...e10766/toc.htm

    Alan

  8. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The only thing I know about SQL Server is how to spell it.
    I don't know if UTILS.CONVERT_TO_DATE or UTILS.DATEADD are available standard functions in SS or unique to your system.
    In any case these two functions are not available in Oracle.
    You will need to either write your own versions of them in PL/SQL or change the code to use existing Oracle functions.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #8
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    I got that above converting from Oracle Developer tool for SQL server T-SQL to PL/SQL Oracle. I am not familiar with PL/SQL for DATE function and your above inputs, so I knew that tool not reliable for doing translating. Anyone can write PL/SQL expertise, please help me to convert it. Thanks in advance.

  10. #9
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Hi,

    I use DISTINCT INV_DATE and SUM(Amount) OVER (PARTITION BY (INV_DATE) to solve my issue for monthly sale. Thank anyway.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •