SELECT tDataCollection.*,
 tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS SID, tServiceID.SubledgerID, tServiceID.CustomerCentreID,
 tServiceID.CustomerActivityID,
 tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd, tServiceID.UpMDF,
 tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, tServiceType.Code, tServiceType.ID AS TID,
 tServiceType.Description, tServiceType.RevenueElementID, tServiceType.ExpenseElementID,
 tServiceType.SplitService, tCustomer.ID AS Customer_ID,
 tDialledNumber.ID as DialledID,
 tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID,
 tTransactionGroup.Description AS TransactionGroup
FROM ((((((tDataCollection LEFT JOIN ((tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN (tGroup LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID) ON tDepartment.GroupID = tGroup.ID)
 ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) ON tDataCollection.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tDialledNumber ON tDataCollection.DialledNumber = tDialledNumber.Description)
 LEFT JOIN tOrigin ON tDataCollection.Origin = tOrigin.Description)
 LEFT JOIN tDestination ON tDataCollection.Destination = tDestination.Description)
 LEFT JOIN tRateDescription ON tDataCollection.RateDescription = tRateDescription.Description)
 LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID)
 LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tServiceID.ServiceID IS NOT NULL
AND tDataCollection.BatchID = 15001
AND  tServiceType.SplitService <> 1
ORDER BY tDataCollection.ID


INSERT INTO tTransaction
(DataRefID, SequenceNo, Duration, CallCode, BatchID, TxnDate, AmountExGST, GSTAmount, AmountIncGST, FromDate, ToDate,
 TransactionTypeID, StatusID, GSTFlag, ActivityID, CentreID, ServiceID, SubledgerID, CustomerCentreID,
 CustomerActivityID, CustomerElementID, CustomerSubledgerID, LocationID, UpEnd, UpMDF, DownEnd, DownMDF, Length, PersonID,
 ServiceTypeID, ElementID, RevenueFlag, CustomerID, DialledNumberID, OriginID, DestinationID, RateDescriptionID,
 Var01, Var02, Var03, Var04, Var05, Var06, Var07, Var08, Var09, Var10, Var11, Var12, Var13, Var14, Var15)
SELECT tDataCollection.ID AS DataRefID, tDataCollection.SequenceNo, tDataCollection.Duration, tDataCollection.CallCode, tDataCollection.BatchID,
 (CASE tDataCollection.TxnDate WHEN NULL THEN NULL ELSE
  SUBSTRING(tDataCollection.TxnDate,7,4) + SUBSTRING(tDataCollection.TxnDate,4,2) + SUBSTRING(tDataCollection.TxnDate,1,2) + 
  (CASE tDataCollection.TxnTime WHEN NULL THEN '' ELSE ' ' + tDataCollection.TxnTime END) END) AS TxnDate,
 tDataCollection.AmountExGST, tDataCollection.GSTAmount, tDataCollection.AmountIncGST,
 (CASE tDataCollection.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.FromDate,7,4) + SUBSTRING(tDataCollection.FromDate,4,2) + SUBSTRING(tDataCollection.FromDate,1,2) END) AS FromDate,
 (CASE tDataCollection.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.ToDate,7,4) + SUBSTRING(tDataCollection.ToDate,4,2) + SUBSTRING(tDataCollection.ToDate,1,2) END) AS ToDate,
 tDataCollection.TransactionTypeID, tDataCollection.StatusID, (CASE tDataCollection.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS GSTFlag, 
 tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS ServiceID, tServiceID.SubledgerID, tServiceID.CustomerCentreID,
 tServiceID.CustomerActivityID, tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd,
 tServiceID.UpMDF, tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, tServiceType.ID AS ServiceTypeID,
 tServiceType.RevenueElementID AS ElementID, (1) AS RevenueFlag,
 tCustomer.ID AS CustomerID, tDialledNumber.ID as DialledNumberID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID,
 tDataCollection.Var01, tDataCollection.Var02, tDataCollection.Var03,
 tDataCollection.Var04, tDataCollection.Var05, tDataCollection.Var06, tDataCollection.Var07, tDataCollection.Var08, tDataCollection.Var09,
 tDataCollection.Var10, tDataCollection.Var11, tDataCollection.Var12, tDataCollection.Var13, tDataCollection.Var14, tDataCollection.Var15
FROM ((((tDataCollection LEFT JOIN ((tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN (tGroup LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID) ON tDepartment.GroupID = tGroup.ID)
 ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) ON tDataCollection.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tDialledNumber ON tDataCollection.DialledNumber = tDialledNumber.Description)
 LEFT JOIN tOrigin ON tDataCollection.Origin = tOrigin.Description)
 LEFT JOIN tDestination ON tDataCollection.Destination = tDestination.Description)
 LEFT JOIN tRateDescription ON tDataCollection.RateDescription = tRateDescription.Description
WHERE tServiceID.ServiceID IS NOT NULL
AND tDataCollection.BatchID = 15001
AND tServiceType.SplitService <> 1
AND tServiceType.RevenueElementID IS NOT NULL


 LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID)
 LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

 (CASE tDataCollection.TxnDate WHEN NULL THEN NULL ELSE
  (SUBSTRING(tDataCollection.TxnDate,7,4) + SUBSTRING(tDataCollection.TxnDate,4,2) + SUBSTRING(tDataCollection.TxnDate,1,2) + 
  (CASE tDataCollection.TxnTime WHEN NULL THEN '' ELSE ' ' + tDataCollection.TxnTime END) END) AS TxnDate,

 (SUBSTRING(tDataCollection.TxnDate,7,4) + SUBSTRING(tDataCollection.TxnDate,4,2) + SUBSTRING(tDataCollection.TxnDate,1,2) + ' ' + tDataCollection.TxnTime) AS TxnDate,

 (CASE tDataCollection.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.ToDate,7,4) + SUBSTRING(tDataCollection.ToDate,4,2) + SUBSTRING(tDataCollection.ToDate,1,2) END) AS ToDate,

 (CASE tDataCollection.TxnDate WHEN NULL THEN NULL ELSE
  SUBSTRING(tDataCollection.TxnDate,7,4) + SUBSTRING(tDataCollection.TxnDate,4,2) + SUBSTRING(tDataCollection.TxnDate,1,2) + 
  (CASE tDataCollection.TxnTime WHEN NULL THEN '' ELSE ' ' + tDataCollection.TxnTime END) END) AS TxnDate,

 CAST((CASE tDataCollection.TxnDate WHEN NULL THEN NULL ELSE
  (SUBSTRING(tDataCollection.TxnDate,7,4) + SUBSTRING(tDataCollection.TxnDate,4,2) + SUBSTRING(tDataCollection.TxnDate,1,2) + 
  (CASE tDataCollection.TxnTime WHEN NULL THEN '' ELSE (' ' + tDataCollection.TxnTime) END)) END) AS DateTime) AS TxnDate,




,
 tTransactionGroup.Description AS TransactionGroup
tServiceType.Description, 
tServiceType.SplitService, 
tServiceType.ExpenseElementID
ORDER BY tDataCollection.ID



SELECT COUNT(*) AS TotalCount 
FROM ((tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID)
CROSS JOIN tServiceSplit
WHERE tDataCollection.BatchID = 50007
AND tServiceType.SplitService = 1
AND tServiceID.ID = tServiceSplit.ServiceID


SELECT tServiceSplit.*, tServiceID.ServiceID
FROM ((tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID)
LEFT JOIN tServiceSplit ON tServiceID.ID = tServiceSplit.ServiceID
WHERE tDataCollection.BatchID = 50007
AND tServiceType.SplitService = 1
AND tServiceID.ID = tServiceSplit.ServiceID






SELECT tDataCollection.ID AS DataRefID,
tDataCollection.SequenceNo, tDataCollection.Duration, tDataCollection.CallCode, tDataCollection.BatchID,
 (CASE tDataCollection.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.TxnDate,7,4) + SUBSTRING(tDataCollection.TxnDate,4,2) + SUBSTRING(tDataCollection.TxnDate,1,2) + (CASE tDataCollection.TxnTime WHEN NULL THEN '''' ELSE '' '' + tDataCollection.TxnTime END) END) AS TxnDate,
 tDataCollection.AmountExGST, tDataCollection.GSTAmount, tDataCollection.AmountIncGST,
 (CASE tDataCollection.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.FromDate,7,4) + SUBSTRING(tDataCollection.FromDate,4,2) + SUBSTRING(tDataCollection.FromDate,1,2) END) AS FromDate,
 (CASE tDataCollection.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(tDataCollection.ToDate,7,4) + SUBSTRING(tDataCollection.ToDate,4,2) + SUBSTRING(tDataCollection.ToDate,1,2) END) AS ToDate,
 tDataCollection.TransactionTypeID, tDataCollection.StatusID, (CASE tDataCollection.GSTFlag WHEN ''Y'' THEN 1 ELSE 0 END) AS GSTFlag,
 tServiceID.ActivityID, tServiceID.CentreID, tServiceID.ID AS ServiceID, tServiceID.SubledgerID, tServiceID.CustomerCentreID,
 tServiceID.CustomerActivityID, tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd,
 tServiceID.UpMDF, tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, tServiceType.ID AS ServiceTypeID,
 tServiceType.RevenueElementID, tServiceType.ExpenseElementID, tTransactionGroup.Description AS TransactionGroup,
 tCustomer.ID AS CustomerID, tDialledNumber.ID as DialledNumberID, tOrigin.ID as OriginID, tDestination.ID as DestinationID, tRateDescription.ID AS RateDescriptionID,
 tDataCollection.Var01, tDataCollection.Var02, tDataCollection.Var03,
 tDataCollection.Var04, tDataCollection.Var05, tDataCollection.Var06, tDataCollection.Var07, tDataCollection.Var08, tDataCollection.Var09,
 tDataCollection.Var10, tDataCollection.Var11, tDataCollection.Var12, tDataCollection.Var13, tDataCollection.Var14, tDataCollection.Var15
FROM ((((((tDataCollection LEFT JOIN ((tServiceID LEFT JOIN (tCentre LEFT JOIN (tDepartment LEFT JOIN (tGroup ON tDepartment.GroupID = tGroup.ID)
 ON tCentre.DepartmentID = tDepartment.ID) ON tServiceID.CustomerCentreID = tCentre.ID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID) ON tDataCollection.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tDialledNumber ON tDataCollection.DialledNumber = tDialledNumber.Description)
 LEFT JOIN tOrigin ON tDataCollection.Origin = tOrigin.Description)
 LEFT JOIN tDestination ON tDataCollection.Destination = tDestination.Description)
 LEFT JOIN tRateDescription ON tDataCollection.RateDescription = tRateDescription.Description)
 LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID)
 LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tServiceID.ServiceID IS NOT NULL
AND tDataCollection.BatchID = 15003
AND tServiceType.SplitService = 1