1 2 Previous Next 23 Replies Latest reply on Nov 15, 2019 12:27 PM by user5716448 Go to original post
      • 15. Re: Indentifying outstanding orders
        L. Fernigrini

        This should take into account having more than 1 item for any order and also having the same order number for different customers (you never included the unique primary keys of the tables)

        It also handles the scenario if an order is partially packed the same day it is opened, or more than 1 shipment is made on the same day.

         

        WITH vSource AS (

        SELECT o.customer, po.product,  po.order_number, po.order_item, po.date_packed AS EventDate, o.item_quantity AS Ordered, po.packed_qty

        FROM packed_orders po

            JOIN orders o ON o.order_number= po.order_number AND o.order_item = po.order_item

        UNION ALL

        SELECT o.customer, o.product,  o.order_number, o.order_item, date_created AS EventDate, o.item_quantity AS Ordered, 0 AS packed_qty

        FROM  orders o),

        vData AS (

            SELECT customer, product,  order_number, order_item, EventDate, MAX(Ordered) AS Ordered, SUM(packed_qty) AS packed_qty

            FROM vSource s

            GROUP BY customer, product,  order_number, order_item, EventDate

        )

        SELECT customer, product,  order_number, order_item, EventDate, Ordered,

                    SUM(packed_qty) OVER (PARTITION BY customer, product,  order_number, order_item ORDER BY EventDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Packed,

                    Ordered - SUM(packed_qty) OVER (PARTITION BY customer, product,  order_number, order_item ORDER BY EventDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Outstanding

        FROM vData;

         

        You should pivot the information on the presentation layer, since nothing prevents you to have an order shipped in 1 2, 3 or N days, thus making the number of columns (days) unknown...

        • 16. Re: Indentifying outstanding orders
          L. Fernigrini

          Some testing data:

           

          insert into packed_orders

          values

          ('A',100,1,5,to_date('14/11/2019','DD/MM/YYYY'));

          insert into packed_orders

          values

          ('A',100,1,10,to_date('15/11/2019','DD/MM/YYYY'));

          insert into packed_orders

          values

          ('A',100,1,12,to_date('15/11/2019','DD/MM/YYYY'));

          insert into packed_orders

          values

          ('A',100,1,11,to_date('16/11/2019','DD/MM/YYYY'));

          insert into packed_orders

          values

          ('A',100,1,2,to_date('17/11/2019','DD/MM/YYYY'));

           

          • 17. Re: Indentifying outstanding orders
            L. Fernigrini

            My final version showing also the quantity packed by day also:

             

            WITH vSource AS (

            SELECT o.customer, po.product,  po.order_number, po.order_item, po.date_packed AS EventDate, o.item_quantity AS Ordered, po.packed_qty

            FROM packed_orders po

                JOIN orders o ON o.order_number= po.order_number AND o.order_item = po.order_item

            UNION ALL

            SELECT o.customer, o.product,  o.order_number, o.order_item, date_created AS EventDate, o.item_quantity AS Ordered, 0 AS packed_qty

            FROM  orders o),

            vData AS (

                SELECT customer, product,  order_number, order_item, EventDate, MAX(Ordered) AS Ordered, SUM(packed_qty) AS packed_qty

                FROM vSource s

                GROUP BY customer, product,  order_number, order_item, EventDate

            )

            SELECT customer, product,  order_number, order_item, EventDate, Ordered,

                        packed_qty As PackedOnDate,

                        SUM(packed_qty) OVER (PARTITION BY customer, product,  order_number, order_item ORDER BY EventDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotalPacked,

                        Ordered - SUM(packed_qty) OVER (PARTITION BY customer, product,  order_number, order_item ORDER BY EventDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Outstanding

            FROM vData

            ORDER BY customer, product,  order_number, order_item, EventDate;

             

            • 18. Re: Indentifying outstanding orders
              L. Fernigrini

              If you use Frank's query with the sample data I provided, you get this results:

               

               

              The query works OK assuming you have only 1 shipment per day, and no shipments on the order date. That does not seem to be a logical scenario.

               

              Since the number of shipments is not limited, each order item may have different number of columns, and that is not possible. You need to do the pivoting on the presentation layer if you require data to be shown in columns rather than rows.

              • 19. Re: Indentifying outstanding orders
                Frank Kulash

                Hi

                user5716448 wrote:

                 

                Hi,

                 

                Re

                Is there an upper limit on the number of dates for a given order?

                 

                No upper limit in theory as depends on source system - if product on sale for years could be an order every day, some products on sale short period of time

                 

                Do you really need separate columns for each date, or would one gigantic VARCHAR2 column (formatted so it looks like separate columns for separate dates) be good enough?

                 

                Ideally separate column

                As Fernigrini said in reply #15, you should try to do the pivoting in your front end.

                If you must do it using dynamic SQL, here's one way:

                WITH    combined_data (product, customer, order_number, a_date, order_qty, packed_qty)    AS

                (

                    SELECT  product, customer, order_number, date_created

                    ,       item_quantity

                    ,       0

                    FROM    orders

                UNION ALL

                    SELECT  product, NULL, order_number, date_packed

                    ,       0

                    ,       packed_qty

                    FROM    packed_orders

                )

                ,    got_cumulative_totals  (product, customer, order_number, a_date, order_qty, packed_qty)  AS

                (

                    SELECT    product

                    ,         LAST_VALUE (customer  IGNORE NULLS)

                                               OVER ( PARTITION BY  product, order_number

                                                      ORDER BY      a_date

                                                    )  AS customer

                    ,         order_number, a_date

                    ,         SUM (order_qty)  OVER ( PARTITION BY  product, order_number

                                                      ORDER BY      a_date

                                                    )  AS order_qty

                    ,         SUM (packed_qty) OVER ( PARTITION BY  product, order_number

                                                      ORDER BY      a_date

                                                    )  AS packed_qty

                    FROM      combined_data

                )

                SELECT    *

                FROM      got_cumulative_totals

                PIVOT     (    SUM (order_qty)               AS orders

                          ,    SUM (order_qty - packed_qty)  AS outstanding

                          FOR  a_date  IN ( TO_DATE ('14/11/2019','DD/MM/YYYY')  AS d_14_11_2019

                                          , TO_DATE ('15/11/2019','DD/MM/YYYY')  AS d_15_11_2019

                                          , TO_DATE ('16/11/2019','DD/MM/YYYY')  AS d_16_11_2019

                     )

                          )

                ORDER BY  customer, product

                ;

                The lines in the PIVOT ... FOR clause are all that need to be made dynamic.

                 

                The results I get from your sample data are:

                                                    D_14_11         D_15_11         D_16_11

                                                      _2019           _2019           _2019

                                            D_14_11    _OUT D_15_11    _OUT D_16_11    _OUT

                                 ORDER        _2019   STAND   _2019   STAND   _2019   STAND

                PRODUCT CUSTOMER _NUMBER    _ORDERS     ING _ORDERS     ING _ORDERS     ING

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

                A       ANYONE   100             40      40      40      20      40       0

                Your sample data and results don't include any examples of multiple shipments on the same day, or on the same day as an order.  If the query above isn't doing exactly what you want in those cases, then it probably only needs minor adjustments.

                1 person found this helpful
                • 20. Re: Indentifying outstanding orders
                  user5716448

                  Thanks for response - looks great and works.

                   

                  For my own understanding wonder if could you explain significance of below and what this does?

                   

                  ORDER BY EventDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

                   

                  Thanks again.

                  • 21. Re: Indentifying outstanding orders
                    user5716448

                    Thanks for reply.

                     

                    Yes agree - will do pivoting at front end now have the sql but good to know how could do it in sql for reference.

                    • 22. Re: Indentifying outstanding orders
                      L. Fernigrini

                      Here is a brief explanation, I will try to keep it simple

                       

                      SUM() is both an Aggregate and Analytical function. As an aggregate I used it on the vData CTE.

                       

                      But in the next CTE I used it as an analytical function. The main difference is that as an aggregate, SUM returns a single value for each group by expression (3 rows on the source table are converted to 1 "summary" row returned by the query) but as an analytical function it calculates the SUM but does not aggregate the rows into a single summary row.

                       

                      The "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" tells the SUM function to take into account all previous rows (UNBOUNDED PRECEDING) up to the actual one (CURRENT ROW), since when I am on a shipment from November 15 I do not want to sum the shipments from November 16 or 17.

                       

                      If you remove that section, all rows will show 40 since is the sum of all shipments, and not all shipments up to current date (that is usually called "running totals").

                       

                      Here is a great explanation on SUM

                      https://oracle-base.com/articles/misc/sum-analytic-function

                      1 person found this helpful
                      • 23. Re: Indentifying outstanding orders
                        user5716448

                        Thanks again for helpful explanation

                        1 2 Previous Next