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, tFinalisedTransaction201402.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, tServiceID.PayType, tTransactionType.Description AS TransactionType, tTransactionGroup.Description AS TransactionGroup, tDialledNumber.Description AS DialledNumber, tFinalisedTransaction201402.Duration, tFinalisedTransaction201402.AmountExGST, tBatchType.Var01Description, tFinalisedTransaction201402.Var01, tBatchType.Var02Description, tFinalisedTransaction201402.Var02, tBatchType.Var03Description, tFinalisedTransaction201402.Var03, tBatchType.Var04Description, tFinalisedTransaction201402.Var04, tBatchType.Var05Description, tFinalisedTransaction201402.Var05, tBatchType.Var06Description, tFinalisedTransaction201402.Var06, tBatchType.Var07Description, tFinalisedTransaction201402.Var07, tBatchType.Var08Description, tFinalisedTransaction201402.Var08, tBatchType.Var09Description, tFinalisedTransaction201402.Var09, tBatchType.Var10Description, tFinalisedTransaction201402.Var10, tBatchType.Var11Description, tFinalisedTransaction201402.Var11, tBatchType.Var12Description, tFinalisedTransaction201402.Var12, tBatchType.Var13Description, tFinalisedTransaction201402.Var13, tBatchType.Var14Description, tFinalisedTransaction201402.Var14, tBatchType.Var15Description, tFinalisedTransaction201402.Var15, tFinalisedTransaction201402.CustomerCentreID, tFinalisedTransaction201402.DepartmentID, tFinalisedTransaction201402.GroupID, tFinalisedTransaction201402.CustomerID --# tServiceType.SupplierID, --# tSupplierServiceCode.SupplierID, --# tSupplierServiceCode.Code AS SupplierServCode, FROM tFinalisedTransaction201402 INNER JOIN ( tBatch LEFT JOIN ( tBatchType LEFT JOIN tReportGroup ON tBatchType.ReportGroupID = tReportGroup.ID ) ON tBatch.BatchTypeID = tBatchType.ID ) ON tFinalisedTransaction201402.BatchID = tBatch.ID LEFT JOIN ( tServiceID LEFT JOIN tApplication ON tServiceID.ApplicationID = tApplication.ID ) ON tFinalisedTransaction201402.ServiceID = tServiceID.ID 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 tFinalisedTransaction201402.InvoiceDetailID = tInvoiceDetail.ID LEFT JOIN tDialledNumber ON tFinalisedTransaction201402.DialledNumberID = tDialledNumber.ID LEFT JOIN tRateDescription ON tFinalisedTransaction201402.RateDescriptionID = tRateDescription.ID LEFT JOIN tDestination ON tFinalisedTransaction201402.DestinationID = tDestination.ID LEFT JOIN tOrigin ON tFinalisedTransaction201402.OriginID = tOrigin.ID LEFT JOIN tElement ON tFinalisedTransaction201402.ElementID = tElement.ID LEFT JOIN tElement tElement_1 ON tFinalisedTransaction201402.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 ) ON tFinalisedTransaction201402.ServiceTypeID = tServiceType.ID LEFT JOIN ( tTransactionType LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID --# LEFT JOIN tSupplierServiceCode --# ON tTransactionType.ID = tSupplierServiceCode.TransactionTypeID ) ON tFinalisedTransaction201402.TransactionTypeID = tTransactionType.ID --# LEFT JOIN tSupplierServiceCode --# ON tFinalisedTransaction201402.SupplierServiceCodeID = tSupplierServiceCode.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 tFinalisedTransaction201402.RevenueFlag = 1 AND tCustomer.ShipTo <> "VRT" --# AND tBatchType.Active = 1 --# AND tSupplierServiceCode.Active <> 0 --# and tElement.ID = tServiceType.RevenueElementID --# and tTransactionType.ID = tSupplierServiceCode.TransactionTypeID --# and tSupplier.ID = SupplierServiceCode.SupplierID AND tInvoice.InvoiceDate > dateadd(dd,15,"20140228") AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,15,"20140228")) --# exclude Victrack Rent and Calls AND tBatchType.ID <> 199 and tCustomer.ShipTo = "NTT" and tInvoice.PlatinumInvoiceNo= "INV029783" and tBatch.ID = 43201 --# and tServiceID.ServiceID = "XXXXXXXXXXX" ORDER BY tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo, tBatch.ID, tServiceID.ServiceID, tFinalisedTransaction201402.Var01, tReportGroup.ID