INSERT INTO taClientBranchProductHistory (
  ClientBranchProductID,
  BillingYearMonth,
  AppointmentCount,
  TimeTravelledTotal,
  DistanceTravelledTotal,
  CoverUsed,
  FreeTimeUsed,
  InvoiceAmount,
  RetainerFee
)

SELECT
  X.ClientBranchProductID,
  X.BillingYearMonth,
  X.AppointmentCount,
  ROUND(X.TimeTravelledTotal, 2) AS TimeTravelledTotal,
  ROUND(X.DistanceTravelledTotal, 2) AS DistanceTravelledTotal,
  ROUND(X.CoverUsed, 2) AS CoverUsed,
  ROUND(X.FreeTimeUsed, 2) AS FreeTimeUsed,
  ROUND(X.InvoiceAmount, 2) AS InvoiceAmount,
  ROUND(fnClientBranchProductRetainer(X.ClientBranchProductID, CONCAT(LEFT(@nBillingYearMonth, 4), '-', RIGHT(@nBillingYearMonth, 2), '-01')) * 1.0, 2) AS RetainerFee
FROM (

  SELECT
    AP.ClientBranchProductID,
    AP.BillingYearMonth,
    COUNT(*) AS AppointmentCount,
    SUM(ROUND(AP.TimeTravelled * AP.TimeTravelledRate, 2)) AS TimeTravelledTotal,
    SUM(ROUND(AP.DistanceTravelled * AP.DistanceTravelledRate, 2)) AS DistanceTravelledTotal,
    SUM(ROUND(AP.CoverUsed, 2)) AS CoverUsed,
    SUM(ROUND(AP.FreeTimeAmount, 2)) AS FreeTimeUsed,
    SUM(ROUND(AP.InvoiceAmount, 2)) AS InvoiceAmount
  FROM
    tbAppointments AS AP
  WHERE
    (AP.ClientBranchProductID IS NOT NULL) AND
    (AP.ClientBranchProductID > 0) AND
    (AP.AppointmentStage = 'Authorised') AND
    (AP.ConsultantBranchID NOT IN (18,116)) AND
    (AP.ServiceValue IS NOT NULL) AND (AP.ServiceValue > 0.0) AND
    (AP.BillingYearMonth IS NOT NULL) AND
    (AP.BillingYearMonth = @nBillingYearMonth)
  GROUP BY 1,2

) AS X
ORDER BY 1,2

