Get the most value from another column

I want to get data historical and the production. My stored procedure is as follows:

ALTER PROCEDURE [dbo].[pCaRptACInactivas](
                    @CodAsesor      VARCHAR(15),
                    @CodOficina     VARCHAR(4))

    AS
    SET NOCOUNT ON

    DECLARE @CodArbolConta VARCHAR(25)

    IF @CodOficina = '%'
        SET @CodArbolConta = '%'
    ELSE
        SELECT @CodArbolConta = CodArbolConta + '%'     FROM   tClOficinas  WHERE  CodOficina LIKE @CodOficina

    SELECT 
        tabACInactivas.CodOficina, 
        tabACInactivas.NomOficina,
        tabACInactivas.NomAsesor,
        MAX(tabACInactivas.CodPrestamo) CodPrestamo,
        tabACInactivas.CodAsociacion,
        tabACInactivas.NombreAsociacion,
        MAX(tabACInactivas.Ciclo) AS Ciclo,
        COUNT(DISTINCT tabACInactivas.CodUsuario) AS CantSocias,
        MAX(tabACInactivas.FechaEstado) AS FechaCancelacion--,

    FROM (      SELECT          tClOficinas.CodOficina,             tClOficinas.NomOficina,             tCaClAsesores.CodAsesor,                        tCaClAsesores.NomAsesor,            tCaPrestamos.CodPrestamo,           tCaAsociacion.CodAsociacion,            tCaAsociacion.NombreAsociacion,             tCaPrestamos.Ciclo,             tCaPrCliente.CodUsuario,            tCaPrestamos.FechaEstado,           tClParametros.FechaProceso      FROM            tCaPrestamos WITH(NOLOCK)           INNER JOIN tCaProducto WITH(NOLOCK) ON tCaProducto.CodProducto = tCaPrestamos.CodProducto           INNER JOIN tClOficinas WITH(NOLOCK) ON tClOficinas.CodOficina = tCaPrestamos.CodOficina             INNER JOIN tCaAsociacion WITH(NOLOCK) ON tCaAsociacion.CodAsociacion = tCaPrestamos.CodAsociacion           INNER JOIN tCaPrCliente WITH(NOLOCK) ON tCaPrCliente.CodPrestamo = tCaPrestamos.CodPrestamo             INNER JOIN tClParametros WITH(NOLOCK) ON tClParametros.CodOficina = tClOficinas.CodOficina          INNER JOIN tCaClAsesores ON tCaClAsesores.CodAsesor = tCaAsociacion.CodAsesor       WHERE           tCaPrestamos.Estado = 'CANCELADO'           AND DATEDIFF(DAY, tCaPrestamos.FechaEstado, tClParametros.FechaProceso) > 30            AND NOT EXISTS(SELECT 1 
                           FROM tCaPrestamos Pr 
                           INNER JOIN tCaPrCliente PrCl ON PrCl.CodPrestamo = Pr.CodPrestamo
                           WHERE Pr.Estado NOT IN ('TRAMITE', 'APROBADO') 
                           AND Pr.FechaDesembolso >= tCaPrestamos.FechaEstado 
                           AND Pr.CodAsociacion = tCaPrestamos.CodAsociacion
                          )             AND tCaProducto.Tecnologia = 3          AND tCaPrestamos.CodAsesor LIKE @CodAsesor              AND tCaPrestamos.CodOficina IN (SELECT CodOficina FROM tClOficinas WHERE CodArbolConta LIKE @CodArbolConta)

        UNION ALL

        SELECT          tClOficinas.CodOficina,             tClOficinas.NomOficina,             tCaClAsesores.CodAsesor,            tCaClAsesores.NomAsesor,            tCaHPrestamos.CodPrestamo,          tCaAsociacion.CodAsociacion,            tCaAsociacion.NombreAsociacion,             tCaHPrestamos.Ciclo,            tCaHPrCliente.CodUsuario,           tCaHPrestamos.FechaEstado,          tClParametros.FechaProceso          FROM            tCaHPrestamos WITH(NOLOCK)          INNER JOIN tCaProducto WITH(NOLOCK) ON tCaProducto.CodProducto = tCaHPrestamos.CodProducto          INNER JOIN tClOficinas WITH(NOLOCK) ON tClOficinas.CodOficina = tCaHPrestamos.CodOficina            INNER JOIN tCaAsociacion WITH(NOLOCK) ON tCaAsociacion.CodAsociacion = tCaHPrestamos.CodAsociacion          INNER JOIN tCaHPrCliente WITH(NOLOCK) ON tCaHPrCliente.CodPrestamo = tCaHPrestamos.CodPrestamo          INNER JOIN tClParametros WITH(NOLOCK) ON tClParametros.CodOficina = tClOficinas.CodOficina          INNER JOIN tCaClAsesores ON tCaClAsesores.CodAsesor = tCaAsociacion.CodAsesor       WHERE           tCaHPrestamos.Estado = 'CANCELADO'          AND DATEDIFF(DAY, tCaHPrestamos.FechaEstado, tClParametros.FechaProceso) > 30           AND NOT EXISTS(SELECT 1 
                           FROM tCaHPrestamos Pr 
                           INNER JOIN tCaHPrCliente PrCl ON PrCl.CodPrestamo = Pr.CodPrestamo
                           WHERE Pr.E开发者_Go百科stado NOT IN ('TRAMITE', 'APROBADO') 
                           AND Pr.FechaDesembolso >= tCaHPrestamos.FechaEstado 
                           AND Pr.CodAsociacion = tCaHPrestamos.CodAsociacion
                          )             AND tCaProducto.Tecnologia = 3          AND tCaHPrestamos.CodAsesor LIKE @CodAsesor             AND tCaHPrestamos.CodOficina    IN (SELECT CodOficina FROM tClOficinas WHERE CodArbolConta LIKE @CodArbolConta)


    )tabACInactivas 
    GROUP BY            tabACInactivas.CodAsociacion,           tabACInactivas.NombreAsociacion,            tabACInactivas.NomOficina,          tabACInactivas.CodOficina,          tabACInactivas.NomAsesor

I want the CantSocias column takes the most value of the Ciclo column, but not working

That stored procedure that you have posted up is way too large and blocky to even try to interpret and understand. So I will go off of your last sentence:

I want the CantSocias column takes the most value of the Ciclo column, but not working

Basically if you want to set a specific column to that, you can do something like this:

update YourTable
set CantSocias = 
(
  select max(Ciclo)
  from YourOtherTable
)
-- here is where you can put a conditional WHERE clause

You may need to create a sub query to get the most value of Ciclo and join back to your query. An example of what I mean is here:

create table #Product
(
    ID int,
    ProductName varchar(20)
)

insert into #Product(ID, ProductName)
    select 1,'ProductOne'
    union
    select 2,'ProductTwo'

create table #ProductSale
(
    ProductID int,
    Number int,
    SalesRegion varchar(20)
)

insert into #ProductSale(ProductID,Number,SalesRegion)
    select 1,1500,'North'
    union
    select 1, 1200, 'South'
    union
    select 2,2500,'North'
    union
    select 2, 3200, 'South'

--select product sales region with the most sales
select * from #Product p

select ProductId, Max(Number) as Bestsale   from #ProductSale ps group by ProductID

--combining
select 
    p.ID,
    p.ProductName,
    tp.Bestsale,
    ps.SalesRegion
from
    #Product p 
inner join
    (select ProductId, Max(Number) as Bestsale   from #ProductSale ps group by ProductID) as tp on p.ID = tp.ProductID
inner join
    #ProductSale ps on p.ID = ps.ProductID and tp.Bestsale = ps.Number