SELECT SUM(AmountExGST), SUM(GSTAmount), SUM(AmountIncGST)
FROM tDataCollection
WHERE BatchID = 26716

Answer: 23266.85, 2326.68, 25593.53		(Correct)



SELECT SUM(AmountExGST), SUM(GSTAmount), SUM(AmountIncGST)
FROM tTransaction
WHERE BatchID = 26716
AND RevenueFlag = 0

Answer: 23266.85, 2324.81, 25592.55		(Error: GST -1.87)



SELECT SUM(AmountExGST), SUM(GSTAmount), SUM(AmountIncGST) FROM (
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, DC2.SupplierServiceCodeID
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 = 26716
AND (tDataCollection.RevenueFlag IS NULL OR tDataCollection.RevenueFlag = 0)
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
AND DC2.ExpenseElementID > 0
) AS ABC

Answer: 22243.01, 2224.30, 24467.31		(Error: GST -1.87)



SELECT SUM(AmountExGST), SUM(GSTAmount), SUM(AmountIncGST) FROM (
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, DC2.SupplierServiceCodeID
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 = 26716
AND (tDataCollection.RevenueFlag IS NULL OR tDataCollection.RevenueFlag = 1)
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
AND DC2.RevenueElementID > 0
) AS ABC

Answer: 22243.01, 2224.30, 24467.31		(Same as previous statement)



SELECT COUNT(*) AS TotalCount
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 = 26716
 AND tDataCollection.RevenueFlag IS NULL
 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
AND tServiceSplit.Active = 1

Answer: 9546



SELECT SUM(AmountExGST), SUM(GSTAmount), SUM(AmountIncGST) FROM (
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, DC2.SupplierServiceCodeID
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 = 26716
AND tDataCollection.RevenueFlag IS NULL
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
) AS ABC

Answer: 2047.68, 204.77, 2252.45