// SQL ftest for Dubious error code = 4 (10% variance in rent or calls):

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 = 21965
 AND tTransaction.RevenueFlag <> 0) AS TN
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID) AS SID
WHERE SID.RentChangePerCent > 10
OR SID.RentChangePerCent < -10
OR SID.CallChangePerCent > 10
OR SID.CallChangePerCent < -10







// Test if there is already an entry for this combination:

SELECT * FROM tDubiousResult
WHERE BatchID = 21965
AND DubiousTypeID = <<DubiousTypeID>>
AND ServiceID = <<ServiceID_ID>>
AND CustomerID = <<CustomerID>>






// Record the dubious error into the result set:

INSERT INTO tDubiousResult
(BatchID, ServiceID, CustomerID, DubiousTypeID, UserID)
VALUES ( 21965, <<ServiceID_ID>>, <<CustomerID>>, 4, <<User_ID>> )