SELECT
 tBatch.ID,
 tBatch.BillingPeriod,
 tBatchType.ID,
 tBatchType.SupplierID,
 tBatchType.Description AS BatchType,
 tSupplier.SupplierName,
 tServiceID.ServiceID,
 tServiceID.Service,
 tServiceType.Code AS ServiceTypeCode,
 tServiceType.Description AS ServiceType,
 tInvoice.InvoiceDate,
 tFinalisedTransaction201004.TxnDate,
 tReportGroup.ID AS ReportGrpID,
 tReportGroup.GroupDesc AS ReportGrpDesc,
 tInvoice.PlatinumFileName,
 tInvoice.PlatinumInvoiceNo,
 tCustomer.ShipTo,
 tCustomer.FullName,
 tCentre.Description AS CustCentre,
 tActivity.Description AS CustActivity,
 tElement.ElementCode AS EleRevServTypeCode,
 tElement.Description AS EleRevServTypeDesc,
 tElement_1.ElementCode AS CustEleCode,
 tElement_1.Description AS CustEleDesc,
 tElement_2.ElementCode AS EleAdmCode,
 tElement_2.Description AS EleAdmDesc,
 tSubledger.Description AS CustSubLedger,
 tDepartment.Description AS CustDept,
 tLocation.Description AS Location,
 tPerson.Person,
 tApplication.ID,
 tApplication.Description,

 tOrigin.Description AS Origin,
 tDestination.Description AS Destination,
 tRateDescription.Description AS RateDescription,
 tRate.Cost,
 tTransactionType.Description AS TransactionType,
 tTransactionGroup.Description AS TransactionGroup,
 tDialledNumber.Description AS DialledNumber,
 tFinalisedTransaction201004.Duration,

 tFinalisedTransaction201004.AmountExGST,

 tBatchType.Var01Description,
 tFinalisedTransaction201004.Var01,
 tBatchType.Var02Description,
 tFinalisedTransaction201004.Var02,
 tBatchType.Var03Description,
 tFinalisedTransaction201004.Var03,
 tBatchType.Var04Description,
 tFinalisedTransaction201004.Var04,
 tBatchType.Var05Description,
 tFinalisedTransaction201004.Var05,
 tBatchType.Var06Description,
 tFinalisedTransaction201004.Var06,
 tBatchType.Var07Description,
 tFinalisedTransaction201004.Var07,
 tBatchType.Var08Description,
 tFinalisedTransaction201004.Var08,
 tBatchType.Var09Description,
 tFinalisedTransaction201004.Var09,
 tBatchType.Var10Description,
 tFinalisedTransaction201004.Var10,
 tBatchType.Var11Description,
 tFinalisedTransaction201004.Var11,
 tBatchType.Var12Description,
 tFinalisedTransaction201004.Var12,
 tBatchType.Var13Description,
 tFinalisedTransaction201004.Var13,
 tBatchType.Var14Description,
 tFinalisedTransaction201004.Var14,
 tBatchType.Var15Description,
 tFinalisedTransaction201004.Var15

