SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, RateDescription,
 AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, ToDate, TransactionTypeID,
 Var01, Var02, Var03,
 Var04, Var05, Var06, Var07, Var08, Var09, Var10
INTO tDC_Temprjc2006
FROM (

SELECT SID2.*, CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST"
FROM (SELECT SID1.*,
 CAST((CASE WHEN SID1.GSTFlag LIKE 'Y' THEN (SID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"
FROM (SELECT SID.*, SI.ServiceID, ''Network Access'' AS "RateDescription", CAST('+sBatchID+' AS int) AS "BatchID",
 ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate",
 CAST('+sTransactionID+' AS int) AS "TransactionTypeID",
 CAST(((CASE WHEN SID.Invoiced IS NULL THEN SID.Cost ELSE SID.Invoiced END)*SID.Rate/12*tServiceType.Frequency) AS money) AS "AmountExGST",
 (CASE WHEN tGroup.CustomerID = '+sVRTID+' THEN 'N' ELSE 'Y' END) AS "GSTFlag",
 CAST(SID.Cost AS varchar) AS "Var01",
 CAST(SID.Cost100 AS varchar) AS "Var02", CAST(SID.Invoiced AS varchar) AS "Var03",
 CAST(SID.ServiceLen AS varchar) AS "Var04",
 SI.UpMDF AS "Var05", SI.DownMDF AS "Var06",
 RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var07", CAST(SI.StatusID AS varchar) AS "Var08", tStatus.Status AS "Var09", SI.SACCnumber AS "Var10"
FROM (SELECT SI0.ID, SI1.Rate, Max(tRate.Cost) AS "Cost", Max(tRate.Cost100) AS "Cost100",
 SI0.Length AS "ServiceLen", SI0.Invoiced
FROM (SELECT SC.ID, SC.Rate
 FROM (SELECT SC1.*, (CASE tDiscountedRate.Rate WHEN NULL THEN 100.0 ELSE tDiscountedRate.Rate END) AS "Rate"
 FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID,
 tCustomer.ID AS "CustomerID", (CASE
  WHEN ST.Frequency IS NULL THEN 1
  WHEN ST.Frequency = 0 THEN 1
 WHEN (CAST(Month(tServiceID.InstallationDate)-'+sMonth+' AS Real) / ST.Frequency) =
  ROUND((Month(tServiceID.InstallationDate)-'+sMonth+') / ST.Frequency,0,0) THEN 0
  ELSE 1
 END) AS "Period"
 FROM tServiceID
 LEFT JOIN tServiceType AS ST ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1))
 LEFT JOIN tCentre ON ((tServiceID.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))
 LEFT JOIN tDepartment ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))
 LEFT JOIN tGroup ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))
 LEFT JOIN tCustomer ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1))
 WHERE ST.SystemID = '+sSystemID);
 AND tServiceID.StatusID IN '+sStatusID);
 AND tServiceID.Active = 1
 AND tCustomer.InvoiceServices = 1) AS SC1
LEFT JOIN tDiscountedRate ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND
 (SC1.CustomerID = tDiscountedRate.CustomerID) AND (tDiscountedRate.Active = 1))
WHERE SC1.Period = 0) AS SC) AS SI1
INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID
LEFT JOIN tRate ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) AND (SI0.Length >= tRate.Length))
GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID
INNER JOIN tServiceID AS SI ON SID.ID = SI.ID
LEFT JOIN tServiceType ON SI.ServiceTypeID = tServiceType.ID
LEFT JOIN tStatus ON SI.StatusID = tStatus.ID
 LEFT JOIN tCentre ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))
 LEFT JOIN tDepartment ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))
 LEFT JOIN tGroup ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))) AS SID1
WHERE SID1.AmountExGST IS NOT NULL) AS SID2) AS SIF






// Control loop:
SELECT *
FROM tDataCollection AS DC1
LEFT JOIN tServiceID AS SI0 ON DC0.ServiceID = SI0.ServiceID
LEFT JOIN tRate ON ((SIO.ServiceTypeID + tRate.ServiceTypeID) AND
 (DC0. = tRate.Length))

