Re: Database Design Question

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 3 Jun 2003 14:46:33 -0700
Message-ID: <c0d87ec0.0306031346.7019682a@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 + @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 - @new_stock_qty)) >= 0
                           THEN (Orders.qty_ordered
-(R1.tot_qty_ordered - @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 - 16:46:33 CDT