SELECT
  X.StartDate,
  @nClientID AS ClientID,
  COALESCE((SELECT
    SUM(PF.RetainerFee) AS RetainerFee
  FROM
    taClientBranchProducts AS BP
      LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID),
    taClientProductFees AS PF
  WHERE
    (CP.ClientID = @nClientID)
    @pClientBranchID
    @pRetainerProductID
    AND (PF.ClientBranchProductID = BP.ClientBranchProductID)
    AND (PF.ClientProductFeeID =
       (SELECT MAX(M.ClientProductFeeID) FROM taClientProductFees AS M
        WHERE
          (M.ClientBranchProductID = BP.ClientBranchProductID) AND
          (M.StartDate = (
            SELECT MAX(N.StartDate) FROM taClientProductFees AS N
            WHERE (N.ClientBranchProductID = M.ClientBranchProductID) AND (N.StartDate <= X.StartDate)
          ))
    ))
  ), 0.0) AS RetainerFee,
  COALESCE((SELECT
    SUM(AP.ServiceValue) AS ServiceValue
  FROM
    tbAppointments AS AP
      LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = AP.ClientBranchProductID)
      LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
      LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
      LEFT JOIN taClients AS CS ON (CS.ClientID = CB.ClientID)
  WHERE
    (AP.BillingYearMonth IS NOT NULL) AND (AP.BillingYearMonth = (YEAR(X.StartDate) * 100 + MONTH(X.StartDate)))
    AND (CS.ClientID IS NOT NULL) AND (CS.ClientID = @nClientID)
    @pClientBranchID
    @pAppointmentProductID
    AND (AP.ServiceValue IS NOT NULL) AND (AP.ServiceValue > 0.0)
    AND ((AP.InvoiceAmount IS NULL) OR (AP.InvoiceAmount <= 0.0))
  ), 0.0) AS ServiceValue
FROM (

  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL -11 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL -10 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL  -9 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL  -8 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL  -7 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL  -6 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL  -5 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL  -4 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL  -3 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL  -2 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL  -1 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL   0 MONTH) AS StartDate
  UNION
  SELECT ADDDATE(DATE(CONCAT(YEAR(@aToDate), '-', MONTH(@aToDate), '-', '01')), INTERVAL   1 MONTH) AS StartDate

) AS X
