In a previous thread I had a question about splitting up an order that is due on different dates. robvolk came through big time and helped me with a perfect solution.This is sorta a part two to that. When these orders are shipped, the shipper doesn't care as much about when the parts are due. Just how many parts they are going to ship. The only info the shipper has is the PO Number of the order, the part number, and the qty they are shipping.So what I need to do is write a sproc that will take a PO Number, a Part number, and a quantity. This sproc with then update the column qty shipped. It will fill the portion of the order that is due next, or the part of the order that is the most past due. Past due portions take precidence and all must be filled before the upcoming portions should be filled. If each scheduled shipment is 100 pcs and 350 pcs are sent, this sproc should fill the first three and then 50 of the next order.Because all portions may not be completely filled, I guess it also has to check if a portion has already been shipped and only ship the remainder. If more pcs are shipped than are remaining in an entire order, the remainder should be placed on the last portion. (You can ship more than the amount due only on the last portion.)Here is the code for the table I'm using CREATE TABLE [tblBookings_Parts] ( [bpID] [int] IDENTITY (1, 1) NOT NULL , [orderID] [int] NULL , [partID] [int] NOT NULL , [bpQty] [int] NOT NULL , [bpPricePerPart] [money] NULL , [dueDate] [datetime] NOT NULL , [bpComments] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [amountShipped] [int] NOT NULL CONSTRAINT [DF_tblBookings_Parts_amountShipped] DEFAULT (0),)
I'm thinking that I'm going to have to use CASE statements, but I'm not sure how to handle cases where more than any given portion of the order are exceeded. Maybe someone can just point me in the right direction! Thanks! 
Shipping Those Recurring Orders
Posted - 2001-10-29 : 17:46:28