SELECT BH.*
FROM (SELECT tBatch.ID AS BatchID, tBatchStatus.Description AS BatchStatus, tBatchType.Description AS BatchType,
 tBatch.BatchStatusID, (tBatch.TotalPayable - tBatch.GSTAmount) AS AmountExGST,
 tBatch.GSTAmount, tBatch.TotalPayable, tBatch.TotalAdjustments
FROM (tBatch
LEFT JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.ID)
LEFT JOIN tBatchStatus ON tBatch.BatchStatusID = tBatchStatus.ID
WHERE tBatch.BillingPeriod = '2004/03') AS BH
ORDER BY BH.BatchID




SELECT BH.*
FROM (SELECT tBatch.ID AS BatchID, tBatchStatus.Description AS BatchStatus, tBatch.BatchStatusID,
 tBatchType.Description AS BatchType, (tBatch.TotalPayable - tBatch.GSTAmount) AS AmountExGST,
 tBatch.GSTAmount, tBatch.TotalPayable, tBatch.TotalAdjustments
FROM (tBatch
LEFT JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.ID)
LEFT JOIN tBatchStatus ON tBatch.BatchStatusID = tBatchStatus.ID
WHERE tBatch.BillingPeriod = '2005/02') AS BH
ORDER BY BH.BatchID DESC


SELECT TN.BatchID, tServiceID.ServiceID, TN.DateFrom, TN.DateTo, TN.Rent, TN.Calls, TN.Other, TN.Total
FROM (SELECT tTransaction.BatchID, tTransaction.ServiceID,
 Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.FromDate END) AS DateFrom,
 Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.ToDate END) AS DateTo,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountIncGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountIncGST ELSE 0 END) AS Calls,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountIncGST ELSE 0 END) AS Other,
 Sum(tTransaction.AmountIncGST) AS Total
FROM (tTransaction
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 14501
AND tTransaction.StatusID IS NULL
AND tTransaction.RevenueFlag = 0
GROUP BY tTransaction.BatchID, tTransaction.ServiceID) AS TN
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID
ORDER BY tServiceID.ServiceID



SELECT TN1.*
FROM (SELECT TN.BatchID, tServiceID.ServiceID, tServiceType.Code, TN.DateFrom, TN.DateTo, TN.Rent, TN.Calls, TN.Other, TN.Admin,
tDiscount.DiscountRate, tDiscount.NonStdRevAdminFee, tDiscount.NonStdExpAdminFee
FROM (SELECT tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID,
 Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.FromDate END) AS DateFrom,
 Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.ToDate END) AS DateTo,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS Rent,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS Calls,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountExGST ELSE 0 END) AS Other,
 Sum(CASE tTransactionGroup.Description WHEN 'ADMIN' THEN tTransaction.AmountExGST ELSE 0 END) AS Admin,
 tTransaction.CustomerID
FROM ((tTransaction
LEFT JOIN tBatch ON tTransaction.Batchid = tBatch.ID)
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tBatch.BillingPeriod = '2004/04'
AND tBatch.BatchStatusID < 6
AND tTransaction.StatusID IS NULL
AND tTransaction.RevenueFlag <> 0
GROUP BY tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID, tTransaction.CustomerID) AS TN
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
LEFT JOIN tDiscount ON ((TN.CustomerID = tDiscount.CustomerID) AND (tServiceType.ID = tDiscount.ServiceTypeID))) AS TN1
ORDER BY TN1.ServiceID