1 2 Previous Next 23 Replies Latest reply on Nov 15, 2019 12:27 PM by user5716448

    Indentifying outstanding orders

    user5716448

      Hi,

       

      Have following scenario

       

      Oracle 11gr2

       

      1 table has orders

       

      order_number

      order_item

      customer

      product

      order_date

      order_qty

       

      Another table has packed_orders

      This just has

      order_number

      order_item

      packed_date

       

      tables linked on order item and order number

       

      Want to determine the number of orders and outstanding orders on each day

       

      an order can be packed on different day to the order and can be packed across multiple days

       

      e.g if have customer a for product b with an order created 14/11/2019 for qty of 40 which is packed 20  on 15/11/2019 and 20 on 16/11/2019

      would want 3 rows which shows current position on each day

      on 14/11/2019 order qty = 40, packed_qty = 0

           15/11/2019 order_qty=40, packed_qty = 20

           16/11/2019 order_qty= 40, packed_qty =40

       

      How can this best be achieved in sql?

       

      Thanks

        • 1. Re: Indentifying outstanding orders
          John Thorton

          user5716448 wrote:

           

          Hi,

           

          Have following scenario

           

          Oracle 11gr2

           

          1 table has orders

           

          order_number

          order_item

          customer

          product

          order_date

          order_qty

           

          Another table has packed_orders

          This just has

          order_number

          order_item

          packed_date

           

          tables linked on order item and order number

           

          Want to determine the number of orders and outstanding orders on each day

           

          an order can be packed on different day to the order and can be packed across multiple days

           

          e.g if have customer a for product b with an order created 14/11/2019 for qty of 40 which is packed 20 on 15/11/2019 and 20 on 16/11/2019

          would want 3 rows which shows current position on each day

          on 14/11/2019 order qty = 40, packed_qty = 0

          15/11/2019 order_qty=40, packed_qty = 20

          16/11/2019 order_qty= 40, packed_qty =40

           

          How can this best be achieved in sql?

           

          Thanks

          Please click on URL below & provide details as stated in #5 - #9 inclusive

           

          How do I ask a question on the forums?


          • 2. Re: Indentifying outstanding orders
            EdStevens

            user5716448 wrote:

             

            Hi,

             

            Have following scenario

             

            Oracle 11gr2

             

            1 table has orders

             

            order_number

            order_item

            customer

            product

            order_date

            order_qty

             

            Another table has packed_orders

            This just has

            order_number

            order_item

            packed_date

             

            tables linked on order item and order number

             

            Want to determine the number of orders and outstanding orders on each day

             

            an order can be packed on different day to the order and can be packed across multiple days

             

            e.g if have customer a for product b with an order created 14/11/2019 for qty of 40 which is packed 20 on 15/11/2019 and 20 on 16/11/2019

            would want 3 rows which shows current position on each day

            on 14/11/2019 order qty = 40, packed_qty = 0

            15/11/2019 order_qty=40, packed_qty = 20

            16/11/2019 order_qty= 40, packed_qty =40

             

            How can this best be achieved in sql?

             

            Thanks

            why isn't PACKED_DATE simply another column (property) of the ORDERS table?

             

            This gives the appearance of being a homework assignment.  In the real world, You'd have one table for orders (ORDER_HEADERS) and second table for order line items (ORDER_LINE_ITEMS).  The product information would be in the ORDER_LINE_ITEMS table.  The way you have it, you could only ever have one item per order.

             

            Many, many SQL difficulties are due to flawed data design.

            • 3. Re: Indentifying outstanding orders
              user5716448

                   Thanks for replies.

               

              Not my table design  - part of another system with data being fed to us this way and want to create report based on it.

               

              version 11.2.0.3

               

              create table packed_orders

              (

                product     VARCHAR2(18) not null,

                order_number     VARCHAR2(10) not null,

                order_item       VARCHAR2(6) not null,

                packed_qty       NUMBER,

                date_packed   date

               

              );

               

               

              create table orders

              (

                product       VARCHAR2(18) not null,

                customer         VARCHAR2(10) not null,

                order_number     VARCHAR2(10) not null,

                order_item       VARCHAR2(6) not null,

                date_created     DATE,

                item_quantity    NUMBER(6)

              );

               

               

               

               

              insert into orders

              values

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

               

               

              insert into packed_orders

              values

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

               

               

              insert into packed_orders

              values

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

               

               

              wanting to determine the number of orders and packed_orders on a running total basis e.g. based on above would want, if possible below.

               

              14/11/2019 order qty = 40, packed_qty = 0

              15/11/2019 order_qty=40, packed_qty = 20

              16/11/2019 order_qty= 40, packed_qty =40

              • 4. Re: Indentifying outstanding orders
                BluShadow

                EdStevens wrote:

                 

                user5716448 wrote:

                 

                Hi,

                 

                Have following scenario

                 

                Oracle 11gr2

                 

                1 table has orders

                 

                order_number

                order_item

                customer

                product

                order_date

                order_qty

                 

                Another table has packed_orders

                This just has

                order_number

                order_item

                packed_date

                 

                tables linked on order item and order number

                 

                Want to determine the number of orders and outstanding orders on each day

                 

                an order can be packed on different day to the order and can be packed across multiple days

                 

                e.g if have customer a for product b with an order created 14/11/2019 for qty of 40 which is packed 20 on 15/11/2019 and 20 on 16/11/2019

                would want 3 rows which shows current position on each day

                on 14/11/2019 order qty = 40, packed_qty = 0

                15/11/2019 order_qty=40, packed_qty = 20

                16/11/2019 order_qty= 40, packed_qty =40

                 

                How can this best be achieved in sql?

                 

                Thanks

                why isn't PACKED_DATE simply another column (property) of the ORDERS table?

                 

                This gives the appearance of being a homework assignment. In the real world, You'd have one table for orders (ORDER_HEADERS) and second table for order line items (ORDER_LINE_ITEMS). The product information would be in the ORDER_LINE_ITEMS table. The way you have it, you could only ever have one item per order.

                 

                Many, many SQL difficulties are due to flawed data design.

                 

                 

                Packed date shouldn't be in the ORDERS table as an order may contain multiple items that can be packed on different days for different shipments before the whole order is complete.

                However, the table design is still incorrect in that respect because you may order 100 of a particular item, only have 50 in stock, so pack and dispatch those first 50 on one date and when supplied are replenished, then pack and dispatch the other 50 on another day, and then when all items of the order have been dispatched, the order is complete.  So, the PACKED_ORDERS table is completely missing the important bit which is the quantity packed.

                • 5. Re: Indentifying outstanding orders
                  EdStevens

                  I can understand being given a bad data model and having to deal with it, but I can't help but re-emphasize what a flawed data model it is.  If you can't modify the model, the at least file this lesson away for future use when you are in a position to influence the model.  Always design your data to Third Normal Form.  If you've never heard of that, google it.

                   

                  That said . . .

                   

                  You are reporting more orders than your test data provides.

                   

                  And looking at your desired output, it raises other questions.

                   

                  Since your report is 'by day' do you count a packed order on the day the order was created, or the day it was packed?  It seems to only make sense to count it in both, with the understanding that there is no relationship between order_qty and packed_qty for any given date.  So once again we have to say, what is the business problem being addressed by this pre-concieved technique of reporting.  What kind of analysis and decisions are to be made from this report?

                  • 6. Re: Indentifying outstanding orders
                    EdStevens

                    BluShadow wrote:

                     

                    EdStevens wrote:

                     

                    <snip>

                    why isn't PACKED_DATE simply another column (property) of the ORDERS table?

                     

                    This gives the appearance of being a homework assignment. In the real world, You'd have one table for orders (ORDER_HEADERS) and second table for order line items (ORDER_LINE_ITEMS). The product information would be in the ORDER_LINE_ITEMS table. The way you have it, you could only ever have one item per order.

                     

                    Many, many SQL difficulties are due to flawed data design.

                     

                     

                    Packed date shouldn't be in the ORDERS table as an order may contain multiple items that can be packed on different days for different shipments before the whole order is complete.

                    However, the table design is still incorrect in that respect because you may order 100 of a particular item, only have 50 in stock, so pack and dispatch those first 50 on one date and when supplied are replenished, then pack and dispatch the other 50 on another day, and then when all items of the order have been dispatched, the order is complete. So, the PACKED_ORDERS table is completely missing the important bit which is the quantity packed.

                    Good points.  Of course, that is the next step after fixing the most fundamental design flaw .. that of a single item per order.  I honestly don't see how the business operates with that model.

                    • 7. Re: Indentifying outstanding orders
                      user5716448

                      Hi,

                       

                      Desired business outcome is each day to determine the running totals of orders for a product and customer and identify number of orders which have not been packed

                       

                      e.g ultimate report to be similar to below - if can get the sql could create report similar to below.

                       

                      e.g

                                                                  14/11/2019                              15/11/2019                               16/11/2019

                      Customer  Product             Orders, Outstanding                Orders Outstanding                 Orders Outstanding

                      ANYONE   A                       40          40                               40         20                                40        0

                       

                       

                      Thanks

                      • 8. Re: Indentifying outstanding orders
                        Frank Kulash

                        Hi,

                         

                        So, you want a cumulative total of the packed_qty.  That sounds like a job for the analytic SUM function.

                        Here's one way:

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

                        (

                            SELECT  product, order_number, date_created

                            ,       item_quantity

                            ,       0

                            FROM    orders

                        UNION ALL

                            SELECT  product, order_number, date_packed

                            ,       0

                            ,       packed_qty

                            FROM    packed_orders

                        )

                        SELECT    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

                        ORDER BY  product, order_number, a_date

                        ;

                        I'm just guessing at the role order_number and product play in this problem.  There's only one value of each in the sample data, and they don't appear in your desired output.

                        1 person found this helpful
                        • 9. Re: Indentifying outstanding orders
                          BEDE

                          I can't help giving some you some idea of how a data model should be for such an application, based on what I've seen in some ERP systems (not school assignments).

                          A table order_master having, amount others, columns: id (primary key), order_date, invoice_date or invoice_id or both columns (invoice_id being eventually foreign key to invoice table), shipment_date.

                          A table order_items, having lines for the items and order_id column foreign key to order_master. In order_items there should also be column quantity_delivered, for you may deliver less than the quantity ordered. And you may possibly have several deliveries for the same order, admitting that, for some reasons, you can't deliver in one go all that is on that order...

                          Then, there is the problem of invoicing: when you invoice and, if you have several deliveries for the same order, you may then possibly have also several invoices, one invoice per delivery. Or, you may simplify things and have only one delivery per order and only one invoice per order, but, even so, you must have quantity_accepted for each item, for it is possible that some items you may not have in stock for the time being or you have had them but you are certain you will not have them anytime soon, although you had some on stock at the time the order was placed.

                          • 10. Re: Indentifying outstanding orders
                            user5716448

                            Thanks for reply.

                             

                            However, looking for outstanding orders by product and customer rather than just product and customer only on the orders table.

                             

                            How can we best handle this?

                            • 11. Re: Indentifying outstanding orders
                              Frank Kulash

                              Hi,

                              user5716448 wrote:

                               

                              Hi,

                               

                              Desired business outcome is each day to determine the running totals of orders for a product and customer and identify number of orders which have not been packed

                               

                              e.g ultimate report to be similar to below - if can get the sql could create report similar to below.

                               

                              e.g

                              14/11/2019 15/11/2019 16/11/2019

                              Customer Product Orders, Outstanding Orders Outstanding Orders Outstanding

                              ANYONE A 40 40 40 20 40 0

                               

                               

                              Thanks

                              In reply #3, you said you wanted 3 rows, with 3 columns from the sample data.

                              In reply #7, you said you wanted 1 row with 8 columns.

                              Please don't change the requirements in the middle of a thread like that; it can waste a lot of time and effort.  Decide what you want before posting a question, and say clearly what that is.

                               

                              Reply #8 shows how you can get the right totals, with a separate row for every date.

                              You can use SELECT … PIVOT on those results to get multiple dates on the same row

                              As always, the exact number of columns in the output, and their names, must be hard-coded into the query.  To get a variable number of columns (e.g., 8 columns if you happen to have 3 dates, 6 columns if you only have 2 dates) or variable names (e,g, including "11/14/2019" in the column name) requires dynamic SQL.

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

                              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?

                              See Re: Please Help in query

                              • 12. Re: Indentifying outstanding orders
                                Frank Kulash

                                Hi,

                                user5716448 wrote:

                                 

                                Thanks for reply.

                                 

                                However, looking for outstanding orders by product and customer rather than just product and customer only on the orders table.

                                 

                                How can we best handle this?

                                Sorry, I don't understand what the problem is.

                                Point out where the query in reply #8 is getting the wrong results from the given sample data, and explain how you figure the right results in those places.  (That is, aside from pivoting.)

                                • 13. Re: Indentifying outstanding orders
                                  user5716448

                                  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

                                  • 14. Re: Indentifying outstanding orders
                                    user5716448

                                    Hi,

                                     

                                    looking to get the outstanding orders for a customer and product on each day

                                     

                                    ran your query and whilst shows results by date, looking for further breakdown by

                                     

                                    appreciate can use pivot as per your other post but wonder if possible to adapt your query to also to include customer?

                                     

                                    e.g

                                                                                14/11/2019                              15/11/2019                               16/11/2019

                                    Customer  Product             Orders, Outstanding                Orders Outstanding                 Orders Outstanding

                                    ANYONE   A                       40          40                               40         20                                40        0

                                     

                                    Thanks

                                    1 2 Previous Next