Re: Database Design Question

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 3 Jun 2003 14:46:33 -0700
Message-ID: <c0d87ec0.0306031346.7019682a_at_posting.google.com>

>> A customer can place multiple orders for varying quantities of
 the same item. e.g.

 Order # 1001 Quantity = 1.
 Order #1002 Quantity = 2.
 Order #1003 Quantity = 5

• total of 8 on order. <<

I am a bit confused about what an Order is, since your sample data shows separate id numbers for three orders.

CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,  customer_id CHAR(12) NOT NULL

            REFERENCES Customers(customer_id),  upc CHAR(10) NOT NULL REFERENCES Inventory(upc)

              ON UPDATE CASCADE,
 order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,  qty_ordered INTEGER NOT NULL,
 qty_shipped INTEGER NOT NULL,
 CHECK (qty_ordered <= qty_shipped), -- no overshipping  UNIQUE (customer_id, order_date), -- one order per timeslot   ...);  

INSERT INTO Orders

VALUES ('Fred', 1001, '1234567890', '2003-01-01', 1, 0),
       ('Fred', 1002, '1234567890', '2003-01-02', 2, 0),
       ('Fred', 1003, '1234567890', '2003-01-03', 5, 0),
       ('Fred', 1004, '1234567890', '2003-01-04', 6, 0);

>> The goods are invoiced but for availability or whatever reasons
only 6 of
this particular item are invoiced, which leaves 2 still on order ..<<

Create a VIEW of running totals

CREATE VIEW RunningTotals(customer_id, upc, order_date, tot_qty_ordered, tot_qty_shipped)
AS SELECT O1.customer_id, O1.upc, O1.order_date,

          SUM(O2.qty_ordered), SUM(O2.tot_qty_shipped)
     FROM Orders AS O1, Orders AS O2
    WHERE O1.customer_id = O2.customer_id
      AND O1.upc = O2.upc
      AND O1.order_date >= O2.order_date
      AND O1.qty_ordered < 01.qty_shipped
      AND O2.qty_ordered < 02.qty_shipped;

Tirm this into a update statement

SELECT Orders.*,
CASE SIGN ((SELECT tot_qty_ordered - (tot_qty_shipped + _at_new_stock_qty)

                      FROM RunningTotals AS R1
                     WHERE R1.customer_id = Orders.customer_id
                       AND R1.upc = Orders.upc
                       AND R1.order_date = Orders.order_date))
         WHEN 0 THEN Orders.qty_ordered  -- exactly filled order
         WHEN -1 THEN Orders.qty_ordered -- more stock than order
         WHEN +1 THEN 
                   (SELECT CASE 
                           WHEN(Orders.qty_ordered
-(R1.tot_qty_ordered - _at_new_stock_qty)) >= 0
                           THEN (Orders.qty_ordered
-(R1.tot_qty_ordered - _at_new_stock_qty))
                           ELSE Orders.qty_shipped END AS new_shipped
                      FROM RunningTotals AS R1
                     WHERE R1.customer_id = Orders.customer_id
                       AND R1.upc = Orders.upc
                       AND R1.order_date = Orders.order_date)
        END 

FROM Orders
 WHERE customer_id = 'Fred'
   AND upc = '1234567890';

It is a little messy, but I don't have time right now to clean it up. Received on Tue Jun 03 2003 - 23:46:33 CEST