I am new to database and trying out some stuff with stored procedure.I am using 3tbales in my stored procedure - Customer, OrderDetails and Order. I am displaying all the unprocessed orders based on the quantity field of orderdetails table.Whenever, the particular order is processed, in my OrderDetail table there is fillInfo field where multiple purchaseOrder number gets inserted from Order table.The data is being displayed but as soon as even 1 quantity is processed then the data related to that particular order disappears.Suppose, if customer orders 10 quantities in 1 order and if I process 2 of them, then that order should be still be displayed in unprocessed orders list because 8 quantities is still remaining to be delivered to customer.
My orderdetail table looks like
SELECT TOP 1000 [lineID] ,[orderID] ,[itemNumber] ,[itemDesc] ,[qty] ,[price] ,[priceType] ,[contract] ,[supplierPartID] ,[uom] ,[classDomain] ,[classValue] ,[comments] ,[shipping] ,[tax] ,[reqDeliveryDate] ,[fillInfo] ,[invoiceID] ,[Version] ,[productid] ,[eta] ,[lineOrderID] FROM [bfdb].[dbo].[OrderDetails]
here orderid comes from order table where order table looks like
SELECT TOP 1000 [orderID] ,[customerID] ,[billAddressID] ,[shipAddressID] ,[orderDate] ,[employeeID] ,[salesTax] ,[poNumber] ,[dueDate] ,[terms] ,[shipMethod] ,[shippingFee] ,[shippingNotes] ,[oStatus] ,[createDate] ,[source] ,[comments] ,[cardType] ,[cardNumber] ,[expMonth] ,[expYear] ,[nameOnCard] ,[cvvCode] ,[cancelDate] ,[total] ,[url] ,[role] ,[consolidationPoint] ,[cxmlData] ,[authStatus] ,[authCode] ,[transactionID] ,[authDateTime] ,[avsResponse] ,[cvvResponse] ,[Version] ,[couponCode] ,[discountAmt] ,[cageCode] ,[awardID] ,[versionNumber] ,[invoiceEmail] ,[trackingEmail] FROM [bfdb].[dbo].[Orders]
1 orderid can have multiple item number with large quantities for same purchase order
I have attached the snapshot of my stored procedure result
SELECT o.orderID, o.orderDate, o.oStatus, e.userName, o.poNumber, c.customerName, c.company, o.source, ( (SELECT SUM(od.price * od.qty) FROM OrderDetails od WHERE o.orderID = od.orderID) + o.salesTax + o.shippingFee ) As Total, ((SELECT COUNT(od.orderID) FROM OrderDetails od WHERE o.orderID = od.orderID) - (SELECT COUNT(od.orderID) FROM OrderDetails od WHERE o.orderID = od.orderID AND (fillInfo != '' OR fillInfo != null)) ) As Filled FROM Orders o JOIN Customers c on o.customerID = c.customerID JOIN Employees e on o.employeeID = e.employeeID WHERE (o.employeeID>0 AND o.oStatus='Active' AND (((SELECT COUNT(od.orderID) FROM OrderDetails od WHERE o.orderID = od.orderID) - (SELECT COUNT(od.orderID) FROM OrderDetails od WHERE o.orderID = od.orderID AND (fillInfo != '' OR fillInfo != null)) ) > 0)) ORDER BY o.orderID
There is filled column in stored procedure which shows the total quantities unprocessed . But still I am not able to display orders for the remaining quantities because as soon as even 1 quantity is processed for particular order, the data from unprocessed list goes away. The data in the unprocessed list is displayed from above stored procedure.