FROM
 tFinalisedTransaction201004
 INNER JOIN (
	tBatch
	LEFT JOIN (
		tBatchType
		LEFT JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		)
		ON tBatch.BatchTypeID = tBatchType.ID
	)
	ON tFinalisedTransaction201004.BatchID = tBatch.ID
 LEFT JOIN ( tServiceID
	 LEFT JOIN tApplication
		ON tServiceID.ApplicationID = tApplication.ID
	) ON tFinalisedTransaction201004.ServiceID = tServiceID.ID
 LEFT JOIN (
	tInvoiceDetail INNER JOIN tInvoice
		ON tInvoiceDetail.InvoiceID = tInvoice.ID
	)
	ON tFinalisedTransaction201004.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tDialledNumber
	ON tFinalisedTransaction201004.DialledNumberID = tDialledNumber.ID
 LEFT JOIN tRateDescription
	ON tFinalisedTransaction201004.RateDescriptionID = tRateDescription.ID
 LEFT JOIN tDestination
	ON tFinalisedTransaction201004.DestinationID = tDestination.ID
 LEFT JOIN tOrigin
	ON tFinalisedTransaction201004.OriginID = tOrigin.ID
 LEFT JOIN tElement
	ON tFinalisedTransaction201004.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
	ON tFinalisedTransaction201004.CustomerElementID = tElement_1.ID
 LEFT JOIN (
	tServiceType
	LEFT JOIN tSupplier
		ON tServiceType.SupplierID = tSupplier.ID
	LEFT JOIN tElement tElement_2
		ON tServiceType.RevenueElementID = tElement_2.ID
	LEFT JOIN tRate
		ON tServiceType.ID = tRate.ServiceTypeID
	)
	ON tFinalisedTransaction201004.ServiceTypeID = tServiceType.ID
  LEFT JOIN (
	tTransactionType
	LEFT JOIN tTransactionGroup
		ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
	) ON tFinalisedTransaction201004.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tCustomer
	ON tFinalisedTransaction201004.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
	ON tFinalisedTransaction201004.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
	ON tFinalisedTransaction201004.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
	ON tFinalisedTransaction201004.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
	ON tFinalisedTransaction201004.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
	ON tFinalisedTransaction201004.LocationID = tLocation.ID
 LEFT JOIN tPerson
	ON tFinalisedTransaction201004.PersonID = tPerson.ID

WHERE
	tBatch.BillingPeriod = '2010/04'   
	AND tFinalisedTransaction201004.RevenueFlag = 1
	AND tCustomer.ShipTo <> 'VRT'
	AND tBatchType.Active = 1
	  AND tInvoice.InvoiceDate > dateadd(dd,9,'20100430')
	  AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,9,'20100430'))
--# exclude Victrack Rent and Calls
	AND tBatchType.ID <> 199
--# only this batchtypeID
	AND tBatchType.ID = 2
ORDER BY tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo, tBatch.ID,
	 tServiceID.ServiceID, tFinalisedTransaction201004.Var01,
	 tReportGroup.ID;



SELECT
 tBatch.ID,
 tBatch.BillingPeriod,
 tBatchType.ID,
 tBatchType.SupplierID,
 tBatchType.Description AS BatchType,
 tSupplier.SupplierName,
 tServiceID.ServiceID,
 tServiceID.Service,
 tServiceType.Code AS ServiceTypeCode,
 tServiceType.Description AS ServiceType,
 '' AS InvoiceDate,
 tFinalisedTransaction201004.TxnDate,
 tReportGroup.ID AS ReportGrpID,
 tReportGroup.GroupDesc AS ReportGrpDesc,
 '' AS PlatinumFileName,
 'VRT999999' AS PlatinumInvoiceNo,
 tCustomer.ShipTo,
 tCustomer.FullName,
 tCentre.Description AS CustCentre,
 tActivity.Description AS CustActivity,
 tElement.ElementCode AS EleRevServTypeCode,
 tElement.Description AS EleRevServTypeDesc,
 tElement_1.ElementCode AS CustEleCode,
 tElement_1.Description AS CustEleDesc,
 tElement_2.ElementCode AS EleAdmCode,
 tElement_2.Description AS EleAdmDesc,
 tSubledger.Description AS CustSubLedger,
 tDepartment.Description AS CustDept,
 tLocation.Description AS Location,
 tPerson.Person,
 tApplication.ID,
 tApplication.Description,

 tOrigin.Description AS Origin,
 tDestination.Description AS Destination,
 tRateDescription.Description AS RateDescription,
 tRate.Cost,
 tTransactionType.Description AS TransactionType,
 tTransactionGroup.Description AS TransactionGroup,
 tDialledNumber.Description AS DialledNumber,
 tFinalisedTransaction201004.Duration,

 tFinalisedTransaction201004.AmountExGST,

 tBatchType.Var01Description,
 tFinalisedTransaction201004.Var01,
 tBatchType.Var02Description,
 tFinalisedTransaction201004.Var02,
 tBatchType.Var03Description,
 tFinalisedTransaction201004.Var03,
 tBatchType.Var04Description,
 tFinalisedTransaction201004.Var04,
 tBatchType.Var05Description,
 tFinalisedTransaction201004.Var05,
 tBatchType.Var06Description,
 tFinalisedTransaction201004.Var06,
 tBatchType.Var07Description,
 tFinalisedTransaction201004.Var07,
 tBatchType.Var08Description,
 tFinalisedTransaction201004.Var08,
 tBatchType.Var09Description,
 tFinalisedTransaction201004.Var09,
 tBatchType.Var10Description,
 tFinalisedTransaction201004.Var10,
 tBatchType.Var11Description,
 tFinalisedTransaction201004.Var11,
 tBatchType.Var12Description,
 tFinalisedTransaction201004.Var12,
 tBatchType.Var13Description,
 tFinalisedTransaction201004.Var13,
 tBatchType.Var14Description,
 tFinalisedTransaction201004.Var14,
 tBatchType.Var15Description,
 tFinalisedTransaction201004.Var15

