// Dubious Test //
SELECT SID.ServiceID, SID.CustomerID,
 SID.CurrentRent, SID.PreviousRent, SID.RentChange, SID.RentChangePerCent,
 SID.CurrentCalls, SID.PreviousCalls, SID.CallChange, CallChangePerCent
FROM (SELECT TN.ServiceID, TN.CustomerID, tServiceID.CurrentRent, tServiceID.PreviousRent,
 (tServiceID.CurrentRent - tServiceID.PreviousRent) AS RentChange,
 CASE
  WHEN tServiceID.PreviousRent > 0 THEN (tServiceID.CurrentRent - tServiceID.PreviousRent) / tServiceID.PreviousRent * 100
  WHEN tServiceID.PreviousRent < 0 THEN (tServiceID.PreviousRent - tServiceID.CurrentRent) / tServiceID.PreviousRent * 100
  WHEN tServiceID.PreviousRent = 0 AND tServiceID.CurrentRent > 0 THEN 100.00
  WHEN tServiceID.PreviousRent = 0 AND tServiceID.CurrentRent < 0 THEN -100.00
  ELSE 0.00
 END AS RentChangePerCent,
 CASE
  WHEN tServiceID.PreviousRent > 0 THEN tServiceID.PreviousRent
  ELSE tServiceID.PreviousRent * -1
 END AS CalcRent,
 tServiceID.CurrentCalls, tServiceID.PreviousCalls,
 (tServiceID.CurrentCalls - tServiceID.PreviousCalls) AS CallChange,
 CASE
  WHEN tServiceID.PreviousCalls > 0 THEN (tServiceID.CurrentCalls - tServiceID.PreviousCalls) / tServiceID.PreviousCalls * 100
  WHEN tServiceID.PreviousCalls < 0 THEN (tServiceID.PreviousCalls - tServiceID.CurrentCalls) / tServiceID.PreviousCalls * 100
  WHEN tServiceID.PreviousCalls = 0 AND tServiceID.CurrentCalls > 0 THEN 100
  WHEN tServiceID.PreviousCalls = 0 AND tServiceID.CurrentCalls < 0 THEN -100
  ELSE 0
 END AS CallChangePerCent,
 CASE
  WHEN tServiceID.PreviousCalls > 0 THEN tServiceID.PreviousCalls
  ELSE tServiceID.PreviousCalls * -1
 END AS CalcCalls
FROM (SELECT DISTINCT tTransaction.ServiceID, tTransaction.CustomerID
 FROM tTransaction
 WHERE tTransaction.BatchID = ' + IntToStr(nBatch));
 AND tTransaction.RevenueFlag <> 0) AS TN
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID) AS SID
WHERE SID.RentChangePerCent > '+sVariance);
OR SID.RentChangePerCent < -'+sVariance);
OR SID.CallChangePerCent > '+sVariance);
OR SID.CallChangePerCent < -'+sVariance);














SELECT TN1.RentAmt, TN1.CallAmt, TN1.OtherAmt, (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt) AS TotalAll, tStatus.Status, tCustomer.ShipTo,
 tPerson.Person, tLocation.Description AS Location,
 tCentre.Description AS Centre, tActivity.Description AS Activity, tElement.ElementCode AS Element, tSubledger.Description AS Subledger,
 tDepartment.Description AS Department, tServiceID.ServiceID AS ServiceName, tServiceID.Description AS ServiceDesc,
 tServiceID.InstallationDate, tServiceType.Code
FROM ((((((((((

SELECT tTransaction.ServiceID, tTransaction.ServiceTypeID,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS RentAmt,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS CallAmt
FROM ((tTransaction
LEFT JOIN tServiceID ON tTransaction.ServiceID = tServiceID.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 20621
AND tTransaction.RevenueFlag <> 1
AND tServiceID.ServiceTypeID = tTransaction.ServiceTypeID
GROUP BY tTransaction.ServiceID, tTransaction.ServiceTypeID

) AS TN1
LEFT JOIN tStatus ON TN1.StatusID = tStatus.ID)
LEFT JOIN tCustomer ON TN1.CustomerID = tCustomer.ID)
LEFT JOIN tPerson ON TN1.PersonID = tPerson.ID)
LEFT JOIN tLocation ON TN1.LocationID = tLocation.ID)
LEFT JOIN (tCentre LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID) ON TN1.CustomerCentreID = tCentre.ID)
LEFT JOIN tActivity ON TN1.CustomerActivityID = tActivity.ID)
LEFT JOIN tElement ON TN1.CustomerElementID = tElement.ID)
LEFT JOIN tSubledger ON TN1.CustomerSubledgerID = tSubledger.ID)
LEFT JOIN tServiceID ON TN1.ServiceID = tServiceID.ID)
LEFT JOIN tServiceType ON TN1.ServiceTypeID = tServiceType.ID
WHERE tStatus.Status IS NULL
OR tStatus.Status NOT LIKE '%Information%'
ORDER BY tServiceID.ServiceID














UPDATE tServiceID
SET tServiceID.PreviousRent = tServiceID.CurrentRent,
 tServiceID.PreviousCalls = tServiceID.CurrentCalls,
 tServiceID.CurrentRent = TT.RentAmt,
 tServiceID.CurrentCalls = TT.CallAmt
FROM (SELECT tTransaction.ServiceID, tTransaction.ServiceTypeID,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS RentAmt,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS CallAmt
FROM ((tTransaction
LEFT JOIN tServiceID ON tTransaction.ServiceID = tServiceID.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 20622
AND tTransaction.RevenueFlag <> 1
AND tServiceID.ServiceTypeID = tTransaction.ServiceTypeID
GROUP BY tTransaction.ServiceID, tTransaction.ServiceTypeID) AS TT
WHERE tServiceID.ID = TT.ServiceID
AND tServiceID.ServiceTypeID = TT.ServiceTypeID