/* Extract all tService entries for Annual Charges */
SELECT tServiceID.ServiceID, tServiceID.Description, tServiceID.ServiceTypeID, tServiceType.Code,
 'TransactionTypeID' = CASE
  WHEN tServiceType.Frequency = 1 THEN '0'
  WHEN tServiceType.Frequency = 3 THEN '0'
  WHEN tServiceType.Frequency = 6 THEN '0'
  WHEN tServiceType.Frequency = 12 THEN '0'
  ELSE '0'
 END, 'GSTFlag' = CASE
  WHEN tGroup.CustomerID = 499 THEN 'N'
  ELSE 'Y'
 END
FROM (((tServiceID
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
WHERE tServiceType.SystemID = 0



/* Update SQL for Annual Charges */
UPDATE tDataCollection
SET AmountExGST = ChargeRate.AmtExGST,
 GSTAmount = ChargeRate.GSTAmt,
 AmountIncGST = ChargeRate.AmtExGST + ChargeRate.GSTAmt
FROM (SELECT GSTrate.ID, GSTrate.AmtExGST,
(GSTrate.AmtExGST * GSTrate.GSTFactor) AS GSTAmt
FROM (SELECT tDataCollection.ID, (CASE WHEN tDataCollection.GSTFlag LIKE 'Y' THEN 0.1000 ELSE 0 END) AS GSTFactor,
'AmtExGST' = CASE
  WHEN tServiceType.Frequency = 1 THEN tRate.Cost / 12
  WHEN tServiceType.Frequency = 3 THEN tRate.Cost / 3
  WHEN tServiceType.Frequency = 6 THEN tRate.Cost / 2
  WHEN tServiceType.Frequency = 12 THEN tRate.Cost
  ELSE 0
 END
FROM ((tDataCollection
LEFT JOIN tServiceID ON tDataCollection.ServiceID = tServiceID.ServiceID)
LEFT JOIN tServiceType ON tServiceID.ServiceTypeID = tServiceType.ID)
LEFT JOIN tRate ON tServiceID.ServiceTypeID = tRate.ServiceTypeID
WHERE tDataCollection.BatchID = 50003
AND tRate.Cost IS NOT NULL) AS GSTrate) AS ChargeRate
WHERE tDataCollection.ID = ChargeRate.ID