Integration of Query with existing Stored Procedure

Transact-SQL
, , ,
1

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]

OrderDetails table

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]

PurchaseOrderDetails table

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]
2

Since no one has solved it for you yet...
Warning - I've not taken the time to create tables, run this, test it or tune it in any way. Just thrown it together from your original queries.

select od.orderID, o.orderDate, o.oStatus, e.userName, o.poNumber, c.customerName, c.company, o.source,
pod.pod_qty as 'PodQty', od.od_qty as 'OdQty', (od.TotPrice + o.salesTax + o.shippingFee) as 'Total', unp.Unprocessed
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, SUM(od.price * od.qty) 'TotPrice'
from OrderDetails od
group by orderid
) od on od.orderid = pod.orderid and pod.pod_qty < od.od_qty
inner join Orders o on o.orderid = od.orderid
inner join Customers c on o.customerID = c.customerID
inner join Employees e on o.employeeID = e.employeeID
inner join (
select od.orderID, COUNT(od.orderID) 'Unprocessed'
from OrderDetails od
where ISNULL(od.fillInfo,'')='' -- I assume this is what you meant?
group by od.orderid
) unp on unp.orderID = od.orderID
where unp.Unprocessed > 0