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;