12 Replies Latest reply on Dec 4, 2018 8:04 AM by BluShadow

    calculate and produce result

    3134376

      Ok, I have simplified requirement now.

       

      Below is table and expected output

       

      WITH t AS
      (SELECT 1 AS O, 1 AS L, 'X' AS P, 250 AS Q, 4 AS GS, 50 AS gcq, 200 AS TR, 7 AS SI, 'Y' AS FLAG, SYSDATE AS rd FROM DUAL UNION
       SELECT 2 AS O, 1 AS L, 'Y' AS P, 300 AS Q, 7 AS GS, 50 AS gcq, 200 AS TR, 7 AS SI, 'Y' AS FLAG, SYSDATE AS rd FROM DUAL 
      
      
      ) SELECT t1.lvl, t.O, t.L, t.P, t.q, t.rd FROM t, LATERAL (SELECT LEVEL lvl FROM DUAL CONNECT BY LEVEL <= CASE WHEN t.flag = 'N' THEN 1
                                                                                                               WHEN t.flag = 'Y' AND t.q > t.tr THEN CASE WHEN t.q / t.gcq > t.gs THEN t.gs END
                                                                                                               WHEN t.flag = 'Y' AND t.q > t.tr THEN CASE WHEN t.q / t.gcq < t.gs THEN t.q / t.gcq END
                                                                                                              END) t1;
      
      Expected output - 
      
      For O = 1 Need 4 lines as 50 qty in first 3 as gcq is 50 and in last line 100 to make it equal to Q i.e. 250
      
      In date column add 7 working day to each line as well.
      
      LVL O L P Q RD
      
      1 1 1 X 50          03-DEC-2018 10:51:26 AM
      2 1 1 X 50          12-DEC-2018 10:51:26 AM
      3 1 1 X 50          21-DEC-2018 10:51:26 AM
      4 1 1 X 100         01-JAN-2019 10:51:26 AM
      
       For O = 2 split in 6 lines on basis of when q > tr and q/gcq < gs
      
      1 2 1 Y 50           03-DEC-2018 10:51:26 AM
      2 2 1 Y 50           12-DEC-2018 10:51:26 AM
      3 2 1 Y 50           21-DEC-2018 10:51:26 AM
      4 2 1 Y 50           01-JAN-2019 10:51:26 AM
      5 2 1 Y 50 10-JAN-2019 10:51:26 AM
      6 2 1 Y 50 21-JAN-2019 10:51:26 AM
      
        • 1. Re: calculate and produce result
          Frank Kulash

          Hi,

           

          Thanks for posting the sample data.

          Don't forget to post the exact results you want from that sample data.  Format the post so it's clear what values are in which columns.  If the results depend on SYSDATE, then say which SYSDATE produces the results you post; e.g. "If I run the query at 13:00 on December 2, 1028, then the output should be …".

          Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

          • 2. Re: calculate and produce result
            John Thorton

            3134376 wrote:

             

            Hi All,

             

            I hve two table as below , I need to build a logic in sql only to do calcuation on quantity and then on basis of rule shown below want to return additional rows.

             

             

             

            WITH t1 AS

            ( SELECT 1 AS order_number, 1 AS line_number, 'x' AS item, 250 AS quantity, 3 split_count, 'c' criteria, 't1' criteria_tier FROM DUAL UNION

            SELECT 1 AS order_number, 2 AS line_number, 'y' AS item, 250 AS quantity, 3 split_count, 'c' criteria, 't1' criteria_tier FROM DUAL UNION

            SELECT 1 AS order_number, 3 AS line_number, 'z' AS item, 250 AS quantity, 10 split_count, 'c' criteria,'t1' criteria_tier FROM DUAL UNION

            SELECT 1 AS order_number, 4 AS line_number, 'a' AS item, 250 AS quantity, 10 split_count, 'c' criteria, 't1' criteria_tier FROM DUAL

            )

            SELECT * FROM t1;

             

             

            WITH t2 AS

            (SELECT 'x' AS item, 'c' AS criteria, 100 AS tier1_qty, 100 AS tier1_qty_tr, NULL AS tier2_qty, NULL AS tier2_qty_tr, '7' AS split_interval, 'N' AS split_allowed FROM DUAL UNION

            SELECT 'y' AS item, 'c' AS criteria, 50 AS tier1_qty, 300 AS tier1_qty_tr, NULL AS tier2_qty, NULL AS tier2_qty_tr, '7' AS split_interval, 'Y' AS split_allowed FROM DUAL UNION

            SELECT 'z' AS item, 'c' AS criteria, 50 AS tier1_qty, 300 AS tier1_qty_tr, NULL AS tier2_qty, NULL AS tier2_qty_tr, '7' AS split_interval, 'Y' AS split_allowed FROM DUAL UNION

            SELECT 'a' AS item, 'c' AS criteria, 50 AS tier1_qty, 200 AS tier1_qty_tr, NULL AS tier2_qty, NULL AS tier2_qty_tr, '7' AS split_interval, 'Y' AS split_allowed FROM DUAL))

            SELECT * FROM t2;

             

             

            Match

            t1.item, criteria, criteria_tier = t2.item, criteria, criteria_tier i.e (criteria_tier1 OR 2)

             

             

            Rule 1 -

             

             

            If t2.split_allowed = N return as below

             

             

            ORDER_NUMBER LINE_NUMBER ITEM QUANTITY delivery_date

            1 1 x 250 SYSDATE

             

             

            Rule 2 -

             

             

            If t2.split_allowed = Y and t21.tier1_qty_tr < t1.quantity then return as below

             

             

            ORDER_NUMBER LINE_NUMBER ITEM QUANTITY delivery_date

            1 2 y 250 SYSDATE

             

             

            Rule 3 -

             

             

            If t2.split_allowed = Y and t21.tier1_qty_tr > t1.quantity and t1.quantity / t2.tier1_qty > t1.split_count then generate t1.split_count + 1 rows as below.

             

             

            ORDER_NUMBER LINE_NUMBER ITEM QUANTITY delivery_date

            1 2 y 50 SYSDATE

            1 2.1 y 50 SYSDATE + 7)

            1 2.2 y 50 (SYSDATE + 7) + 7

            1 2.3 y 100 ((SYSDATE + 7) + 7) + 7 exculde weekend while adding 7 in next rows

             

             

            If t2.split_allowed = Y and t21.tier1_qty_tr > t1.quantity and t1.quantity / t2.tier1_qty < t1.split_count then generate rows as count of the calculation.

             

             

            250/50

             

             

            ORDER_NUMBER LINE_NUMBER ITEM QUANTITY delivery_date

            1 3 z 50 SYSDATE

            1 3.1 z 50 SYSDATE + 7)

            1 3.2 z 50 (SYSDATE + 7) + 7

            1 3.3 z 50 ((SYSDATE + 7) + 7) + 7

            1 3.4 z 50 (((SYSDATE + 7) + 7) + 7) + 7 exculde weekend while adding 7 in next rows

            Why are so many of your threads not marked as answered?

             

            3134376

             

            What are the expected & desired results based upon sample data?

            • 3. Re: calculate and produce result
              Frank Kulash

              Hi,

              3134376 wrote:

               

              Hi All,

              ...

              If t2.split_allowed = Y and t21.tier1_qty_tr > t1.quantity and t1.quantity / t2.tier1_qty < t1.split_count then generate rows as count of the calculation.

               

               

              250/50

               

               

              ORDER_NUMBER LINE_NUMBER ITEM QUANTITY delivery_date

              1 3 z 50 SYSDATE

              1 3.1 z 50 SYSDATE + 7)

              1 3.2 z 50 (SYSDATE + 7) + 7

              1 3.3 z 50 ((SYSDATE + 7) + 7) + 7

              1 3.4 z 50 (((SYSDATE + 7) + 7) + 7) + 7 exculde weekend while adding 7 in next rows

              You need to be more precise.

              How many output rows will you want?

              How is quantity calculated? Explain in general terms, and give a specific example.

              What does " exculde weekend while adding 7" mean?  Explain in general terms, and give a specific example.

               

              Why don't you want any output for item='a'?

               

              I don't see anything in your explanation about some columns, like order_number, criteria and criteria_tier.  Do they play any role in this problem (aside from being displayed in the final output)?  If so, explain.

              • 4. Re: calculate and produce result
                Frank Kulash

                Hi,

                 

                Please don't make changes to your posts.  It's very confusing, and easy to miss.  If you need to make corrections to an existing message, post a new reply.  The only change you should make to an existing message is to add a line like "EDIT: See reply #4 for corrections".

                 

                Here's one way to do what you requested:

                SELECT    c.lvl

                ,         t.o

                ,         t.l

                ,         t.p

                ,         c.q

                ,         cf.dt

                FROM      t

                CROSS APPLY (

                                SELECT  LEVEL  AS lvl

                                ,       CASE

                                            WHEN  CONNECT_BY_ISLEAF = 0

                                            THEN  gcq

                                            ELSE  q - (gcq * (LEVEL - 1))

                                        END    AS q

                                FROM    dual

                                CONNECT BY   t.flag    = 'Y'

                                        AND  t.q       > t.tr

                                        AND  LEVEL     <= LEAST ( t.gs

                                                                , CEIL (t.q / t.gcq)

                                                                )

                            )       c

                JOIN      calendar  cp  ON   cp.dt           = TRUNC (t.rd)

                JOIN      calendar  cf  ON   cf.bus_day_cnt  = cp.bus_day_cnt + (7 * (c.lvl - 1))

                                        AND  cf.day_type     = 'B'

                ORDER BY  t.o

                ,         c.lvl

                ;

                For the calendar table, see PL/SQL function to calculate non-working days  (reply #4).

                If t is populated on December 3, 2018, then the output from the query above is

                LVL     O    L P        Q DT

                ---- ---- ---- --- ------ -----------

                   1    1    1 X       50 03-Dec-2018

                   2    1    1 X       50 12-Dec-2018

                   3    1    1 X       50 21-Dec-2018

                   4    1    1 X      100 03-Jan-2019

                   1    2    1 Y       50 03-Dec-2018

                   2    2    1 Y       50 12-Dec-2018

                   3    2    1 Y       50 21-Dec-2018

                   4    2    1 Y       50 03-Jan-2019

                   5    2    1 Y       50 14-Jan-2019

                   6    2    1 Y       50 24-Jan-2019

                In my calendar table, there are 3 holidays between December 3, 2018 and January 24, 2019; that's why the dt column is up to 3 days later than what you posted.  If you create a calendar table without those holidays (or with a column like bus_day_cnt that ignores holidays), then you can get the exact results you requested.

                 

                You can do this job without a calendar table, but it's much more complicated and less efficient.

                [EDIT: Actually, it's not all that complicated.  See reply #7, below.)

                • 5. Re: calculate and produce result
                  jaramill

                  John Thorton wrote:

                   

                  Why are so many of your threads not marked as answered?

                   

                  3134376

                   

                   

                  Out of the 40 topics the OP created  (20 topics per page history), 25 of them are left UNANSWERED

                   

                   

                  • 6. Re: calculate and produce result
                    3134376

                    Why to mark answer if solution not given ?

                    • 7. Re: calculate and produce result
                      Frank Kulash

                      Hi,

                       

                      Here's a way to do it without a calendar table, using a recursive WITH clause instead of CONNECT BY:

                      WITH  r    (o, l, p, q, gcq, rd, lvl, max_lvl)  AS

                      (

                          SELECT  o, l, p, q, gcq, rd

                          ,       1    AS lvl

                          ,       CASE

                                      WHEN  flag = 'N'

                                      OR    q    <= tr

                                      THEN  1

                                      ELSE  LEAST ( gs

                                                  , CEIL (q / gcq)

                                                  )

                                  END  AS max_lvl

                          FROM    t

                      UNION ALL

                          SELECT  o, l, p, q, gcq

                          ,       rd + CASE  TO_CHAR (rd, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')

                                           WHEN  'THU'  THEN  11

                                           WHEN  'FRI'  THEN  11

                                           WHEN  'SAT'  THEN  10

                                                        ELSE   9

                                       END  AS rd

                          ,       lvl + 1   AS lvl

                          ,       max_lvl

                          FROM    r

                          WHERE   lvl  < max_lvl

                      )

                      SELECT    lvl, o, l, p

                      ,         CASE

                                    WHEN  lvl  < max_lvl

                                    THEN  gcq

                                    ELSE  q - (gcq * (lvl - 1))

                                END  AS q

                      ,         rd

                      FROM      r

                      ORDER BY  o, lvl

                      ;

                      Output (exactly as requested):

                      LVL     O    L P        Q RD

                      ---- ---- ---- --- ------ -----------

                         1    1    1 X       50 03-Dec-2018

                         2    1    1 X       50 12-Dec-2018

                         3    1    1 X       50 21-Dec-2018

                         4    1    1 X      100 01-Jan-2019

                         1    2    1 Y       50 03-Dec-2018

                         2    2    1 Y       50 12-Dec-2018

                         3    2    1 Y       50 21-Dec-2018

                         4    2    1 Y       50 01-Jan-2019

                         5    2    1 Y       50 10-Jan-2019

                         6    2    1 Y       50 21-Jan-2019

                      Whether you use CONNECT BY or recursive WITH, I still recommend using a calendar table, especially if you need to consider holidays.

                       

                      What results would you want if t.rd is a Saturday or Sunday?  You may need to adjust the CASE expression that generates rd slightly.

                      1 person found this helpful
                      • 8. Re: calculate and produce result
                        jaramill

                        3134376 wrote:

                         

                        Why to mark answer if solution not given ?

                        Agreed on your point.  It's when the person DISAPPEARS from not answering people's questions to continue is the issue.  Most of the threads people respond waiting for you to come back to answer yet nothing from you.

                        • 9. Re: calculate and produce result
                          3134376

                          This forum is a gold mine can you tell me how I can search it effectively for my needs

                           

                          @ Moderators,

                           

                          Modify the way of searching this forum and don't give me silly excuses.

                           

                          Shall I raise a SR for same ?

                          • 10. Re: calculate and produce result
                            Frank Kulash

                            Hi,

                            3134376 wrote:

                             

                            ...

                            Modify the way of searching this forum and don't give me silly excuses.

                             

                            Shall I raise a SR for same ?

                            Sorry, I don't understand.

                             

                            If you have trouble using some feature of this forum (such as the search feature), or if you want to suggest improvements, create a new thread in the Community Feedback Forum:

                            Community Feedback (No Product Questions)

                             

                            By the way, if you do have trouble using the search feature, you've got lots of company.  Usually, I don't even try the forum search feature; I use a generic search engine.  Often, most of the hits are from this forum.

                            • 11. Re: calculate and produce result
                              3134376

                              Why I can't access link given here -

                               

                              PL/SQL function to calculate non-working days

                               

                               

                              • 12. Re: calculate and produce result
                                BluShadow

                                3134376 wrote:

                                 

                                Why I can't access link given here -

                                 

                                PL/SQL function to calculate non-working days

                                 

                                 

                                 

                                 

                                 

                                 

                                Because that link goes to a post on the MOSC community, for which you will need to be a paying customer with a Customer Support Identifier.  If your company has a support contract with Oracle (which they should have if they are using it commercially) then you need to find out who is the "Oracle Administrator" within your company and ask for them to add you to the list of users within your company who can access Oracle Support (MOS) and then you'll be able to also access the MOS community (MOSC)