// Inner SQL statement:
SELECT DC0.ServiceID,
 (CASE WHEN DC0.Var09 IS NULL THEN 0
   WHEN DC0.Var09 = 'XX' THEN 0
   WHEN DC0.Var09 = 'NN' THEN NULL
   ELSE CAST(DC0.Var09, int) END) AS Length
FROM tDataCollection AS DC0
LEFT JOIN tTransactionType ON DC0.TransactionTypeID = tTransactionType.ID
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE DC0.BatchID = 11111
AND tTransactionGroup.Description LIKE 'RENT'





// Inner Loop - data to be updated (AmountExGST) to be linked via tDataCollection(DC1).ID:
SELECT DC1.ID, DC1.ServiceID, Max(tRate.Cost) AS "AmountExGST"
FROM ((SELECT DC0.ID, DC0.ServiceID,
 (CASE WHEN DC0.Var09 IS NULL THEN 0
   WHEN DC0.Var09 = 'XX' THEN 0
   WHEN DC0.Var09 = 'NN' THEN NULL
   WHEN DC0.Var09 = '' THEN 0
   ELSE CAST(DC0.Var09 AS int) END) AS "Length"
FROM tDataCollection AS DC0
LEFT JOIN tTransactionType ON DC0.TransactionTypeID = tTransactionType.ID
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE DC0.BatchID = 11111
AND tTransactionGroup.Description LIKE 'RENT') AS DC1
LEFT JOIN tServiceID AS SI1 ON DC1.ServiceID = SI1.ServiceID)
INNER JOIN tRate ON ((SI1.ServiceTypeID = tRate.ServiceTypeID) AND (DC1.Length >= tRate.Length))
WHERE DC1.Length IS NOT NULL
AND tRate.Cost IS NOT NULL
GROUP BY  DC1.ID, DC1.ServiceID




// Conditions and values for totals to be used in the UPDATE command:
SELECT DC3.*, CAST((DC3.AmountExGST + DC3.GSTAmount) AS money) AS "AmountIncGST"
FROM (SELECT DC2.*, CAST((CASE WHEN tDataCollection.GSTFlag LIKE 'Y' THEN (DC2.AmountExGST * 0.1) ELSE 0 END) AS money) AS "GSTAmount"
FROM (SELECT DC1.ID, DC1.ServiceID, Max(tRate.Cost) AS "AmountExGST"
FROM ((SELECT DC0.ID, DC0.ServiceID,
 (CASE WHEN DC0.Var09 IS NULL THEN 0
   WHEN DC0.Var09 = 'XX' THEN 0
   WHEN DC0.Var09 = 'NN' THEN NULL
   WHEN DC0.Var09 = '' THEN 0
   ELSE CAST(DC0.Var09 AS int) END) AS "Length"
FROM tDataCollection AS DC0
LEFT JOIN tTransactionType ON DC0.TransactionTypeID = tTransactionType.ID
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE DC0.BatchID = 11111
AND tTransactionGroup.Description LIKE 'RENT') AS DC1
LEFT JOIN tServiceID AS SI1 ON DC1.ServiceID = SI1.ServiceID)
INNER JOIN tRate ON ((SI1.ServiceTypeID = tRate.ServiceTypeID) AND (DC1.Length >= tRate.Length))
WHERE DC1.Length IS NOT NULL
AND tRate.Cost IS NOT NULL
GROUP BY  DC1.ID, DC1.ServiceID) AS DC2
INNER JOIN tDataCollection ON tDataCollection.ID = DC2.ID) AS DC3




// Final UPDATE statement:

UPDATE tDataCollection
SET tDataCollection.AmountExGST = DC4.AmountExGST,
 tDataCollection.GSTAmount = DC4.GSTAmount,
 tDataCollection.AmountIncGST = DC4.AmountIncGST
