//////////////////// 1:

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 = 21965
AND tServiceID.ServiceID IS NOT NULL) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC1
WHERE (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







////////////////////// 2:

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 = 21965
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










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 = 21965
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












////////////////////  3:

SELECT TN.BatchID, tServiceID.ServiceID, TN.DateFrom, TN.DateTo, TN.Rent, TN.Calls, TN.Other, TN.Admin, TN.Total,
(TN.Rent + TN.Calls + TN.Other) AS "SubTotal"
FROM (SELECT tTransaction.BatchID, tTransaction.ServiceID,
 Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.FromDate END) AS DateFrom,
 Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.ToDate END) AS DateTo,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS Calls,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountExGST ELSE 0 END) AS Other,
 Sum(CASE tTransactionGroup.Description WHEN 'ADMIN' THEN tTransaction.AmountExGST ELSE 0 END) AS Admin,
 Sum(tTransaction.AmountExGST) AS Total
FROM ((tTransaction
LEFT JOIN tBatch ON tTransaction.Batchid = tBatch.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 21965
AND tTransaction.StatusID IS NULL
AND tTransaction.RevenueFlag = 0
GROUP BY tTransaction.BatchID, tTransaction.ServiceID) AS TN
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID
ORDER BY tServiceID.ServiceID


AND tBatch.BatchStatusID = '+IntToStr(BATCHSTAT_ADMINOK));