// 1. FULL UPDATE statement.
UPDATE tDataCollection
SET tDataCollection.AmountExGST = DC4.AmountExGST,
 tDataCollection.GSTAmount = DC4.GSTAmount,
 tDataCollection.AmountIncGST = DC4.AmountIncGST
FROM (SELECT DC3.*, CAST((DC3.AmountExGST + DC3.GSTAmount) AS money) 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 = 24036) 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



// 2. Most Inner statement
SELECT tDataCollection.ID, tDataCollection.ServiceID, 
 SUBSTRING(tDataCollection.Var09,2,2) AS "TEC"
FROM tDataCollection
LEFT JOIN tTransactionType ON tDataCollection.TransactionTypeID = tTransactionType.ID
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tDataCollection.BatchID = 24036
AND tTransactionGroup.Description LIKE 'RENT'


// 3. Next outer loop statement.
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 = 24036) AS DC0
LEFT JOIN tTransactionType ON DC0.TransactionTypeID = tTransactionType.ID
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransactionGroup.Description LIKE 'RENT'



// 4. Next outer loop.
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 = 24036) 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



// 4A. A slight change to the above to run if the above returned nothing.
//    Changes are (a) INNER join with tRate changed to LEFT join, and
//                (b) The 2nd last line is removed - to see where empty costs are returned.
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 = 24036) 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)
LEFT JOIN tRate ON ((SI1.ServiceTypeID = tRate.ServiceTypeID) AND (DC1.Length >= tRate.Length))
WHERE DC1.Length IS NOT NULL
GROUP BY  DC1.ID



// 5. Next outer loop.
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 = 24036) 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




// 5A. Like 5, but with Mods the same as 4A above.
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 = 24036) 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)
LEFT JOIN tRate ON ((SI1.ServiceTypeID = tRate.ServiceTypeID) AND (DC1.Length >= tRate.Length))
WHERE DC1.Length IS NOT NULL
GROUP BY  DC1.ID) AS DC2
INNER JOIN tDataCollection ON tDataCollection.ID = DC2.ID




// 6. Most outer loop.
SELECT DC3.*, CAST((DC3.AmountExGST + DC3.GSTAmount) AS money) 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 = 24036) 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




// 6A. Like 6, but with Mods the same as 4A above.
SELECT DC3.*, CAST((DC3.AmountExGST + DC3.GSTAmount) AS money) 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 = 24036) 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)
LEFT JOIN tRate ON ((SI1.ServiceTypeID = tRate.ServiceTypeID) AND (DC1.Length >= tRate.Length))
WHERE DC1.Length IS NOT NULL
GROUP BY  DC1.ID) AS DC2
INNER JOIN tDataCollection ON tDataCollection.ID = DC2.ID) AS DC3




// 7. The last statement is the first (1) as it combines #6 with the UPDATE command.