SELECT DISTINCT tCustomer.Shipto AS Div, tInvoice.PlatinumFileName, tInvoice.PlatinumInvoiceNo, tFinalisedTransaction201205.BatchID AS Sundry, tSupplier.SupplierName AS Supplier, tInvoiceDetail.ElementID, tElement.Description, tInvoiceDetail.Period, tFinalisedTransaction201205.InvoiceDetailID, tInvoice.InvoiceDate, tInvoiceDetail.Description AS InvParticular, tElement.ReportGroupID, tInvoiceDetail.GSTAmount FROM tFinalisedTransaction201205 INNER JOIN tBatch ON tBatch.ID = tFinalisedTransaction201205.BatchID INNER JOIN tSupplier ON tSupplier.ID = tBatch.SupplierID INNER JOIN tElement ON tElement.ID = tFinalisedTransaction201205.ElementID INNER JOIN tInvoiceDetail ON tElement.ID = tFinalisedTransaction201205.ElementID INNER JOIN tInvoiceDetail ON tInvoiceDetail.ID = tFinalisedTransaction201205.InvoiceDetailID INNER JOIN tInvoice ON tInvoiceDetail.InvoiceID = tInvoice.ID --# and tInvoice.ID NOT IN ( --# SELECT distinct tInvoice.CreditNoteInvID --# FROM tInvoice --# WHERE tInvoice.CreditNoteInvID is not null --# ) INNER JOIN tCustomer ON tInvoice.CustomerID = tCustomer.ID WHERE tInvoice.PlatinumInvoiceNo IS NOT NULL AND tInvoiceDetail.Period = '2012/05' AND tInvoice.InvoiceDate > dateadd(dd,9,'20120531') AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,9,'20120531')) ORDER BY Div, tInvoice.PlatinumFileName, tInvoice.PlatinumInvoiceNo, tInvoice.InvoiceDate, Sundry, Supplier, tInvoiceDetail.ElementID;