I am new to stored procedure and I am trying to integrate my query with the existing stored procedure. My stored procedure displays the orderID that are not processed and to display this it checks the value of FillInfo column in Order table whether it is null or not. Joining is done between Order and OrderDetails table based on OrderID
Here is my stored procedure.
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 UnProcessed 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
The problem with the above stored procedure is "It doesn't check whether all the quantities for particular OrderId is processed or not."So even if 1 quantity is processed out of 5, that OrderID will not be displayed in unprocessed orders.The processed quantities for particular orderID is present in PurchaseOrderDetails table. To avoid the above problem, my query is
select od.orderID, pod.pod_qty as Totalqty, od.od_qty from ( select orderid, sum(pod.qty) as pod_qty from PurchaseOrderDetails pod inner join PurchaseOrders po on po.poID = pod.poID and po.status!='VOID' group by orderid ) pod inner join ( select orderid, sum(od.qty) as od_qty from OrderDetails od group by orderid ) od on od.orderid = pod.orderid and pod.pod_qty<od.od_qty ;
This query is to check for quantity, the total quantities for particular order in orderDetails should not be equal to sum of quantities in PurchaseOrderDetails table which contains the processed quantities for particular orderID and I want to integrate this query with my above stored procedure so that it will work based on both the fillInfo column and even based on quantity processed.
My Order table is
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 [Orders]
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 [OrderDetails]
SELECT TOP 1000 [podID] ,[poID] ,[distPN] ,[mfrID] ,[itemNumber] ,[itemName] ,[qty] ,[price] ,[invoiceID] ,[orderID] ,[itemReceived] ,[qtyReceived] ,[distConf] ,[distFillStatus] ,[distLastUpdate] ,[distTracking] ,[distFillDesc] ,[Version] ,[productid] ,[lineOrderID] FROM [PurchaseOrderDetails]