/* tDataCollection with ShipTo, SQL 050623 - Okay */
SELECT DC3.*
FROM (SELECT DC2.*, tCriticalType.Description AS "Reason"
FROM (SELECT DC1.*, (DC1.RENT + DC1.CALL + DC1.OTHER) AS "TotalExGST", tServiceType.AdminFee,
 tCustomer.ShipTo
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.*, (CASE WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code
 ELSE RTRIM(SUBSTRING(tDataCollection.ServiceType,1,5)) END) AS "Code"
FROM (tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID
WHERE tDataCollection.BatchID = 20088) 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 tCentre ON tServiceID.CustomerCentreID = tCentre.ID)
 LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID)
 LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID)
 LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID)
 LEFT JOIN tServiceType ON DC1.ServiceTypeID = tServiceType.ID) AS DC2
 LEFT JOIN tCriticalType ON DC2.StatusID = tCriticalType.ID) AS DC3
ORDER BY DC3.ServiceID


/* Admin with ShipTo, SQL 050623 - Okay */
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", tCustomer.ShipTo
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 = 20089
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 tCentre ON tServiceID.CustomerCentreID = tCentre.ID)
LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID)
LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID)
LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID)
LEFT JOIN tDiscount ON ((TN.CustomerID = tDiscount.CustomerID) AND (TN.ServiceTypeID = tDiscount.ServiceTypeID))) AS TN1
ORDER BY TN1.ServiceID