/* New tDataCollection - okay 15/06/2005 */
SELECT DC3.*
FROM (SELECT DC2.*, tCriticalType.Description AS "Reason"
FROM (SELECT DC1.*, (DC1.RENT + DC1.CALL + DC1.OTHER) AS "TotalExGST", tServiceType.AdminFee
FROM ((SELECT DC0.ServiceID, DC0.StatusID, DC0.ServiceTypeID, DC0.Code,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN DC0.AmountExGST ELSE 0 END) AS "Rent",
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN DC0.AmountExGST ELSE 0 END) AS "Call",
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN DC0.AmountExGST ELSE 0 END) AS "Other",
 Sum(CASE DC0.GSTAmount WHEN NULL THEN 0 ELSE DC0.GSTAmount END) AS "GST",
 Sum(CASE DC0.AmountIncGST WHEN NULL THEN 0 ELSE DC0.AmountIncGST END) AS "TotalIncGST",
 Min(CASE tTransactionGroup.Description WHEN 'RENT' THEN DC0.FromDate END) AS "RentFrom",
 Max(CASE tTransactionGroup.Description WHEN 'RENT' THEN DC0.ToDate END) AS "RentTill",
 Max(CASE tTransactionGroup.Description WHEN 'CALL' THEN DC0.ToDate END) AS "CallTill"
FROM ((SELECT DC.*, tServiceType.ID AS ServiceTypeID
FROM (SELECT tDataCollection.*, 'Code' = CASE
  WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code ELSE RTRIM(SUBSTRING(tDataCollection.ServiceType,1,5)) END
FROM (tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tDataCollection.BatchID = 20090) AS DC
LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC0
LEFT JOIN tTransactionType ON DC0.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
GROUP BY DC0.ServiceID, DC0.StatusID, DC0.ServiceTypeID, DC0.Code) AS DC1
 LEFT JOIN tServiceID ON DC1.ServiceID = tServiceID.ServiceID)
 LEFT JOIN tServiceType ON DC1.ServiceTypeID = tServiceType.ID) AS DC2
 LEFT JOIN tCriticalType ON DC2.StatusID = tCriticalType.ID) AS DC3
ORDER BY DC3.ServiceID


/* New admin summary - okay 15/06/2005 */
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, (TN.Rent + TN.Calls + TN.Other) AS "SubTotal",
(TN.Rent + TN.Calls + TN.Other + TN.Admin) AS "Total"
FROM (SELECT tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID, tTransaction.ServiceTypeID,
 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.ID = 20088
AND tBatch.BatchStatusID > 4
AND tBatch.BatchStatusID < 8
AND tTransaction.StatusID IS NULL
AND tTransaction.RevenueFlag = 1
GROUP BY tBatch.BillingPeriod, tTransaction.BatchID, tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.ServiceTypeID) 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 (TN.ServiceTypeID = tDiscount.ServiceTypeID))) AS TN1
ORDER BY TN1.ServiceID



/* New Release transactions - okay 15/06/2005 */
SELECT TN.BatchID, tServiceID.ServiceID, TN.DateFrom, TN.DateTo, TN.Rent, TN.Calls, TN.Other, TN.Admin, TN.Total,
(TN.Rent + TN.Calls + TN.Other) AS "SubTotal"
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.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,
 Sum(tTransaction.AmountExGST) AS Total
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 tTransaction.BatchID = 20088
AND tBatch.BatchStatusID = 10
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



/* New Dubious Summary - okay 15/06/2005 */
SELECT DR.*
FROM (SELECT TN.*, tDubiousResult.DubiousTypeID, tDubiousResult.ID AS DID, tServiceID.ServiceID AS Service,
 tDubiousType.Description AS DubiousError, tDubiousResolution.Description AS Resolution
FROM ((((SELECT tTransaction.BatchID, tTransaction.ServiceID, tBatch.BatchTypeID,
 Sum(tTransaction.AmountExGST) AS TotalExGST, Sum(tTransaction.GSTAmount) AS TotalGST,
 Sum(tTransaction.AmountIncGST) AS TotalIncGST, COUNT(tTransaction.ServiceID) AS RecordCount
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 tTransaction.BatchID = 20088
AND (tBatch.BatchStatusID > 7 AND tBatch.BatchStatusID < 11)
AND tTransactionGroup.Description NOT LIKE 'ADMIN'
AND tTransactionType.Description NOT LIKE '%Fee %'
AND tTransaction.StatusID IS NULL
GROUP BY tTransaction.BatchID, tBatch.BatchTypeID, tTransaction.ServiceID) AS TN
INNER JOIN tDubiousResult ON ((TN.BatchID = tDubiousResult.BatchID) AND (TN.ServiceID = tDubiousResult.ServiceID)))
LEFT JOIN tDubiousType ON tDubiousResult.DubiousTypeID = tDubiousType.ID)
LEFT JOIN tDubiousResolution ON tDubiousResult.ResolutionID = tDubiousResolution.ID)
LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID
WHERE tDubiousResult.DubiousTypeID IS NOT NULL) AS DR
ORDER BY DR.BatchID, DR.ServiceID