Try this query:

SELECT SOPOrderReturn.DocumentNo, SOPOrderReturn.DocumentDate, SOPOrderReturn.DocumentTypeID, SOPOrderReturn.DocumentStatusID, SOPOrderReturnLine.ItemCode, SOPOrderReturnLine.ItemDescription,
CustomerAccountName, LineQuantity, UnitSellingPrice, SOPOrderReturnLine.LineTotalValue, SOPOrderReturn.CustomerDocumentNo,
SourceDocumentNo,
SOPOrderReturn.AnalysisCode3 [LostReason],
CASE WHEN SOPOrderReturn.DocumentStatusID = 2 THEN 'Won'
ELSE CASE WHEN SOPOrderReturn.AnalysisCode9 <> 'Pending' AND SOPOrderReturn.AnalysisCode9 <> ''
THEN CASE WHEN SOPOrderReturn.AnalysisCode9 = 'WON' OR SOPOrderReturn.AnalysisCode9 = 'LOST'
THEN SOPOrderReturn.AnalysisCode9
ELSE 'In Progress' END
ELSE CASE WHEN SOPOrderReturn.AnalysisCode3 = 'Not Applicable'
THEN 'In Progress'
ELSE 'Lost' END END END [QuoteStatus],
SOPOrderReturn.AnalysisCode6 [ContractRef],
DocumentCreatedBy,
SOPOrderReturn.AnalysisCode4 [Notes],
Warehouse.Name, SOPInvCredDelAddress.PostCode
FROM SLCustomerAccount
INNER JOIN SOPOrderReturn ON SLCustomerAccountID = CustomerID
LEFT JOIN Warehouse ON Warehouse.WarehouseID = SOPOrderReturn.WarehouseID
INNER JOIN SOPOrderReturnLine ON SOPOrderReturnLine.SOPOrderReturnID = SOPOrderReturn.SOPOrderReturnID
LEFT OUTER JOIN SOPStandardItemLink ON SOPStandardItemLink.SOPOrderReturnLineID = SOPOrderReturnLine.SOPOrderReturnLineID
LEFT OUTER JOIN SOPInvoiceCreditLine on SOPInvoiceCreditLine.SOPOrderReturnLineID = SOPOrderReturnLine.SOPOrderReturnLineID
LEFT OUTER JOIN SOPInvoiceCredit ON SOPInvoiceCredit.SOPInvoiceCreditID = SOPInvoiceCreditLine.SOPInvoiceCreditID
LEFT OUTER JOIN SOPInvCredDelAddress ON SOPInvCredDelAddress.SOPInvoiceCreditID = SOPInvoiceCredit.SOPInvoiceCreditID
WHERE SOPOrderReturn.DocumentTypeID = 3 OR SOPOrderReturn.DocumentTypeID = 2 OR SOPOrderReturn.DocumentTypeID = 0 OR SOPOrderReturn.DocumentTypeID = 1
ORDER BY DocumentNo DESC

Try this query:

SELECT SOPOrderReturn.DocumentNo, SOPOrderReturn.DocumentDate, SOPOrderReturn.DocumentTypeID, SOPOrderReturn.DocumentStatusID, SOPOrderReturnLine.ItemCode, SOPOrderReturnLine.ItemDescription,
CustomerAccountName, LineQuantity, UnitSellingPrice, SOPOrderReturnLine.LineTotalValue, SOPOrderReturn.CustomerDocumentNo,
SourceDocumentNo,
SOPOrderReturn.AnalysisCode3 [LostReason],
CASE WHEN SOPOrderReturn.DocumentStatusID = 2 THEN 'Won'
ELSE CASE WHEN SOPOrderReturn.AnalysisCode9 <> 'Pending' AND SOPOrderReturn.AnalysisCode9 <> ''
THEN CASE WHEN SOPOrderReturn.AnalysisCode9 = 'WON' OR SOPOrderReturn.AnalysisCode9 = 'LOST'
THEN SOPOrderReturn.AnalysisCode9
ELSE 'In Progress' END
ELSE CASE WHEN SOPOrderReturn.AnalysisCode3 = 'Not Applicable'
THEN 'In Progress'
ELSE 'Lost' END END END [QuoteStatus],
SOPOrderReturn.AnalysisCode6 [ContractRef],
DocumentCreatedBy,
SOPOrderReturn.AnalysisCode4 [Notes],
Warehouse.Name, SOPInvCredDelAddress.PostCode
FROM SLCustomerAccount
INNER JOIN SOPOrderReturn ON SLCustomerAccountID = CustomerID
LEFT JOIN Warehouse ON Warehouse.WarehouseID = SOPOrderReturn.WarehouseID
INNER JOIN SOPOrderReturnLine ON SOPOrderReturnLine.SOPOrderReturnID = SOPOrderReturn.SOPOrderReturnID
LEFT OUTER JOIN SOPStandardItemLink ON SOPStandardItemLink.SOPOrderReturnLineID = SOPOrderReturnLine.SOPOrderReturnLineID
LEFT OUTER JOIN SOPInvoiceCreditLine on SOPInvoiceCreditLine.SOPOrderReturnLineID = SOPOrderReturnLine.SOPOrderReturnLineID
LEFT OUTER JOIN SOPInvoiceCredit ON SOPInvoiceCredit.SOPInvoiceCreditID = SOPInvoiceCreditLine.SOPInvoiceCreditID
LEFT OUTER JOIN SOPInvCredDelAddress ON SOPInvCredDelAddress.SOPInvoiceCreditID = SOPInvoiceCredit.SOPInvoiceCreditID
WHERE SOPOrderReturn.DocumentTypeID = 3 OR SOPOrderReturn.DocumentTypeID = 2 OR SOPOrderReturn.DocumentTypeID = 0 OR SOPOrderReturn.DocumentTypeID = 1
ORDER BY DocumentNo DESC

Hi Steve,

Thanks for this, it is bring back some of the postcodes but not all of them for the orders. I have manually checked some of these in sage and there is a postcode in sage but is not picking up?

Thanks,

Jorawar

Jorawar,

This would be the result when the Invoice Address is the same as the Delivery Address as the SOPInvCredDelAddress table is not populated when this is true.

I have added in the Invoice postcode but still shows both fields as blank. When I look into the order on sage I can see the postcode but then it's not pulling back on the report

Just looking through the report it looks as though is basing off the Document Status ID - it is only bringing through postcodes for ones where the document status ID is complete