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