/* NON-SPLIT Transactions */
AAAAAAAAAAAAAAAAA
SELECT DC1.*
FROM (SELECT DC.*
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code",
 tServiceType.SplitService, tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20087
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService <> 1 OR (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NULL OR DC1.Active <> 1)))
AAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBB
SELECT DC2.*
FROM (SELECT DC1.*
FROM (SELECT DC.*
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code",
 tServiceType.SplitService, tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20087
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService <> 1 OR (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NULL OR DC1.Active <> 1)))) AS DC2
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) ON DC2.SID = tServiceID.ID
BBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCC
SELECT DC2.*
FROM (((((SELECT DC1.*
FROM (SELECT DC.*
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code",
 tServiceType.SplitService, tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20087
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService <> 1 OR (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NULL OR DC1.Active <> 1)))) AS DC2
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) ON DC2.SID = tServiceID.ID)
LEFT JOIN tDialledNumber ON DC2.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC2.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC2.RateDescription = tRateDescription.Description
CCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDD
SELECT DC2.ID AS DataRefID, DC2.SequenceNo, DC2.Duration, DC2.CallCode, DC2.BatchID,
 (CASE DC2.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.TxnDate,7,4) + SUBSTRING(DC2.TxnDate,4,2) + SUBSTRING(DC2.TxnDate,1,2) + (CASE DC2.TxnTime WHEN NULL THEN '' ELSE ' ' + DC2.TxnTime END) END) AS "TxnDate",
 DC2.AmountExGST, DC2.GSTAmount, DC2.AmountIncGST,
 (CASE DC2.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.FromDate,7,4) + SUBSTRING(DC2.FromDate,4,2) + SUBSTRING(DC2.FromDate,1,2) END) AS "FromDate",
 (CASE DC2.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.ToDate,7,4) + SUBSTRING(DC2.ToDate,4,2) + SUBSTRING(DC2.ToDate,1,2) END) AS "ToDate",
 DC2.TransactionTypeID, DC2.StatusID, (CASE DC2.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS "GSTFlag",
 tServiceID.ActivityID, tServiceID.CentreID, DC2.SID 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, DC2.ServiceTypeID,
 (0) AS "RevenueFlag", DC2.ExpenseElementID AS "ElementID",
 tGroup.CustomerID, tDialledNumber.ID as "DialledNumberID", tOrigin.ID as "OriginID", tDestination.ID as "DestinationID", tRateDescription.ID AS "RateDescriptionID", DC2.Peak,
 DC2.Var01, DC2.Var02, DC2.Var03, DC2.Var04, DC2.Var05,
 DC2.Var06, DC2.Var07, DC2.Var08, DC2.Var09, DC2.Var10,
 DC2.Var11, DC2.Var12, DC2.Var13, DC2.Var14, DC2.Var15
FROM (((((SELECT DC1.*
FROM (SELECT DC.*, tServiceType.ID AS "ServiceTypeID", tServiceType.ExpenseElementID, tServiceType.RevenueElementID
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code", tServiceType.SplitService,
 tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20087
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService <> 1 OR (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NULL OR DC1.Active <> 1)))) AS DC2
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) ON DC2.SID = tServiceID.ID)
LEFT JOIN tDialledNumber ON DC2.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC2.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC2.RateDescription = tRateDescription.Description
DDDDDDDDDDDDDDDDDDD
EEEEEEEEEEEEEEEEEEE                      FINAL - OK.
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, RevenueFlag, ElementID, CustomerID, DialledNumberID, OriginID, DestinationID, RateDescriptionID, Peak,
 Var01, Var02, Var03, Var04, Var05, Var06, Var07, Var08, Var09, Var10, Var11, Var12, Var13, Var14, Var15)
SELECT DC2.ID AS DataRefID, DC2.SequenceNo, DC2.Duration, DC2.CallCode, DC2.BatchID,
 (CASE DC2.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.TxnDate,7,4) + SUBSTRING(DC2.TxnDate,4,2) + SUBSTRING(DC2.TxnDate,1,2) + (CASE DC2.TxnTime WHEN NULL THEN '' ELSE ' ' + DC2.TxnTime END) END) AS "TxnDate",
 DC2.AmountExGST, DC2.GSTAmount, DC2.AmountIncGST,
 (CASE DC2.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.FromDate,7,4) + SUBSTRING(DC2.FromDate,4,2) + SUBSTRING(DC2.FromDate,1,2) END) AS "FromDate",
 (CASE DC2.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.ToDate,7,4) + SUBSTRING(DC2.ToDate,4,2) + SUBSTRING(DC2.ToDate,1,2) END) AS "ToDate",
 DC2.TransactionTypeID, DC2.StatusID, (CASE DC2.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS "GSTFlag",
 tServiceID.ActivityID, tServiceID.CentreID, DC2.SID 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, DC2.ServiceTypeID,
 (0) AS "RevenueFlag", DC2.ExpenseElementID AS "ElementID",
 tGroup.CustomerID, tDialledNumber.ID as "DialledNumberID", tOrigin.ID as "OriginID", tDestination.ID as "DestinationID", tRateDescription.ID AS "RateDescriptionID", DC2.Peak,
 DC2.Var01, DC2.Var02, DC2.Var03, DC2.Var04, DC2.Var05,
 DC2.Var06, DC2.Var07, DC2.Var08, DC2.Var09, DC2.Var10,
 DC2.Var11, DC2.Var12, DC2.Var13, DC2.Var14, DC2.Var15
FROM (((((SELECT DC1.*
FROM (SELECT DC.*, tServiceType.ID AS "ServiceTypeID", tServiceType.ExpenseElementID, tServiceType.RevenueElementID
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code", tServiceType.SplitService,
 tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20087
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService <> 1 OR (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NULL OR DC1.Active <> 1)))) AS DC2
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) ON DC2.SID = tServiceID.ID)
LEFT JOIN tDialledNumber ON DC2.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC2.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC2.RateDescription = tRateDescription.Description
EEEEEEEEEEEEEEEEEEE                      FINAL - OK.

/* SPLIT Transactions */
FFFFFFFFFFFFFFFFFFF                      ORIGINAL - OK.
SELECT COUNT(*) AS TotalCount
FROM ((SELECT tDataCollection.*, tServiceID.ID AS SID, 'Code' = CASE
 WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END
 FROM (tDataCollection
 LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
 WHERE tDataCollection.BatchID = 20087 AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code)
INNER JOIN tServiceSplit ON DC.SID = tServiceSplit.ServiceID
WHERE tServiceType.SplitService = 1
AND tServiceSplit.ServiceID IS NOT NULL
FFFFFFFFFFFFFFFFFFF                      ORIGINAL - OK.
GGGGGGGGGGGGGGGGGGG                      ORIGINAL OK BUT MODIFIED.

SELECT DC.ID AS "DataRefID",
 DC.SequenceNo, DC.Duration, DC.CallCode, DC.ServiceID, DC.BatchID,
 (CASE DC.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.TxnDate,7,4) + SUBSTRING(DC.TxnDate,4,2) + SUBSTRING(DC.TxnDate,1,2) + (CASE DC.TxnTime WHEN NULL THEN '' ELSE ' ' + DC.TxnTime END) END) AS "TxnDate",
 DC.AmountExGST, DC.GSTAmount, DC.AmountIncGST,
 (CASE DC.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.FromDate,7,4) + SUBSTRING(DC.FromDate,4,2) + SUBSTRING(DC.FromDate,1,2) END) AS "FromDate",
 (CASE DC.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC.ToDate,7,4) + SUBSTRING(DC.ToDate,4,2) + SUBSTRING(DC.ToDate,1,2) END) AS "ToDate",
 DC.TransactionTypeID, DC.StatusID, (CASE DC.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS "GSTFlag",
 tServiceID.ActivityID, tServiceID.CentreID, DC.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.ID AS "ServiceTypeID",
 tTransactionGroup.Description AS "TransactionGroup", tServiceType.RevenueElementID, tServiceType.ExpenseElementID,
 tGroup.CustomerID, tDialledNumber.ID AS "DialledNumberID", tOrigin.ID AS "OriginID", tDestination.ID AS "DestinationID", tRateDescription.ID AS "RateDescriptionID", DC.Peak,
 DC.Var01, DC.Var02, DC.Var03, DC.Var04, DC.Var05,
 DC.Var06, DC.Var07, DC.Var08, DC.Var09, DC.Var10,
 DC.Var11, DC.Var12, DC.Var13, DC.Var14, DC.Var15
FROM (((((((((SELECT tDataCollection.*, tServiceID.ID AS "SID",
 (CASE WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code"
FROM (tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tDataCollection.BatchID = 20000
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code)
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) ON DC.SID = tServiceID.ID)
LEFT JOIN tServiceSplit ON tServiceID.ID = tServiceSplit.ServiceID)
LEFT JOIN tDialledNumber ON DC.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC.RateDescription = tRateDescription.Description)
LEFT JOIN tTransactionType ON DC.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tServiceType.SplitService = 1
AND tServiceSplit.Active = 1
AND tServiceSplit.ServiceID IS NOT NULL
ORDER BY DC.ServiceID

GGGGGGGGGGGGGGGGGGG                      ORIGINAL OK BUT MODIFIED.















ACTUAL COMMANDS:
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, RevenueFlag, ElementID, CustomerID, DialledNumberID, OriginID, DestinationID, RateDescriptionID, Peak,
 Var01, Var02, Var03, Var04, Var05, Var06, Var07, Var08, Var09, Var10, Var11, Var12, Var13, Var14, Var15)

SELECT DC2.ID AS DataRefID, DC2.SequenceNo, DC2.Duration, DC2.CallCode, DC2.BatchID,
 (CASE DC2.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.TxnDate,7,4) + SUBSTRING(DC2.TxnDate,4,2) + SUBSTRING(DC2.TxnDate,1,2) + (CASE DC2.TxnTime WHEN NULL THEN '' ELSE ' ' + DC2.TxnTime END) END) AS "TxnDate",
 DC2.AmountExGST, DC2.GSTAmount, DC2.AmountIncGST,
 (CASE DC2.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.FromDate,7,4) + SUBSTRING(DC2.FromDate,4,2) + SUBSTRING(DC2.FromDate,1,2) END) AS "FromDate",
 (CASE DC2.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.ToDate,7,4) + SUBSTRING(DC2.ToDate,4,2) + SUBSTRING(DC2.ToDate,1,2) END) AS "ToDate",
 DC2.TransactionTypeID, DC2.StatusID, (CASE DC2.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS "GSTFlag",
 tServiceID.ActivityID, tServiceID.CentreID, DC2.SID 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, DC2.ServiceTypeID,
 (0) AS "RevenueFlag", DC2.ExpenseElementID AS "ElementID",
 tGroup.CustomerID, tDialledNumber.ID as "DialledNumberID", tOrigin.ID as "OriginID", tDestination.ID as "DestinationID", tRateDescription.ID AS "RateDescriptionID", DC2.Peak,
 DC2.Var01, DC2.Var02, DC2.Var03, DC2.Var04, DC2.Var05,
 DC2.Var06, DC2.Var07, DC2.Var08, DC2.Var09, DC2.Var10,
 DC2.Var11, DC2.Var12, DC2.Var13, DC2.Var14, DC2.Var15
FROM (((((SELECT DC1.*
FROM (SELECT DC.*, tServiceType.ID AS "ServiceTypeID", tServiceType.ExpenseElementID, tServiceType.RevenueElementID
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code", tServiceType.SplitService,
 tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20000
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService <> 1 OR (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NULL OR DC1.Active <> 1)))) AS DC2
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) ON DC2.SID = tServiceID.ID)
LEFT JOIN tDialledNumber ON DC2.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC2.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC2.RateDescription = tRateDescription.Description
WHERE DC2.ExpenseElementID IS NOT NULL
























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, RevenueFlag, ElementID, CustomerID, DialledNumberID, OriginID, DestinationID, RateDescriptionID, Peak,
 Var01, Var02, Var03, Var04, Var05, Var06, Var07, Var08, Var09, Var10, Var11, Var12, Var13, Var14, Var15)

SELECT DC2.ID AS DataRefID, DC2.SequenceNo, DC2.Duration, DC2.CallCode, DC2.BatchID,
 (CASE DC2.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.TxnDate,7,4) + SUBSTRING(DC2.TxnDate,4,2) + SUBSTRING(DC2.TxnDate,1,2) + (CASE DC2.TxnTime WHEN NULL THEN '' ELSE ' ' + DC2.TxnTime END) END) AS "TxnDate",
 DC2.AmountExGST, DC2.GSTAmount, DC2.AmountIncGST,
 (CASE DC2.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.FromDate,7,4) + SUBSTRING(DC2.FromDate,4,2) + SUBSTRING(DC2.FromDate,1,2) END) AS "FromDate",
 (CASE DC2.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.ToDate,7,4) + SUBSTRING(DC2.ToDate,4,2) + SUBSTRING(DC2.ToDate,1,2) END) AS "ToDate",
 DC2.TransactionTypeID, DC2.StatusID, (CASE DC2.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS "GSTFlag",
 tServiceID.ActivityID, tServiceID.CentreID, DC2.SID 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, DC2.ServiceTypeID,
(1) AS "RevenueFlag", DC2.RevenueElementID AS "ElementID",
 tGroup.CustomerID, tDialledNumber.ID as "DialledNumberID", tOrigin.ID as "OriginID", tDestination.ID as "DestinationID", tRateDescription.ID AS "RateDescriptionID", DC2.Peak,
 DC2.Var01, DC2.Var02, DC2.Var03, DC2.Var04, DC2.Var05,
 DC2.Var06, DC2.Var07, DC2.Var08, DC2.Var09, DC2.Var10,
 DC2.Var11, DC2.Var12, DC2.Var13, DC2.Var14, DC2.Var15
FROM (((((SELECT DC1.*
FROM (SELECT DC.*, tServiceType.ID AS "ServiceTypeID", tServiceType.ExpenseElementID, tServiceType.RevenueElementID
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code", tServiceType.SplitService,
 tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20000
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService <> 1 OR (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NULL OR DC1.Active <> 1)))) AS DC2
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) ON DC2.SID = tServiceID.ID)
LEFT JOIN tDialledNumber ON DC2.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC2.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC2.RateDescription = tRateDescription.Description
WHERE DC2.RevenueElementID IS NOT NULL





/* Splits - 08/06/2005 */
SELECT DC2.ID AS DataRefID, DC2.SequenceNo, DC2.Duration, DC2.CallCode, DC2.BatchID, DC2.ServiceID, DC2.SID,
 (CASE DC2.TxnDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.TxnDate,7,4) + SUBSTRING(DC2.TxnDate,4,2) + SUBSTRING(DC2.TxnDate,1,2) + (CASE DC2.TxnTime WHEN NULL THEN '' ELSE ' ' + DC2.TxnTime END) END) AS "TxnDate",
 DC2.AmountExGST, DC2.GSTAmount, DC2.AmountIncGST,
 (CASE DC2.FromDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.FromDate,7,4) + SUBSTRING(DC2.FromDate,4,2) + SUBSTRING(DC2.FromDate,1,2) END) AS "FromDate",
 (CASE DC2.ToDate WHEN NULL THEN NULL ELSE SUBSTRING(DC2.ToDate,7,4) + SUBSTRING(DC2.ToDate,4,2) + SUBSTRING(DC2.ToDate,1,2) END) AS "ToDate",
 DC2.TransactionTypeID, DC2.StatusID, (CASE DC2.GSTFlag WHEN 'Y' THEN 1 ELSE 0 END) AS "GSTFlag",
 tServiceID.ActivityID, tServiceID.CentreID, tServiceID.SubledgerID, tServiceID.CustomerCentreID,
 tServiceID.CustomerActivityID, tServiceID.CustomerElementID, tServiceID.CustomerSubledgerID, tServiceID.LocationID, tServiceID.UpEnd,
 tServiceID.UpMDF, tServiceID.DownEnd, tServiceID.DownMDF, tServiceID.Length, tServiceID.PersonID, DC2.ServiceTypeID,
 tTransactionGroup.Description AS "TransactionGroup", DC2.RevenueElementID, DC2.ExpenseElementID,
 tGroup.CustomerID, tDialledNumber.ID as "DialledNumberID", tOrigin.ID as "OriginID", tDestination.ID as "DestinationID", tRateDescription.ID AS "RateDescriptionID", DC2.Peak,
 DC2.Var01, DC2.Var02, DC2.Var03, DC2.Var04, DC2.Var05,
 DC2.Var06, DC2.Var07, DC2.Var08, DC2.Var09, DC2.Var10,
 DC2.Var11, DC2.Var12, DC2.Var13, DC2.Var14, DC2.Var15
FROM (((((((SELECT DC1.*
FROM (SELECT DC.*, tServiceType.ID AS "ServiceTypeID", tServiceType.ExpenseElementID, tServiceType.RevenueElementID
FROM (SELECT tDataCollection.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN
 tServiceType.Code ELSE tDataCollection.ServiceType END) AS "Code", tServiceType.SplitService,
 tServiceSplit.ServiceID AS SplitServiceID, tServiceSplit.Active, tServiceID.ID AS "SID"
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 = 20000
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (DC1.SplitService = 1 AND (DC1.SplitService = 1 AND (DC1.SplitServiceID IS NOT NULL AND DC1.Active = 1)))) AS DC2
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) ON DC2.SID = tServiceID.ID)
LEFT JOIN tDialledNumber ON DC2.DialledNumber = tDialledNumber.Description)
LEFT JOIN tOrigin ON DC2.Origin = tOrigin.Description)
LEFT JOIN tDestination ON DC2.Destination = tDestination.Description)
LEFT JOIN tRateDescription ON DC2.RateDescription = tRateDescription.Description)
LEFT JOIN tTransactionType ON DC2.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID