SELECT tElement.Description AS ElementDesc, tServiceID.ServiceID AS ServiceID, tInvoiceDetail.BatchID AS Batch, tBatchType.SupplierDisplayedName AS Supplier, tLocation.Description AS Location, tPerson.Person, tCentre.Description AS Centre, tActivity.Description AS Activity, tElement_1.Description AS Element1Desc, tSubledger.Description AS SubLedger, tFinalisedTransaction201402.PayType, tServiceType.Description AS ServiceType, tInvoiceDetail.Description AS InvoiceDescription, tInvoiceDetail.Period, tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo AS Invoice, SUM(tFinalisedTransaction201402.AmountExGST) AS ExGST, SUM(tFinalisedTransaction201402.GSTAmount) AS GST, SUM(tFinalisedTransaction201402.AmountIncGST) AS IncGST, tTransactionGroup.Description AS TxnGrp, tFinalisedTransaction201402.Var01, tFinalisedTransaction201402.Var02, tFinalisedTransaction201402.Var03, tFinalisedTransaction201402.Var04, tFinalisedTransaction201402.Var05, tFinalisedTransaction201402.Var06, tFinalisedTransaction201402.Var07, tFinalisedTransaction201402.Var08, tFinalisedTransaction201402.Var09, tFinalisedTransaction201402.Var10, tFinalisedTransaction201402.Var11, tFinalisedTransaction201402.Var12, tFinalisedTransaction201402.Var13, tFinalisedTransaction201402.Var14, tFinalisedTransaction201402.Var15, tBatchType.Description AS BatchType, tReportGroup.GroupDesc ReportGroup, tDepartment.Description AS Dept, tServiceID.Service AS Service, tCentre.ID AS CentreID, tDepartment.ID AS DeptID, tFinalisedTransaction201402.GroupID FROM tFinalisedTransaction201402 INNER JOIN ( tBatch LEFT JOIN ( tBatchType LEFT OUTER JOIN tReportGroup ON tBatchType.ReportGroupID = tReportGroup.ID ) ON tBatch.BatchTypeID = tBatchType.ID ) ON tBatch.ID = tFinalisedTransaction201402.BatchID LEFT JOIN tServiceID ON tFinalisedTransaction201402.ServiceID = tServiceID.ID INNER JOIN ( tInvoiceDetail 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 --# ) ) ON tFinalisedTransaction201402.InvoiceDetailID = tInvoiceDetail.ID LEFT JOIN tTransactionType ON tFinalisedTransaction201402.TransactionTypeID = tTransactionType.ID LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID LEFT JOIN tElement ON tFinalisedTransaction201402.ElementID = tElement.ID LEFT JOIN tElement tElement_1 ON tFinalisedTransaction201402.CustomerElementID = tElement_1.ID LEFT JOIN tServiceType ON tFinalisedTransaction201402.ServiceTypeID = tServiceType.ID LEFT JOIN tCustomer ON tFinalisedTransaction201402.CustomerID = tCustomer.ID LEFT JOIN tCentre ON tFinalisedTransaction201402.CustomerCentreID = tCentre.ID LEFT JOIN tActivity ON tFinalisedTransaction201402.CustomerActivityID = tActivity.ID LEFT JOIN tSubledger ON tFinalisedTransaction201402.CustomerSubledgerID = tSubledger.ID LEFT JOIN tDepartment ON tFinalisedTransaction201402.DepartmentID = tDepartment.ID LEFT JOIN tLocation ON tFinalisedTransaction201402.LocationID = tLocation.ID LEFT JOIN tPerson ON tFinalisedTransaction201402.PersonID = tPerson.ID WHERE tBatch.BillingPeriod = "2014/02" and tInvoice.PlatinumInvoiceNo = "INV029685" AND tFinalisedTransaction201402.RevenueFlag = 1 AND tCustomer.ShipTo <> "VRT" --# AND tBatchType.Active = 1 AND tInvoice.InvoiceDate > dateadd(dd,9,"20140228") AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,9,"20140228")) AND tServiceID.ServiceID = "0404-879300" GROUP BY tElement.Description, tServiceID.ServiceID, tServiceID.Service, tInvoiceDetail.BatchID, tBatchType.SupplierDisplayedName, tLocation.Description, tPerson.Person, tCentre.ID, tCentre.Description, tServiceType.Description, tInvoiceDetail.Description, tSubledger.Description, tInvoiceDetail.Period, tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo, tDepartment.ID, tFinalisedTransaction201402.GroupID, tFinalisedTransaction201402.Var01, tFinalisedTransaction201402.Var02, tFinalisedTransaction201402.Var03, tFinalisedTransaction201402.Var04, tFinalisedTransaction201402.Var05, tFinalisedTransaction201402.Var06, tFinalisedTransaction201402.Var07, tFinalisedTransaction201402.Var08, tFinalisedTransaction201402.Var09, tFinalisedTransaction201402.Var10, tFinalisedTransaction201402.Var11, tFinalisedTransaction201402.Var12, tFinalisedTransaction201402.Var13, tFinalisedTransaction201402.Var14, tFinalisedTransaction201402.Var15, tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, tDepartment.Description, tFinalisedTransaction201402.PayType, tActivity.Description, tElement_1.Description ORDER BY tInvoice.PlatinumInvoiceNo, Batch