SELECT
    tCustomer.ShipTo,
    TN1.BatchID AS Sundry,
    TN1.RentAmt,
    TN1.CallAmt,
    TN1.OtherAmt,
    TN1.AdminAmt,
    (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt + TN1.AdminAmt) AS TotalAll,
    tBatchType.ID AS BatchTypeID,
    tBatchType.Description AS BatchTypeDesc,
    tBatchType.ReportGroupID,
    tReportGroup.GroupDesc AS ReportGroupDesc,
    tReportGroup.ReportOrder,
    tServiceType.Code,
    tServiceID.ServiceID AS ServiceName,
    tLocation.Description AS Location,
    tPerson.Person, 
    tCentre.ID, tCentre.Description AS Centre,
    tActivity.Description AS Activity,
    tElement.ElementCode AS Element,
    tSubledger.Description AS Subledger,
    tServiceID.PayType,
    tDepartment.ID, tDepartment.Description AS Department, 
    tServiceID.Description AS ServiceDesc,
    tServiceID.InstallationDate,
    tStatus.Status
FROM
 (SELECT
	tFinalisedTransaction201004.batchID,
	tFinalisedTransaction201004.ServiceID,
	tFinalisedTransaction201004.ServiceTypeID,
	tFinalisedTransaction201004.CustomerID,
	tFinalisedTransaction201004.StatusID,
	tFinalisedTransaction201004.PersonID,
	tFinalisedTransaction201004.LocationID,
	tFinalisedTransaction201004.CustomerCentreID,
	tFinalisedTransaction201004.CustomerActivityID,
	tFinalisedTransaction201004.CustomerElementID,
	tFinalisedTransaction201004.CustomerSubledgerID,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'RENT' THEN
		tFinalisedTransaction201004.AmountExGST ELSE 0 END
	   ) AS RentAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'CALL' THEN
		tFinalisedTransaction201004.AmountExGST ELSE 0 END
	   ) AS CallAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'OTHER' THEN
		tFinalisedTransaction201004.AmountExGST ELSE 0 END
	   ) AS OtherAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'ADMIN' THEN
		tFinalisedTransaction201004.AmountExGST ELSE 0 END
	   ) AS AdminAmt
	FROM tFinalisedTransaction201004
	 INNER JOIN tBatch
		ON tBatch.ID = tFinalisedTransaction201004.BatchID
	 LEFT 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 tFinalisedTransaction201004.InvoiceDetailID = tInvoiceDetail.ID
	 LEFT JOIN tTransactionType
		ON tFinalisedTransaction201004.TransactionTypeID = tTransactionType.ID
	 LEFT JOIN tTransactionGroup
		ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
	WHERE tBatch.BillingPeriod = '2010/04' 
	  AND tFinalisedTransaction201004.RevenueFlag = 1
          
          
	GROUP BY
		tFinalisedTransaction201004.ServiceID,
		tFinalisedTransaction201004.BatchID,
		tFinalisedTransaction201004.ServiceTypeID,
		tFinalisedTransaction201004.CustomerID,
		tFinalisedTransaction201004.StatusID,
		tFinalisedTransaction201004.PersonID,
		tFinalisedTransaction201004.LocationID,
		tFinalisedTransaction201004.CustomerCentreID,
		tFinalisedTransaction201004.CustomerActivityID,
		tFinalisedTransaction201004.CustomerElementID,
		tFinalisedTransaction201004.CustomerSubledgerID
 ) AS TN1
 LEFT JOIN tStatus
	ON TN1.StatusID = tStatus.ID
 LEFT JOIN tCustomer
	ON TN1.CustomerID = tCustomer.ID
 LEFT JOIN tPerson
	ON TN1.PersonID = tPerson.ID
 LEFT JOIN tLocation
	ON TN1.LocationID = tLocation.ID
 LEFT JOIN ( tCentre LEFT JOIN tDepartment
		ON tCentre.DepartmentID = tDepartment.ID
	  ) ON TN1.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
	ON TN1.CustomerActivityID = tActivity.ID
 LEFT JOIN tElement
	ON TN1.CustomerElementID = tElement.ID
 LEFT JOIN tSubledger
	ON TN1.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tServiceID
	ON TN1.ServiceID = tServiceID.ID
 LEFT JOIN tServiceType
	ON TN1.ServiceTypeID = tServiceType.ID
 LEFT JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			 ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON TN1.BatchID = tBatch.ID
WHERE
 ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' )
    and tCustomer.ShipTo = 'VRT'
    and tBatchType.Active = 1

 and tBatch.ID = 33667
 and tServiceID.ServiceID = "593BAL"

ORDER BY tCustomer.ShipTo, tServiceID.ServiceID, tServiceType.Code, Sundry;