SELECT TN3.*
FROM (

SELECT TN2.*, tCriticalType.Description AS Reason
FROM (



SELECT TN1.RentAmt, TN1.CallAmt, TN1.OtherAmt, (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt) AS TotalAll, tStatus.Status, tCustomer.ShipTo,
 (tCentre.Description + ' ' + tActivity.Description + ' ' + tElement.Description + ' ' + tSubledger.Description) AS ChargeNumber,
 tDepartment.Description AS Department, tServiceID.ServiceID AS ServiceName, tServiceID.Description AS ServiceDesc,
 tServiceID.InstallationDate
FROM (((((((((SELECT tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.StatusID, tTransaction.PersonID,
 tTransaction.LocationID, tTransaction.CustomerCentreID, tTransaction.CustomerActivityID, tTransaction.CustomerElementID,
 tTransaction.CustomerSubledgerID,
 Sum(CASE tTransactionGroup.Description WHEN 'RENT' THEN tTransaction.AmountExGST ELSE 0 END) AS RentAmt,
 Sum(CASE tTransactionGroup.Description WHEN 'CALL' THEN tTransaction.AmountExGST ELSE 0 END) AS CallAmt,
 Sum(CASE tTransactionGroup.Description WHEN 'OTHER' THEN tTransaction.AmountExGST ELSE 0 END) AS OtherAmt,
 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
FROM (tTransaction
LEFT JOIN tTransactionType ON tTransaction.TransactionTypeID = tTransactionType.ID)
LEFT JOIN tTransactionGroup ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
WHERE tTransaction.BatchID = 14501
GROUP BY tTransaction.ServiceID, tTransaction.CustomerID, tTransaction.StatusID, tTransaction.PersonID, tTransaction.LocationID,
 tTransaction.CustomerCentreID, tTransaction.CustomerActivityID, tTransaction.CustomerElementID, tTransaction.CustomerSubledgerID) AS TN1
LEFT JOIN tStatus ON TN1.StatusID = tStatus.ID)
LEFT JOIN tCustomer ON TN1.CustomerID = tCustomer.ID)
LEFT JOIN tPerson ON TN1.PersonID = tPerson.ID)
LEFT JOIN tLocation ON TN1.LocationID = tLocation.ID)
LEFT JOIN (tCentre LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID) ON TN1.CustomerCentreID = tCentre.ID)
LEFT JOIN tActivity ON TN1.CustomerActivityID = tActivity.ID)
LEFT JOIN tElement ON TN1.CustomerElementID = tElement.ID)
LEFT JOIN tSubledger ON TN1.CustomerSubledgerID = tSubledger.ID)
LEFT JOIN tServiceID ON TN1.ServiceID = tServiceID.ID
WHERE tStatus.Status NOT LIKE '%Information%'


) AS TN2

 LEFT JOIN tCriticalType ON TN2.StatusID = tCriticalType.ID

) AS TN3

ORDER BY TN3.ServiceID


Cost100










SELECT tValidAccountNumber.*, tBatchType.Description AS BatchType
FROM tValidAccountNumber
LEFT JOIN tBatchType ON tValidAccountNumber.BatchTypeID = tBatchType.ID
ORDER BY tValidAccountNumber.ID

SELECT tValidAccountNumber.*, tBatchType.Description AS BatchType
FROM tValidAccountNumber
LEFT JOIN tBatchType ON tValidAccountNumber.BatchTypeID = tBatchType.ID
WHERE AccountNo LIKE '2%'
ORDER BY tValidAccountNumber.ID