9 Replies Latest reply: Mar 13, 2014 7:39 AM by K.S.I. RSS

    Reg: Ratio_to_Report

    S10390

      Dear All,

       

      Could you please help me with the below query.

       

      We have the data for two fiscal Years i.e, 2013, 2014, As whole result the below query by omitting the Where clause, the Ratio_to_report function is taking as 100%.

       

      But when I use the Where clause, it is taking the data as 2.14% for 2013 and for 97.86% ( in which the total was 100%).

       

      If I keep the Where clause ( in bold), 2013 / 2014, individually it has to show as 100%, I mean If I given the fiscal_year IN ('2013'), It has to show the data as 100% for that whole year and the same with 2014 as well.

       

      Below is the sample data and kindly help me to get the expected result.

       

      SELECT *

      FROM

        (SELECT site,

          CASE

            WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3)

            THEN EXTRACT(YEAR FROM schedule_date)

            ELSE 1+EXTRACT(YEAR FROM schedule_date)

          END                                              AS fiscal_year,

          COUNT(wo_number)                                 AS wo_count,

          ratio_to_report (COUNT (wo_number)) OVER ()* 100 AS PERCENT

        FROM XM_wo_view

        WHERE site NOT    IN ('CNQ1', 'TRANSPORT', 'QS1', 'CATERING')

        AND wo_status NOT IN ('CANCELED', 'PND CANCEL')

        AND wo_type       <> 'Standing Work Order'

        GROUP BY site,

          CASE

            WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3)

            THEN EXTRACT(YEAR FROM schedule_date)

            ELSE 1+EXTRACT(YEAR FROM schedule_date)

          END

        )

      WHERE fiscal_year IN ('2013')

      ORDER BY site,

        fiscal_year;

       

       

       

      Sample Data:

       

      WO Count - Accommodation Site*NEW*3/13/2014 2:16:04 PM

      SiteFiscal YearWO CountPercent
      AS620132.07
      AS120137.23
      AJM2120134.13
      AJM1820133.10
      AJ320139.30
      AJ2201314.47
      AJ1201325.83
      642.14
        • 2. Re: Reg: Ratio_to_Report
          K.S.I.

          Hi.

          in this case  over () the analytics will work at all volume 

           

          Therefore it is necessary to use   clause over (partition by ) ...

          SELECT *

           

          FROM

           

            (SELECT site,

           

              CASE

           

                WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3)

           

                THEN EXTRACT(YEAR FROM schedule_date)

           

                ELSE 1+EXTRACT(YEAR FROM schedule_date)

           

              END                                              AS fiscal_year,

           

              COUNT(wo_number)                                 AS wo_count,

           

              ratio_to_report (COUNT (wo_number))

              OVER (PARTITION BY

              CASE

           

                WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3)

           

                THEN EXTRACT(YEAR FROM schedule_date)

           

                ELSE 1+EXTRACT(YEAR FROM schedule_date)

           

              END

               )* 100 AS PERCENT

           

            FROM XM_wo_view

           

            WHERE site NOT    IN ('CNQ1', 'TRANSPORT', 'QS1', 'CATERING')

           

            AND wo_status NOT IN ('CANCELED', 'PND CANCEL')

           

            AND wo_type       <> 'Standing Work Order'

           

            GROUP BY site,

           

              CASE

           

                WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3)

           

                THEN EXTRACT(YEAR FROM schedule_date)

           

                ELSE 1+EXTRACT(YEAR FROM schedule_date)

           

              END

           

            )

           

          ORDER BY site,

           

            fiscal_year;

          • 3. Re: Reg: Ratio_to_Report
            Roger

            Now...the WHERE clause has nothing to do with the RATIO_TO_REPORT as the RATIO_TO_REPORT has long been calculated when the WHERE clause takes place.

             

            So what you would have to to is add a partition by clause to the RATIO_TO_REPORT

             

            ratio_to_report (COUNT (wo_number)) OVER ( CASE

             

                                                                                     WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3)

             

                                                                                           THEN EXTRACT(YEAR FROM schedule_date)

             

                                                                                           ELSE 1+EXTRACT(YEAR FROM schedule_date)

             

                                                                                     END)* 100


            so the count would be weighted to the total of each year instead of to the grand total


            hth

            • 4. Re: Reg: Ratio_to_Report
              S10390

              Apologies for the inconvinience Karthick, But, request you to tell me what is missing in my post. I have provided/explained as much as i can.

               

              @Roger:

               

              Thanks for you reply but, I am unable to include that in your code. "ORA-00907: missing right parenthesis" error coming.

               

              SELECT site,

              CASE WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3) THEN EXTRACT(YEAR FROM schedule_date) ELSE 1+EXTRACT(YEAR FROM schedule_date) END AS fiscal_year,

              COUNT(wo_number) AS wo_count,

              RATIO_TO_REPORT (COUNT(wo_number))

              OVER(CASE

              WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3)

              THEN EXTRACT(YEAR FROM schedule_date)

              ELSE 1+EXTRACT(YEAR FROM schedule_date)

              END) *100 AS Percent

              FROM XM_wo_view

              WHERE site NOT IN ('CNAQ1', 'TRANSPORT', 'QCS1', 'CATERING') AND wo_status NOT IN ('CANCELED', 'PND CANCEL') AND wo_type <> 'Standing Work Order' 

              GROUP BY site, CASE WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3) THEN EXTRACT(YEAR FROM schedule_date) ELSE 1+EXTRACT(YEAR FROM schedule_date) END

               

              Thanks

              • 5. Re: Reg: Ratio_to_Report
                _Karthick_

                Here is a simple computation using EMP table.

                 

                SQL> select case when grouping_id(empno) = 1 then
                  2                     ' Total of ' || to_char(deptno) || '=>'
                  3              else
                  4                     to_char(deptno)
                  5         end deptno
                  6       , empno
                  7       , sum(sal) sal
                  8       , sum(sal_per * 100) "SAL_%"
                  9    from (
                10          select deptno
                11               , empno
                12               , sal
                13               , ratio_to_report(sal) over(partition by deptno) sal_per
                14            from emp
                15         )
                16   group
                17      by rollup(deptno, empno)
                18  having grouping_id(deptno) = 0;

                 

                DEPTNO                    EMPNO        SAL      SAL_%
                -------------------- ---------- ---------- ----------
                10                         7782      12450 71.3467049
                10                         7839       5000 28.6532951
                Total of 10=>                       17450        100
                20                         7369      12975  25.923558
                20                         7566      12975  25.923558
                20                         7788      13000  25.973507
                20                         7876      11101  22.179377
                Total of 20=>                       50051        100
                30                         7499      11600 19.8460222
                30                         7521      11250 19.2472198
                30                         7654      11250 19.2472198
                30                         7698      12850 21.9846022
                30                         7844      11500 19.6749358
                Total of 30=>                       58450        100

                 

                14 rows selected.

                • 6. Re: Reg: Ratio_to_Report
                  K.S.I.

                  SELECT site,

                  CASE WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3) THEN EXTRACT(YEAR FROM schedule_date) ELSE 1+EXTRACT(YEAR FROM schedule_date) END AS fiscal_year,

                  COUNT(wo_number) AS wo_count,

                  RATIO_TO_REPORT (COUNT(wo_number))

                  OVER(PARTITION BY  CASE

                  WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3)

                  THEN EXTRACT(YEAR FROM schedule_date)

                  ELSE 1+EXTRACT(YEAR FROM schedule_date)

                  END) *100 AS Percent

                  FROM XM_wo_view

                  WHERE site NOT IN ('CNAQ1', 'TRANSPORT', 'QCS1', 'CATERING') AND wo_status NOT IN ('CANCELED', 'PND CANCEL') AND wo_type <> 'Standing Work Order' 

                  GROUP BY site, CASE WHEN EXTRACT(MONTH FROM schedule_date) IN (1, 2, 3) THEN EXTRACT(YEAR FROM schedule_date) ELSE 1+EXTRACT(YEAR FROM schedule_date) END

                   

                  • 7. Re: Reg: Ratio_to_Report
                    S10390

                    Sorry My mistake, I have missed the PARTITION BY keyword.

                     

                    Thank you K.S.I and others.  Issued got fixed.

                     

                    Thank you Karthick for explaning with emp table.

                    • 8. Re: Reg: Ratio_to_Report
                      S10390

                      @K.S.I,

                       

                      Could you please help again.

                       

                      If run with the where clause for each year once, getting percentage as 100%. where as If i eliminate the where clause (WHERE fiscal_year IN ('2013') ), now, the total percentage is showing as 200%.

                       

                      Thanks

                      • 9. Re: Reg: Ratio_to_Report
                        K.S.I.

                        you can set an example of your data for 2013 and 2014

                        on some records in the period ?