SELECT
  CB.ClientID,
  CB.ClientBranchID,
  CB.BranchName,
  CB.TradeName,
  CB.BranchStatus,
  CS.ClientStatus,
  CS.TerminationDate AS ClientTerminationDate,
  CB.LabourNetBranchID,
  CB.MapReference,
  CB.IsHeadOffice,
  CB.NoOfEmployees,
  CB.RoundTripDistance,
  CB.FreeKiloDistance,
  CB.Address1,
  CB.Address2,
  CB.Address3,
  CB.Address4,
  CB.Address5,
  CB.TelephoneAreaCode,
  LEFT(CB.TelephoneNo, 3) AS TelephoneTelA,
  MID(CB.TelephoneNo, 4, 10) AS TelephoneTelB,
  CB.FaxAreaCode,
  LEFT(CB.FaxNo, 3) AS FaxTelA,
  MID(CB.FaxNo, 4, 10) AS FaxTelB,
  CB.OtherAreaCode,
  LEFT(CB.OtherTelNo, 3) AS OtherTelA,
  MID(CB.OtherTelNo, 4, 10) AS OtherTelB,
  (SELECT COUNT(*) FROM taClientBranches AS CB1
   WHERE (CB1.ClientID = CB.ClientID) AND (CB1.ClientBranchID <> CB.ClientBranchID) AND (CB1.BranchStatus = 1)
  ) AS MergePossible,
  CB.TerminationDate,
  CB.TerminationReason,
  CB.TerminationNotes
FROM
  taClientBranches AS CB
    LEFT JOIN taClients AS CS ON (CS.ClientID = CB.ClientID)
WHERE
  (CB.ClientBranchID = @nClientBranchID)

