// Discounts (20060106):
SELECT TN.*, tDiscount.DiscountRate
FROM (SELECT tTransaction.ServiceID, tTransaction.ServiceTypeID, Sum(tTransaction.AmountExGST) AS TOTAL,
  Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.FromDate END) AS RENTFROM,
  Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.ToDate END) AS RENTTILL,
  Max(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.ToDate END) AS CALLTILL,
  tTransaction.CentreID, tTransaction.ActivityID, tTransaction.SubledgerID,
  tTransaction.LocationID, tTransaction.PersonID, tTransaction.CustomerID,
  tTransaction.CustomerCentreID, tTransaction.CustomerActivityID, tTransaction.CustomerElementID,
  tTransaction.CustomerSubledgerID
 FROM (tTransaction
   LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
   LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
  WHERE tTransaction.BatchID = 20626
  AND tTransaction.StatusID IS NULL
  AND tTransaction.RevenueFlag = 1
 GROUP BY tTransaction.ServiceID, tTransaction.ServiceTypeID, tTransaction.CentreID,
  tTransaction.ActivityID, tTransaction.SubledgerID,
  tTransaction.LocationID, tTransaction.PersonID, tTransaction.CustomerID,
  tTransaction.CustomerCentreID, tTransaction.CustomerActivityID, tTransaction.CustomerElementID,
  tTransaction.CustomerSubledgerID) AS TN
 LEFT JOIN tDiscount ON ((TN.ServiceTypeID = tDiscount.ServiceTypeID) AND (TN.CustomerID = tDiscount.CustomerID))
WHERE (tDiscount.DiscountRate IS NOT NULL) AND (TN.TOTAL > 0)
ORDER BY TN.ServiceID





// Admin Fees (20060106):
SELECT *
FROM (SELECT (TN2.RENT + TN2.CALL + TN2.OTHER) AS TOTAL, TN2.*, tServiceType.AdminFee, tDiscount.DiscountRate,
 tDiscount.NonStdRevAdminFee, tDiscount.NonStdExpAdminFee
FROM ((SELECT TN1.ServiceID, TN1.ServiceTypeID, TN1.RevFlag,
  Sum(CASE TN1.Description WHEN 'RENT' THEN TN1.AmountExGST ELSE 0 END) AS RENT,
  Sum(CASE TN1.Description WHEN 'CALL' THEN TN1.AmountExGST ELSE 0 END) AS CALL,
  Sum(CASE TN1.Description WHEN 'OTHER' THEN TN1.AmountExGST ELSE 0 END) AS OTHER,
  Min(CASE TN1.Description WHEN 'RENT' THEN TN1.FromDate END) AS RENTFROM,
  Max(CASE TN1.Description WHEN 'RENT' THEN TN1.ToDate END) AS RENTTILL,
  Max(CASE TN1.Description WHEN 'CALL' THEN TN1.ToDate END) AS CALLTILL,
  TN1.CentreID, TN1.ActivityID, TN1.SubledgerID,
  TN1.LocationID, TN1.PersonID, TN1.CustomerID,
  TN1.CustomerCentreID, TN1.CustomerActivityID, TN1.CustomerElementID, TN1.CustomerSubledgerID
 FROM (SELECT tTransaction.ServiceID, tTransaction.ServiceTypeID, tTransaction.AmountExGST,
   tTransaction.FromDate, tTransaction.ToDate,
   tTransaction.CentreID, tTransaction.ActivityID, tTransaction.SubledgerID, tTransaction.LocationID,
   tTransaction.PersonID, tTransaction.CustomerID,
   tTransaction.CustomerCentreID, tTransaction.CustomerActivityID,  tTransaction.CustomerElementID,
   tTransaction.CustomerSubledgerID, (CASE tTransaction.RevenueFlag WHEN 1 THEN 1 ELSE 0 END) AS RevFlag,
   tTransactionGroup.Description
  FROM tTransaction LEFT JOIN 
   (tTransactionType LEFT JOIN tTransactionGroup
    ON tTransactionType.TransactionGroupID = tTransactionGroup.ID)
   ON tTransaction.TransactionTypeID = tTransactionType.ID
  WHERE tTransaction.BatchID = 20567) AS TN1
 GROUP BY TN1.ServiceID, TN1.ServiceTypeID, TN1.RevFlag, TN1.CentreID, TN1.ActivityID, TN1.SubledgerID,
  TN1.LocationID, TN1.PersonID, TN1.CustomerID,
  TN1.CustomerCentreID, TN1.CustomerActivityID, TN1.CustomerElementID, TN1.CustomerSubledgerID) AS TN2
 LEFT JOIN tServiceType ON TN2.ServiceTypeID = tServiceType.ID)
 LEFT JOIN tDiscount ON TN2.ServiceTypeID = tDiscount.ServiceTypeID AND TN2.CustomerID = tdiscount.CustomerID) AS TN3
WHERE TOTAL > 0
ORDER BY ServiceID