none
Calculo de variacion entre fechas que se tienen que calcular.. RRS feed

  • Pregunta

  • Hola hace un tiempo hice una misma consulta sobre variaciones pero era para tomar datos en una tabla en la cual yo le ingresaba la fecha...

    Ahora yo lo que necesito es calcular segun la ultima fecha que se genero una facturacion como puedo sacar la variacion porcentual y los valores con respecto al mes pasado...

    el query que comence a hacer yo y me habia gustado es el siguiente tambien me gustaria que me lo arreglen por favor...

    Declare @FechaActual smalldatetime,
    		@FechaAnterior smalldatetime,
    		@Valor1 decimal(18,2),
    		@Valor2 decimal(18,2),
    		@Variacion decimal(10,2)
    
    Set @FechaActual = (Select Max(FechaComprobante) From ClientesCtaCte Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    												 Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    												 and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X'))
    
    Set @FechaAnterior = (Select Max(DateAdd(MM,-1,@FechaActual)) From ClientesCtaCte Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    															  Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    															  and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X'))
    
    Set @Valor1 = (Select SUM(ImporteTotal) From ClientesCtaCte Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    										Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    										and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X')
    										and FechaComprobante = @FechaActual)										)
    
    Set @Valor2 = (Select SUM(ImporteTotal) From ClientesCtaCte Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    										Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    										and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X') 
    										and FechaComprobante = @FechaAnterior)										
    
    Set @Variacion = (@Valor1 - @Valor2) / (@Valor1 + @Valor2)
    
    Select @FechaActual,@FechaAnterior,@Valor1,@Valor2,@Variacion
    From ClientesCtaCte Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID
    Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X')
    and ClientesCtaCte.ClienteID = 19386 and FechaComprobante = '20210201'

    Explico un poco para que me puedan llegar a entender primero guardo en una variable la fecha actual de facturación del mes corriente (el día siempre es 1ero de mes).

    Luego en la siguiente variable fecha anterior guarde cual es la misma fecha pero del mes anterior (por ende tiene que ser el mismo dia pero un mes atras)

    Luego a ello busco cual es la facturacion del cliente del mes actual y lo guardo en un valor (hay veces que un cliente se le genera mas de una factura por lo que por cada cliente se tendria que "englobar el valor para que quede prolijo".

    Por ultimo lo mismo que el primer caso pero se tiene que tener como referencia que se guarde lo hecho el mes anterior...

    Finalmente lo que hace el query es calcularlo con un cliente en especifico ya que en nuestra tabla de clientes algunos tienen condicion de cuenta y otros no (por eso el CuentaCorriente = 1) y le pase como parametro que tenga que hacer ese calculo este mes...

    Al querer correr el query no me genero errores pero por ejemplo me sumo todos los valores de todos los clientes con cuenta corriente tanto del mes anterior como del mes actual en cada una de las variables y a eso le hizo el calculo de la variacion...

    Yo quisiera saber como logro hacer que me muestre solo ese cliente (debo de pasarlo tmb como parametro?) o tengo que ponerlo antes?...

    Es la primer vez que trabajo asi con variables por lo que me gustaria que me digan bien cual es el correcto funcionamiento...

    Muchas Gracias.

    Dejo asi a modo de ejemplo como tendria que quedar para hacer una prueba correcta...

    Mes Actual                  Mes Anterior                 Valor1                 Valor2          Variacion
    01/02/2021                01/01/2021                   1000                   1200            -0.09 (Tengo entendido que es v1-v2/v1+v2)

    Tablas de ejemplo

    Facturas

    Fecha smalldatetime

    tipooperacion nvarchar(3)

    NroComprobante nvarchar(13) Formato de los comprobantes es 'A000900123456' o 'X000100045689'

    ImporteTotal decimal(18,2)

    ClienteId bigint

    Clientes

    ClienteId bigint

    CuentaCorriente bit 

    Estado = nvarchar(1)



    • Editado SomniaL viernes, 5 de febrero de 2021 19:01
    viernes, 5 de febrero de 2021 17:59

Respuestas

  • Hola SomniaL:

    Lo primero, el enfoque que tienes es incorrecto. Intentar procesar datos como elementos individuales, pensando en que tengo que hacer con cada valor individual no es propio de un lenguaje de conjuntos como TSQL.

    Vamos a darle una pequeña vuelta al planteamiento inicial, y haber si conseguimos trasladarlo a tú solicitud.

    Como punto de partida, en vez de utilizar variables, vamos a cambiar por tablas de expresión común correlativas. Donde tengamos esas fechas y sus clientes, o esas fechas y sus cantidades, porque no hemos obtenido la máx fecha del cliente. sino la de todos.

    ;With c as (
    	Select Max(FechaComprobante) as FechaActual, Clientes.ClienteID 
    		From ClientesCtaCte 
    				Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    		Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    			 and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X')
    		Group by Clientes.clienteId	
    	), c2 as (
    	Select Max(DateAdd(MM,-1,c.FechaActual)) as FechaAnterior, Clientes.clienteId	
    		From ClientesCtaCte 
    				Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    				inner Join c on Clientes.ClienteID = c.clienteId
    		Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    			and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X')
    		Group by Clientes.clienteId	
    	), c3 as (
    	Select SUM(ImporteTotal) as valor1, c.ClienteID, c.FechaActual
    		From ClientesCtaCte 
    				Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    				Inner join c on Clientes.ClienteId = c.ClienteID
    	    Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    			and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X')
    			and FechaComprobante = c.FechaActual
    		Group by c.ClienteID, c.FechaActual 
    	), c4 as (
    	Select SUM(ImporteTotal) as valor2,c2.ClienteID, c2.FechaAnterior 
    		From ClientesCtaCte 
    				Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    				Inner join c2 on Clientes.ClienteId = c2.ClienteId
    		Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    			and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X') 
    			and FechaComprobante = c2.FechaAnterior
    		Group by c2.ClienteID, c2.FechaAnterior 	 
    	)										
    	Select c3.FechaActual, c4.FechaAnterior, c3.valor1, c4.valor2, (c3.valor1 - c4.valor2) / (c3.valor1 + c4.valor2)
    		from c4 
    			inner join c3 on c4.ClienteID = c3.ClienteID
    

    En el conjunto c extraemos la fecha de comprobante de cada cliente.

    En el conjunto c2 hacemos lo mismo que tenías pero para cada cliente, relacionado además con el conjunto c

    En el conjunto c3 hacemos la cantidad para cada cliente relacionado con c

    En el conjunto c4 hacemos la cantidad para cada cliente relacionado con c2

    Luego mezclamos la salida de c4 y c3

    Aunque esto es sólo una aproximación, pues se pueden reducir las lecturas a una sola query. Pero es un inicio.

    Tablas de expresión común correlativas.

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

    Group by

    https://javifer2.wordpress.com/2019/10/04/group-by-quizas-la-clausula-mas-conflictiva/

    • Marcado como respuesta SomniaL lunes, 8 de febrero de 2021 15:43
    sábado, 6 de febrero de 2021 9:16

Todas las respuestas

  • Hola SomniaL:

    Lo primero, el enfoque que tienes es incorrecto. Intentar procesar datos como elementos individuales, pensando en que tengo que hacer con cada valor individual no es propio de un lenguaje de conjuntos como TSQL.

    Vamos a darle una pequeña vuelta al planteamiento inicial, y haber si conseguimos trasladarlo a tú solicitud.

    Como punto de partida, en vez de utilizar variables, vamos a cambiar por tablas de expresión común correlativas. Donde tengamos esas fechas y sus clientes, o esas fechas y sus cantidades, porque no hemos obtenido la máx fecha del cliente. sino la de todos.

    ;With c as (
    	Select Max(FechaComprobante) as FechaActual, Clientes.ClienteID 
    		From ClientesCtaCte 
    				Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    		Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    			 and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X')
    		Group by Clientes.clienteId	
    	), c2 as (
    	Select Max(DateAdd(MM,-1,c.FechaActual)) as FechaAnterior, Clientes.clienteId	
    		From ClientesCtaCte 
    				Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    				inner Join c on Clientes.ClienteID = c.clienteId
    		Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    			and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X')
    		Group by Clientes.clienteId	
    	), c3 as (
    	Select SUM(ImporteTotal) as valor1, c.ClienteID, c.FechaActual
    		From ClientesCtaCte 
    				Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    				Inner join c on Clientes.ClienteId = c.ClienteID
    	    Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    			and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X')
    			and FechaComprobante = c.FechaActual
    		Group by c.ClienteID, c.FechaActual 
    	), c4 as (
    	Select SUM(ImporteTotal) as valor2,c2.ClienteID, c2.FechaAnterior 
    		From ClientesCtaCte 
    				Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    				Inner join c2 on Clientes.ClienteId = c2.ClienteId
    		Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    			and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X') 
    			and FechaComprobante = c2.FechaAnterior
    		Group by c2.ClienteID, c2.FechaAnterior 	 
    	)										
    	Select c3.FechaActual, c4.FechaAnterior, c3.valor1, c4.valor2, (c3.valor1 - c4.valor2) / (c3.valor1 + c4.valor2)
    		from c4 
    			inner join c3 on c4.ClienteID = c3.ClienteID
    

    En el conjunto c extraemos la fecha de comprobante de cada cliente.

    En el conjunto c2 hacemos lo mismo que tenías pero para cada cliente, relacionado además con el conjunto c

    En el conjunto c3 hacemos la cantidad para cada cliente relacionado con c

    En el conjunto c4 hacemos la cantidad para cada cliente relacionado con c2

    Luego mezclamos la salida de c4 y c3

    Aunque esto es sólo una aproximación, pues se pueden reducir las lecturas a una sola query. Pero es un inicio.

    Tablas de expresión común correlativas.

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

    Group by

    https://javifer2.wordpress.com/2019/10/04/group-by-quizas-la-clausula-mas-conflictiva/

    • Marcado como respuesta SomniaL lunes, 8 de febrero de 2021 15:43
    sábado, 6 de febrero de 2021 9:16
  • Hola buenos días Javier Muchísimas gracias por tu respuesta ahí pude entender como generaste la consulta y me dio perfecto ahora te consulto...

    Como podía hacer para que sea una variación anual?? osea por ejemplo saber como fueron las variaciones de todos los clientes a lo largo del anio anterior??

    Otra cosa que vi pero eso es debido a mis clientes como hago el Having para que por ejemplo pueda visualizar aquellos monto que superaron de facturación ponle mas de 300.000$??? Eso se realiza por fuera del with a la hora del select final???

    lunes, 8 de febrero de 2021 15:47
  • Hola SomniaL:

    A lo mejor la respuesta te sale desordenada, porque últimamente, al menos yo, me cuesta encontrar vuestras respuestas. No me salen y tengo que ir a través de vuestro enlace, para verlas. Luego de unos días esto se suele corregir, pero....

    Having, lo puedes usar después de cada group by o también como dices al final del cte, pero ahi ya utilizarías where

    En cuanto a todos los clientes. Es posible que la restricción de clientes venga dada por los filtros where de cada uno de los conjuntos.

    Select Max(FechaComprobante) as FechaActual, Clientes.ClienteID 
    		From ClientesCtaCte 
    				Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    		Where CuentaCorriente = 1 and Estado = 'A' and TipoOperacion IN ('CPO','FAC')
    			 and (Substring(NroComprobante,2,4) = '0009' or Substring(NroComprobante,1,1) = 'X')
    		Group by Clientes.clienteId	

    Esa query debería de ser para todos los clientes, la max fecha comprobante .... que cumplan esos criterios.

    Ej.

    MaxFecha, ClienteId

    20210101, 1

    20210201, 2

    20210101, 3

    lunes, 8 de febrero de 2021 16:17