Hi,
I have a dataset with the order number and another one with all items in those orders. Some of those items are a comment stating that the thing above is just a corrective invoice. I need to exclude the whole order containing comments like that. The problem is that the code I wrote, is only excluding the comment, but the item stays as it was, showing value. How to exclude the whole order if any of the items in this order are containing a specific keyword?
Example:
Order Number Database:
DocumnetNo
1111111
Items in this order
DocumnetNo | ItemName | LineTotalValue
1111111 | Shampoo 200ml | £9999
1111111 | Wrong invoice sent. | £0
What I have tried:
The code below excludes the item with the comment but the item without the keyword is causing the DocumentNo not to be entirely excluded.
My code so far:
SELECT
'US' AS Region,
T1.CustomerDocumentNo,
T1.SOPOrderReturnID,
T1.DocumentNo,
T2.LineTotalValue,
T2.LineQuantity,
T2.SOPOrderReturnLineID,
T1.DocumentDate,
T1.RequestedDeliveryDate,
T1.PromisedDeliveryDate,
TotalNetValue AS OrderNetValue,
TotalGrossValue AS OrderGrossValue,
TotalTaxValue AS OrderTaxValue,
T1.DocumentDueDate,
T1.DateTimeCreated,
T4.Name,
T3.CustomerAccountName,
T3.SLCustomerAccountID,
T2.ItemCode,
T2.ItemDescription,
T5.StockItemTypeName,
T6.SOPOrderReturnTypeName,
T7.ItemName
FROM
SOPOrderReturn AS T1
JOIN SOPOrderReturnLine AS T2 ON
T1.SOPOrderReturnID=T2.SOPOrderReturnID
JOIN SLCustomerAccount AS T3 ON
T1.CustomerID=T3.SLCustomerAccountID
JOIN DocumentStatus AS T4 ON
T1.DocumentStatusID=T4.DocumentStatusID
JOIN StockItemType AS T5 ON
T2.StockItemTypeID=T5.StockItemTypeID
JOIN SOPOrderReturnType AS T6 ON
T6.SOPOrderReturnTypeID=T1.DocumentTypeID
LEFT JOIN SOPInvoiceCreditLine AS T7 ON
T7.SOPOrderReturnLineID=T2.SOPOrderReturnLineID
WHERE DocumentNo NOT IN (SELECT ItemName
FROM SOPInvoiceCreditLine
WHERE ItemName LIKE'%Invoice%')