FROM (SELECT DC3.*, (DC3.AmountExGST + DC3.GSTAmount) AS "AmountIncGST"
FROM (SELECT DC2.ID, DC2.AmountExGST,
 CAST((CASE WHEN tDataCollection.GSTFlag LIKE 'Y' THEN (DC2.AmountExGST * 0.1) ELSE 0 END) AS money) AS "GSTAmount"
FROM (SELECT DC1.ID, (Max(tRate.Cost)/12) AS "AmountExGST"
FROM ((SELECT DC0.ID, DC0.ServiceID, (CASE 
   WHEN DC0.TEC IS NULL THEN 0
   WHEN DC0.TEC = 'XX' THEN 0
   WHEN DC0.TEC = 'NN' THEN NULL
   WHEN DATALENGTH(RTRIM(DC0.TEC)) = 0 THEN 0
   ELSE CAST(DC0.TEC AS int) END) AS "Length"
FROM (SELECT tDataCollection.ID, tDataCollection.ServiceID, tDataCollection.TransactionTypeID,
 SUBSTRING(tDataCollection.Var09,2,2) AS "TEC"
FROM tDataCollection
WHERE tDataCollection.BatchID = 11111) AS DC0
LEFT JOIN tTransactionType ON DC0.TransactionTypeID = tTransactionType.ID
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransactionGroup.Description LIKE 'RENT') AS DC1
LEFT JOIN tServiceID AS SI1 ON DC1.ServiceID = SI1.ServiceID)
INNER JOIN tRate ON ((SI1.ServiceTypeID = tRate.ServiceTypeID) AND (DC1.Length >= tRate.Length))
WHERE DC1.Length IS NOT NULL
AND tRate.Cost IS NOT NULL
GROUP BY  DC1.ID) AS DC2
INNER JOIN tDataCollection ON tDataCollection.ID = DC2.ID) AS DC3) AS DC4
WHERE tDataCollection.ID = DC4.ID



// Final UPDATE statement with variables:

UPDATE tDataCollection
SET tDataCollection.AmountExGST = DC4.AmountExGST,
 tDataCollection.GSTAmount = DC4.GSTAmount,
 tDataCollection.AmountIncGST = DC4.AmountIncGST
FROM (SELECT DC3.*, (DC3.AmountExGST + DC3.GSTAmount) AS "AmountIncGST"
FROM (SELECT DC2.ID, DC2.AmountExGST,
 CAST((CASE WHEN tDataCollection.GSTFlag LIKE 'Y' THEN (DC2.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"
FROM (SELECT DC1.ID, (Max(tRate.Cost)/12) AS "AmountExGST"
FROM ((SELECT DC0.ID, DC0.ServiceID, (CASE 
   WHEN DC0.TEC IS NULL THEN 0
   WHEN DC0.TEC = 'XX' THEN 0
   WHEN DC0.TEC = 'NN' THEN NULL
   WHEN DATALENGTH(RTRIM(DC0.TEC)) = 0 THEN 0
   ELSE CAST(DC0.TEC AS int) END) AS "Length"
FROM (SELECT tDataCollection.ID, tDataCollection.ServiceID, tDataCollection.TransactionTypeID,
 SUBSTRING(tDataCollection.Var09,2,2) AS "TEC"
FROM tDataCollection
WHERE tDataCollection.BatchID = 11111) AS DC0
LEFT JOIN tTransactionType ON DC0.TransactionTypeID = tTransactionType.ID
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransactionGroup.Description LIKE 'RENT') AS DC1
LEFT JOIN tServiceID AS SI1 ON DC1.ServiceID = SI1.ServiceID)
INNER JOIN tRate ON ((SI1.ServiceTypeID = tRate.ServiceTypeID) AND (DC1.Length >= tRate.Length))
WHERE DC1.Length IS NOT NULL
AND tRate.Cost IS NOT NULL
GROUP BY  DC1.ID) AS DC2
INNER JOIN tDataCollection ON tDataCollection.ID = DC2.ID) AS DC3) AS DC4
WHERE tDataCollection.ID = DC4.ID