CODEWITHSUNDEEP
sqlsql-server
Josh Kirkpatrick
Josh Kirkpatrick

Reputation: 403

Filtering results in SQL based on multiple factors

I have a query in SQL server 2008 that pulls order data from Sage. This query just pulls current orders and displays if they are being collected and have been dispatched. Here is the query 

SELECT SOPOrderReturn.DocumentNo,
                   CONVERT(date, SOPOrderReturn.DocumentDate) AS DocumentDate,
                   SLCustomerAccount.CustomerAccountName,
                   CONVERT(Date, SOPOrderReturn.RequestedDeliveryDate) AS RequestedDeliveryDate,
                   SOPDocDelAddress.PostalName,
                   POPOrderReturn.SupplierDocumentNo,
                   PLSupplierAccount.SupplierAccountName,
                   SOPDespatchReceiptType.Name
            FROM SOPOrderReturn
            INNER JOIN SLCustomerAccount ON SOPOrderReturn.CustomerID = SLCustomerAccount.SLCustomerAccountID
            INNER JOIN SOPDocDelAddress ON SOPOrderReturn.SOPOrderReturnID = SOPDocDelAddress.SOPOrderReturnID
            LEFT OUTER JOIN SOPDespatchReceiptType
            INNER JOIN SOPDespatchReceipt ON SOPDespatchReceiptType.SOPDespatchReceiptTypeID = SOPDespatchReceipt.SOPDespatchReceiptTypeID ON SOPOrderReturn.SOPOrderReturnID = SOPDespatchReceipt.SOPOrderID
            LEFT OUTER JOIN PLSupplierAccount
            INNER JOIN POPOrderReturn ON PLSupplierAccount.PLSupplierAccountID = POPOrderReturn.SupplierID ON RIGHT(SOPOrderReturn.DocumentNo, 6) = POPOrderReturn.SupplierDocumentNo
            GROUP BY SOPOrderReturn.DocumentDate,
                     SLCustomerAccount.CustomerAccountName,
                     SOPOrderReturn.DocumentNo,
                     SLCustomerAccount.AccountIsOnHold,
                     SOPOrderReturn.RequestedDeliveryDate,
                     SOPDocDelAddress.PostalName,
                     POPOrderReturn.SupplierDocumentNo,
                     PLSupplierAccount.SupplierAccountName,
                     SOPDespatchReceiptType.Name
            HAVING (SLCustomerAccount.CustomerAccountName <> 'Cash Sales')
            AND (SLCustomerAccount.CustomerAccountName <> 'Staff Purchases')
            AND (SLCustomerAccount.CustomerAccountName <> 'Samples')
            ORDER BY RequestedDeliveryDate

The results of this query look like this :

DocumentNo  DocumentDate    CustomerAccountName RequestedDeliveryDate   PostalName  SupplierDocumentNo  SupplierAccountName Name
233510      03/03/2015      CustomerName            03/03/2015         PostalName           NULL            NULL            NULL
233497      02/03/2015      CustomerName            03/03/2015          Collection          NULL            NULL            Despatch Note
233507      03/03/2015      CustomerName            10/03/2015          PostalName          NULL            NULL            Despatch Note 
233503      03/03/2015      CustomerName            03/03/2015          PostalName          NULL            NULL            NULL
233504      03/03/2015      CustomerName            03/03/2015          PostalName          NULL            NULL            NULL
233509      03/03/2015      CustomerName            03/03/2015          PostalName          NULL            NULL            NULL
233478      02/03/2015      CustomerName            04/03/2015          Collection          NULL            NULL            NULL
233501      02/03/2015      CustomerName            04/03/2015          PostalName          233501          name            Despatch Note
233462      27/02/2015      CustomerName            04/03/2015          PostalName          233462          name            Despatch Note
233499      02/03/2015      CustomerName            05/03/2015          Collection          NULL            NULL            NULL
233431      25/02/2015      CustomerName            06/03/2015          Collection          NULL            NULL            NULL
233434      25/02/2015      CustomerName            06/03/2015          PostalName          NULL            NULL            NULL
233506      03/03/2015      CustomerName            09/03/2015          PostalName          NULL            NULL            NULL
233513      04/03/2015      CustomerName            10/03/2015          PostalName          NULL            NULL            Despatch Note
233507      03/03/2015      CustomerName            10/03/2015          PostalName          NULL            NULL            Despatch Note
233446      26/02/2015      CustomerName            10/03/2015          PostalName          NULL            NULL            Despatch Note   
233488      02/03/2015      CustomerName            16/03/2015          PostalName          NULL            NULL            NULL
232975      30/01/2015      CustomerName            23/03/2015          PostalName          NULL            NULL            NULL

different shipment methods

Collection = Anything with a PostalName = Collection regardless of any other fields

Hauiler = Anything with SupplierDocumentNo  that IS NOT null 

Delivery by us = Any PostalName that is not Collection and SupplierDocumentNo   is null.

So what i need to filter out of the results orders that are being shipped by us and have been delivered, We can tell this by if in the Name field it is equal to Despatch Note

so Row three of the result set is being deliverd by us as PostalName is not Collection and SupplierDocumentNo IS NUll, And Name = Despatch Note

Row one however is also being shipped by us but Name != Despatch Note so it has not been shipped, so this needs to still be in the result set

Here is what the result set should look like 

    DocumentNo  DocumentDate    CustomerAccountName RequestedDeliveryDate   PostalName  SupplierDocumentNo  SupplierAccountName Name
233510      03/03/2015      CustomerName            03/03/2015         PostalName           NULL            NULL            NULL
233497      02/03/2015      CustomerName            03/03/2015          Collection          NULL            NULL            Despatch Note
Row filtered out
233503      03/03/2015      CustomerName            03/03/2015          PostalName          NULL            NULL            NULL
233504      03/03/2015      CustomerName            03/03/2015          PostalName          NULL            NULL            NULL
233509      03/03/2015      CustomerName            03/03/2015          PostalName          NULL            NULL            NULL
233478      02/03/2015      CustomerName            04/03/2015          Collection          NULL            NULL            NULL
233501      02/03/2015      CustomerName            04/03/2015          PostalName          233501          name            Despatch Note
233462      27/02/2015      CustomerName            04/03/2015          PostalName          233462          name            Despatch Note
233499      02/03/2015      CustomerName            05/03/2015          Collection          NULL            NULL            NULL
233431      25/02/2015      CustomerName            06/03/2015          Collection          NULL            NULL            NULL
233434      25/02/2015      CustomerName            06/03/2015          PostalName          NULL            NULL            NULL
233506      03/03/2015      CustomerName            09/03/2015          PostalName          NULL            NULL            NULL
Row filtered out
Row filtered out
Row filtered out
233488      02/03/2015      CustomerName            16/03/2015          PostalName          NULL            NULL            NULL
232975      30/01/2015      CustomerName            23/03/2015          PostalName          NULL            NULL            NULL

Upvotes: 0

Views: 68

Answers (1)

Pinx0
Pinx0

Reputation: 1258

Add the following WHERE CLAUSE:

WHERE PostalName = 'Collection' OR SupplierDocumentNo IS NOT NULL OR Name != 'Despatch Note'

Just before the GROUP BY.

Upvotes: 1

Related Questions