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;