SELECT TOP 20000 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, 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.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.*, '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 = 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.ServiceID = tServiceID.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 tServiceID.ServiceID IS NOT NULL
AND tServiceType.SplitService = 1
ORDER BY DC.ID


/* Totals */
SELECT Sum(case when DC1.AmountExGST IS NULL then 0 else DC1.AmountExGST end) AS AmtExGST,
 Sum(case when DC1.GSTAmount IS NULL then 0 else DC1.GSTAmount end) AS GSTAmt,
 Sum(case when DC1.AmountIncGST IS NULL then 0 else DC1.AmountIncGST end) AS AmtIncGST
FROM (SELECT DC.AmountExGST, DC.GSTAmount, DC.AmountIncGST
FROM ((((((((SELECT tDataCollection.*, '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 = 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.ServiceID = tServiceID.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 tServiceID.ServiceID IS NOT NULL
AND tServiceType.SplitService = 1) AS DC1

and tTransactionGroup.Description like 'RENT'




/* SQL_1: Actual Final SQL for only Expenses */
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 = 20448
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


/* SQL_2: Actual Final SQL for only Revenue */
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 = 20448
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


/* SQL_3: Actual Final SQL for Split Charges */
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 = 20448
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
ORDER BY DC2.ID