none
Es posible sumar columnas en SQL SERVER ? RRS feed

  • Pregunta

  • Les pondre algo parecido a la informacion que manejo en la institucion: 

    Supongamos que temenos la  siguiente table con sus datos correspondientes :

    un listado de prestamos que cada mes actualizan su balanceVigente y los interes

    Prestamo Mes BalanceVigente Interes
    78787878 10/31/2018                            8,000.00                                                      325.50
    78787878 11/29/2018                            7,000.00                                                      300.00
    78787878 12/28/2018                            6,000.00                                                      250.00
    78787878 1/31/2019                            5,000.00                                                      200.00
    78787546 10/31/2018                          10,000.00                                                      400.00
    78787546 11/29/2018                          90,000.00                                                      380.00
    78787546 12/28/2018                          80,000.00                                                      350.00
    78787546 1/31/2019                          70,000.00                                                      300.00

    Lo que quiero hacer es lo siguiente : Agrupar esos creditos por su numero de prestamo y poner el balance y los interes de cada mes en una sola linea : Por ejemplo:

    Prestamo BalanceVigente-10/31/2018 Interes-10/31/2018 BalanceVigente-11/29/2018 Interes-11/29/2018 BalanceVigente-12/28/2018 Interes-12/28/2018 BalanceVigente-1/31/2019 Interes-1/31/2019
    78787878                                          8,000.00                           325.00                                          7,000.00                           300.00                                          6,000.00                           250.00                                          5,000.00                           200.00
    78787546                                       10,000.00                           400.00                                       90,000.00                           380.00                                       80,000.00                           350.00                                       70,000.00                           300.00

    Es posible realizar esto ? 

    lunes, 21 de octubre de 2019 14:48

Respuestas

  • Hola AlejandroMst:

    Si claro que es posible. El único handicap es que para montar esas cabeceras, de columnas con las fechas hay que utilizar una query dinámica.

    Declare @query NVARCHAR(4000);
    Declare @mesEnCurso int = Month(getdatE());
    
    SET @query = N'
    ;with cte as (
    	select 
    		  p.prestamo
    		, sum (BALANCEVIGENTE) as balance
    		, month(mes) as mes
    		, sum(intereses) as intereses
    	from PRESTAMOS p
    		group by p.prestamo, month(mes)
    	), cte2 as 
    	(
    	Select   cte.*
    			,lead(cte.balance)over(partition by prestamo order by mes) as sigPrestamo
    			,lead(cte.intereses)over(partition by prestamo order by mes) as sigInteres
    	 from 
    			cte
    	)
    	select cte2.PRESTAMO As Prestamo,
    		cte2.balance as [BalanceVigente-(titulo1)],
    		cte2.intereses as [Interes-(titulo2)],
    		cte2.sigPrestamo as [BalanceVigente-(titulo3)],
    		cte2.sigInteres as [Interes-(titulo4)]
    	 from cte2
    	where cte2.mes = 10
    	order by prestamo desc;';
    
    declare @maxFecha date = (select max(mes) from PRESTAMOS where month(mes) = @mesEnCurso);
    declare @minSig date = ISNULL((select min(mes) from Prestamos where MONTH(MES) = (@mesEnCurso+1)),DATEADD(MONTH,1,GETDATE()));
    
    SET @query = REPLACE(@query,'(titulo1)',(CAST(@maxFecha as varchar(20))));
    SET @query = REPLACE(@query,'(titulo2)',(CAST(@maxFecha as varchar(20))));
    SET @query = REPLACE(@query,'(titulo3)',(CAST(@minSig as varchar(20))));
    SET @query = REPLACE(@query,'(titulo4)',(CAST(@minSig as varchar(20))));
    
    exec sp_executeSql @query;
    

    Algo de este estilo.

    Espero te ayude

    lunes, 21 de octubre de 2019 15:21
  • Deleted
    lunes, 21 de octubre de 2019 15:23

Todas las respuestas

  • Hola AlejandroMst:

    Si claro que es posible. El único handicap es que para montar esas cabeceras, de columnas con las fechas hay que utilizar una query dinámica.

    Declare @query NVARCHAR(4000);
    Declare @mesEnCurso int = Month(getdatE());
    
    SET @query = N'
    ;with cte as (
    	select 
    		  p.prestamo
    		, sum (BALANCEVIGENTE) as balance
    		, month(mes) as mes
    		, sum(intereses) as intereses
    	from PRESTAMOS p
    		group by p.prestamo, month(mes)
    	), cte2 as 
    	(
    	Select   cte.*
    			,lead(cte.balance)over(partition by prestamo order by mes) as sigPrestamo
    			,lead(cte.intereses)over(partition by prestamo order by mes) as sigInteres
    	 from 
    			cte
    	)
    	select cte2.PRESTAMO As Prestamo,
    		cte2.balance as [BalanceVigente-(titulo1)],
    		cte2.intereses as [Interes-(titulo2)],
    		cte2.sigPrestamo as [BalanceVigente-(titulo3)],
    		cte2.sigInteres as [Interes-(titulo4)]
    	 from cte2
    	where cte2.mes = 10
    	order by prestamo desc;';
    
    declare @maxFecha date = (select max(mes) from PRESTAMOS where month(mes) = @mesEnCurso);
    declare @minSig date = ISNULL((select min(mes) from Prestamos where MONTH(MES) = (@mesEnCurso+1)),DATEADD(MONTH,1,GETDATE()));
    
    SET @query = REPLACE(@query,'(titulo1)',(CAST(@maxFecha as varchar(20))));
    SET @query = REPLACE(@query,'(titulo2)',(CAST(@maxFecha as varchar(20))));
    SET @query = REPLACE(@query,'(titulo3)',(CAST(@minSig as varchar(20))));
    SET @query = REPLACE(@query,'(titulo4)',(CAST(@minSig as varchar(20))));
    
    exec sp_executeSql @query;
    

    Algo de este estilo.

    Espero te ayude

    lunes, 21 de octubre de 2019 15:21
  • Deleted
    lunes, 21 de octubre de 2019 15:23