// SQL that created Revenue transactions in tDataCollection for SmartBus:

INSERT INTO tDataCollection
(SequenceNo, ServiceID, BatchID,
 TxnDate, TxnTime, Origin,
 Duration, CallCode, RateDescription,
 FromDate, ToDate, TransactionTypeID,
 AmountExGST, GSTAmount, GSTFlag, AmountIncGST,
 RevenueFlag, Var01, Var02, Var03, Var04)
SELECT DC4.*
FROM (SELECT tDataCollection.SequenceNo, tDataCollection.ServiceID, tDataCollection.BatchID,
 tDataCollection.TxnDate, tDataCollection.TxnTime, tDataCollection.Origin,
 tDataCollection.Duration, tDataCollection.CallCode, tDataCollection.RateDescription,
 tDataCollection.FromDate, tDataCollection.ToDate, tDataCollection.TransactionTypeID,
 DC3.AmountExGST, DC3.GSTAmount, DC3.GSTFlag,
 CAST((DC3.AmountExGST + DC3.GSTAmount) AS money) AS "AmountIncGST", (1) AS "RevenueFlag",
 tDataCollection.Var01, CONVERT(varchar,DC3.Cost,2) AS "Var02",
 CONVERT(varchar,DC3.AmountExGST,2) AS "Var03", (41.02) AS Var04
FROM (SELECT DC2.*,
 CAST((CASE WHEN DC2.GSTFlag LIKE 'Y' THEN (DC2.AmountExGST * 0.1) ELSE 0 END) AS money) AS "GSTAmount"
FROM (SELECT DC1.DataCollectionID, DC1.Cost,
 (CAST(tDataCollection.Var01 AS Integer)*Cost) AS "AmountExGST",
 (CASE WHEN DC1.CustomerID = 499 THEN 'N' ELSE 'Y' END) AS "GSTFlag"
FROM (SELECT DC0.*, Min(tRate.Cost) AS "Cost"
FROM (SELECT tDataCollection.ID AS "DataCollectionID",
 tServiceID.ServiceTypeID, tGroup.CustomerID
FROM tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID
LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID
LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID
LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID
WHERE tDataCollection.BatchID = 22847
AND tDataCollection.RevenueFlag = 0) AS DC0
LEFT JOIN tRate ON ((DC0.ServiceTypeID = tRate.ServiceTypeID) AND (41.02 >= tRate.Length))
GROUP BY DC0.DataCollectionID, DC0.ServiceTypeID, DC0.CustomerID) AS DC1
LEFT JOIN tDataCollection ON DC1.DataCollectionID = tDataCollection.ID) AS DC2) AS DC3
LEFT JOIN tDataCollection ON DC3.DataCollectionID = tDataCollection.ID) AS DC4




// To get the Rate for each ServiceTypeID:

SELECT DC1.*, CAST(CAST(Cost AS real) AS varchar) AS "Var02"
FROM (SELECT DC0.ServiceTypeID, Max(tRate.Cost) AS "Cost"
FROM (SELECT tServiceID.ServiceTypeID
FROM tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID
WHERE tDataCollection.BatchID = 22847
AND tDataCollection.RevenueFlag = 0) AS DC0
LEFT JOIN tRate ON ((DC0.ServiceTypeID = tRate.ServiceTypeID)
 AND (tRate.Active = 1)
 AND (41.02 >= tRate.Length))
GROUP BY DC0.ServiceTypeID) AS DC1
WHERE DC1.Cost <> 0



// To update the tBatch data:

UPDATE tBatch
SET TotalRevenue = BatchRate.RevenueAmt,
 TotalCredits = ,
 CreditsToCustomers = ,
 OrderNumber = ,
 RequisitionNumber = 
FROM (SELECT Sum(tDataCollection.AmountExGST) AS "RevenueAmt"
FROM tDataCollection
WHERE tDataCollection.BatchID = 23152
AND tDataCollection.RevenueFlag = 1) AS BatchRate
WHERE tBatch.ID = 23152
 


SELECT tDataCollection.SequenceNo, tDataCollection.ServiceID, tDataCollection.BatchID,
 tDataCollection.TxnDate, tDataCollection.TxnTime, tDataCollection.Origin,
 tDataCollection.Duration, tDataCollection.CallCode, tDataCollection.RateDescription,
 tDataCollection.FromDate, tDataCollection.ToDate, tDataCollection.TransactionTypeID,
 DC3.AmountExGST, DC3.GSTAmount, DC3.GSTFlag,
 CAST((DC3.AmountExGST + DC3.GSTAmount) AS money) AS "AmountIncGST", (1) AS "RevenueFlag",
 tDataCollection.Var01, CONVERT(varchar,DC3.Cost,2) AS "Var02",
 CONVERT(varchar,DC3.AmountExGST,2) AS "Var03", ('+sGByte+') AS Var04
FROM (SELECT DC2.*,
 CAST((CASE WHEN DC2.GSTFlag LIKE 'Y' THEN (DC2.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"
FROM (SELECT DC1.DataCollectionID, DC1.Cost,
 (CAST(tDataCollection.Var01 AS Integer)*Cost) AS "AmountExGST",
 (CASE WHEN DC1.CustomerID = '+sVRTID+' THEN 'N' ELSE 'Y' END) AS "GSTFlag"

  // Use Min() rate a/c price is reduced for more usage.
FROM (SELECT DC0.*, Min(tRate.Cost) AS "Cost"
FROM (SELECT tDataCollection.ID AS "DataCollectionID",
 tServiceID.ServiceTypeID, tGroup.CustomerID
FROM tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID
LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID
LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID
LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID
WHERE tDataCollection.BatchID = '+sBatchID);
AND tDataCollection.RevenueFlag = 0) AS DC0
LEFT JOIN tRate ON ((DC0.ServiceTypeID = tRate.ServiceTypeID) AND ('+sGByte+' >= tRate.Length))
GROUP BY DC0.DataCollectionID, DC0.ServiceTypeID, DC0.CustomerID) AS DC1
LEFT JOIN tDataCollection ON DC1.DataCollectionID = tDataCollection.ID) AS DC2) AS DC3
LEFT JOIN tDataCollection ON DC3.DataCollectionID = tDataCollection.ID




SELECT CAST(DC1.ServiceTypeID AS varchar) AS "ServiceType",
 CAST(CAST(DC1.Cost AS real) AS varchar) AS "RateCost"
FROM (SELECT DC0.ServiceTypeID, Max(tRate.Cost) AS "Cost"
FROM (SELECT tServiceID.ServiceTypeID
FROM tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID
WHERE tDataCollection.BatchID = 23152
AND tDataCollection.RevenueFlag = 0) AS DC0
LEFT JOIN tRate ON ((DC0.ServiceTypeID = tRate.ServiceTypeID)
 AND (tRate.Active = 1)
 AND (41.02 >= tRate.Length))
GROUP BY DC0.ServiceTypeID) AS DC1
WHERE DC1.Cost <> 0