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 

SELECT 
  SOPOrderReturn.DocumentNo, 
  SOPOrderReturn.DocumentDate, 
  SOPOrderReturn.DocumentTypeID, 
  SOPOrderReturn.DocumentStatusID, 
  SOPOrderReturnLine.ItemCode, 
  SOPOrderReturnLine.ItemDescription, 
  SLCustomerAccount.CustomerAccountName, 
  SOPOrderReturnLine.LineQuantity, 
  SOPOrderReturnLine.UnitSellingPrice, 
  SOPOrderReturnLine.LineTotalValue, 
  SOPOrderReturn.CustomerDocumentNo, 
  SOPOrderReturn.SourceDocumentNo, 
  SOPOrderReturn.AnalysisCode3 AS LostReason, 
  CASE WHEN 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 AS QuoteStatus, 
  SOPOrderReturn.AnalysisCode6 AS ContractRef, 
  SOPOrderReturn.DocumentCreatedBy, 
  SOPOrderReturn.AnalysisCode4 AS Notes, 
  Warehouse.Name, 
  SOPDocDelAddress.PostCode 
FROM 
  SLCustomerAccount 
  INNER JOIN SOPOrderReturn ON SLCustomerAccount.SLCustomerAccountID = SOPOrderReturn.CustomerID 
  LEFT OUTER JOIN Warehouse ON Warehouse.WarehouseID = SOPOrderReturn.WarehouseID 
  INNER JOIN SOPOrderReturnLine ON SOPOrderReturnLine.SOPOrderReturnID = SOPOrderReturn.SOPOrderReturnID 
  left outer JOIN SOPDocDelAddress ON SOPOrderReturn.SOPOrderReturnID = SOPDocDelAddress.SOPOrderReturnID 
  LEFT OUTER JOIN SOPStandardItemLink ON SOPStandardItemLink.SOPOrderReturnLineID = SOPOrderReturnLine.SOPOrderReturnLineID 
WHERE 
  (SOPOrderReturn.DocumentTypeID = 3) OR (SOPOrderReturn.DocumentTypeID = 2) OR (SOPOrderReturn.DocumentTypeID = 0) OR ( SOPOrderReturn.DocumentTypeID = 1 ) 
ORDER BY 
  SOPOrderReturn.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