FROM
 tFinalisedTransaction201004
 INNER JOIN (
	tBatch
	LEFT JOIN (
		tBatchType
		LEFT JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		)
		ON tBatch.BatchTypeID = tBatchType.ID
	)
	ON tFinalisedTransaction201004.BatchID = tBatch.ID
 LEFT JOIN ( tServiceID
	 LEFT JOIN tApplication
		ON tServiceID.ApplicationID = tApplication.ID
	) ON tFinalisedTransaction201004.ServiceID = tServiceID.ID
 LEFT JOIN tDialledNumber
	ON tFinalisedTransaction201004.DialledNumberID = tDialledNumber.ID
 LEFT JOIN tRateDescription
	ON tFinalisedTransaction201004.RateDescriptionID = tRateDescription.ID
 LEFT JOIN tDestination
	ON tFinalisedTransaction201004.DestinationID = tDestination.ID
 LEFT JOIN tOrigin
	ON tFinalisedTransaction201004.OriginID = tOrigin.ID
 LEFT JOIN tElement
	ON tFinalisedTransaction201004.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
	ON tFinalisedTransaction201004.CustomerElementID = tElement_1.ID
 LEFT JOIN (
	tServiceType
	LEFT JOIN tSupplier
		ON tServiceType.SupplierID = tSupplier.ID
	LEFT JOIN tElement tElement_2
		ON tServiceType.RevenueElementID = tElement_2.ID
	LEFT JOIN tRate
		ON tServiceType.ID = tRate.ServiceTypeID
	)
	ON tFinalisedTransaction201004.ServiceTypeID = tServiceType.ID
  LEFT JOIN (
	tTransactionType
	LEFT JOIN tTransactionGroup
		ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
	) ON tFinalisedTransaction201004.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tCustomer
	ON tFinalisedTransaction201004.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
	ON tFinalisedTransaction201004.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
	ON tFinalisedTransaction201004.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
	ON tFinalisedTransaction201004.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
	ON tFinalisedTransaction201004.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
	ON tFinalisedTransaction201004.LocationID = tLocation.ID
 LEFT JOIN tPerson
	ON tFinalisedTransaction201004.PersonID = tPerson.ID

WHERE
	tBatch.BillingPeriod = '2010/04'   
	AND tFinalisedTransaction201004.RevenueFlag = 1
	AND tCustomer.ShipTo = 'VRT'
	AND tBatchType.Active = 1
--# exclude Victrack Rent and Calls
	AND tBatchType.ID <> 199
--# only this batchtypeID
	AND tBatchType.ID = 2
ORDER BY tCustomer.ShipTo, PlatinumInvoiceNo, tBatch.ID,
	 tServiceID.ServiceID, tFinalisedTransaction201004.Var01,
	 tReportGroup.ID;