none
como sumo precio semana 1 año 2018 y semana 1 año 2019 sacar el total de Varianza y la Varianza % RRS feed

  • Pregunta

  • Hola esta es mi tabla me pueden ayudar a calcular la suma total entre dos semana pero con año diferentes o con el mismo año y que saque la varianza formula (precioActual-PrecioAnterior) y la varianza porcentual% formula PrecioActual*100/PrecioAnterior-100.

    Ejemplo con el mismo año

    introducir la descripción de la imagen aquí


    • Editado alexabc2019 viernes, 13 de diciembre de 2019 19:54
    viernes, 13 de diciembre de 2019 19:52

Respuestas

  • En la tabla PreciosProducto, ¿se registran los precios una vez por semana?

    ¿Cómo contar las semanas: por día de la semana (de lunes a domingo, por ejemplo) o por día (de 1 a 7, de 8 a 14, etc.)?

    ¿Cuál es la versión de SQL Server?

    ¿Puede publicar una muestra de los datos para facilitar la comprensión del problema?


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    viernes, 13 de diciembre de 2019 21:24
  • Hola alexabc2019:

    create schema muestreo
    go
    create table muestreo.preciosProducto 
    (id int primary key,
    fecha datetime,
    tipocentro int,
    centrocaptacion nvarchar(70),
    producto nvarchar(70),
    canal nvarchar(50),
    precio decimal (10,4))
    go
    
    insert into muestreo.preciosProducto(id, fecha, tipocentro, centrocaptacion, producto, canal, precio)
    values
    (1 ,'20180101',1,'ORIENTAL','aceite','detalle',13.23),
    (2 ,'20180102',1,'ORIENTAL','aceite','detalle',14.33),
    (3 ,'20180103',1,'ORIENTAL','aceite','detalle',16.41),
    (4 ,'20180104',1,'ORIENTAL','aceite','detalle',13.72),
    (5 ,'20180105',1,'ORIENTAL','aceite','detalle',17.11),
    (6 ,'20180106',1,'ORIENTAL','aceite','detalle',15.04),
    (7 ,'20180107',1,'ORIENTAL','aceite','detalle',13.06),
    (8 ,'20180108',1,'ORIENTAL','aceite','detalle',13.99),
    (9 ,'20180109',1,'ORIENTAL','aceite','detalle',13.23),
    (10,'20180110',1,'ORIENTAL','aceite','detalle',14.31),
    (11,'20180111',1,'ORIENTAL','aceite','detalle',16.18),
    (12,'20180112',1,'ORIENTAL','aceite','detalle',13.55),
    (13,'20180113',1,'ORIENTAL','aceite','detalle',17.69),
    (14,'20180114',1,'ORIENTAL','aceite','detalle',15.14),
    (15,'20180115',1,'ORIENTAL','aceite','detalle',13.12),
    (16,'20180116',1,'ORIENTAL','aceite','detalle',13.78),
    (17,'20190101',1,'ORIENTAL','aceite','detalle',23.23),
    (18,'20190102',1,'ORIENTAL','aceite','detalle',24.33),
    (19,'20190103',1,'ORIENTAL','aceite','detalle',26.41),
    (20,'20190104',1,'ORIENTAL','aceite','detalle',23.72),
    (21,'20190105',1,'ORIENTAL','aceite','detalle',27.11),
    (22,'20190106',1,'ORIENTAL','aceite','detalle',25.04),
    (23,'20190107',1,'ORIENTAL','aceite','detalle',23.06),
    (24,'20190108',1,'ORIENTAL','aceite','detalle',23.32),
    (25,'20190109',1,'ORIENTAL','aceite','detalle',23.44),
    (26,'20190110',1,'ORIENTAL','aceite','detalle',24.41),
    (27,'20190111',1,'ORIENTAL','aceite','detalle',26.67),
    (28,'20190112',1,'ORIENTAL','aceite','detalle',23.33),
    (29,'20190113',1,'ORIENTAL','aceite','detalle',27.12),
    (30,'20190114',1,'ORIENTAL','aceite','detalle',25.15),
    (31,'20190115',1,'ORIENTAL','aceite','detalle',23.09),
    (32,'20190116',1,'ORIENTAL','aceite','detalle',23.01);
    go

    Tu escenario, con datos para la semana 1 y 2 del año 2018 y 2019.

    DECLARE @numSemana INT= 2; -- numero de semana a obtener datos.
    
    DECLARE @fecha DATETIME= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1); -- primer dia del año anterior
    
    DECLARE @fechafin DATETIME= DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1); -- primer dia del año siguiente
    
    WITH cte
         AS (
    	 -- recogemos la media de precios para las senanas que sean igual a la filtrada, para este año y el anterior
    	 SELECT producto
                  , centrocaptacion
                  , canal
                  , DATEPART(week, fecha) semana
                  , YEAR(fecha) anual
                  , AVG(precio) AS precio
             FROM muestreo.PreciosProducto
                    WHERE(DATEPART(week, fecha) = @numSemana)
                         AND fecha >= @fecha
                         AND fecha < @fechafin
                    GROUP BY DATEPART(week, fecha)
                           , YEAR(fecha)
                           , producto
                           , centrocaptacion
                           , canal),
         calculados
         AS (
    	 -- calculamos lo que necesitamos extraido del conjunto cte y mezclado con el mismo pero para el año anterior
    	 SELECT c.producto
                  , c.centrocaptacion
                  , c.canal
                  , c.precio AS preciopost
                  , c.anual AS anualpost
                  , c2.precio AS precioant
                  , c2.anual AS anualant
                  , (c.precio - c2.precio) AS varianza
                  , (c.precio * 100 / c2.precio - 100) AS [varianza%]
             FROM cte c
                  LEFT JOIN cte c2 ON(c.anual = c2.anual + 1)
                                     AND (c.semana = c2.semana)
                    WHERE c2.precio IS NOT NULL)
         SELECT calculados.producto
              , calculados.centrocaptacion
              , calculados.canal
              , calculados.preciopost
              , calculados.anualpost
              , calculados.precioant
              , calculados.anualant
              , calculados.varianza
              , calculados.[varianza%]
         FROM calculados;

    Salida


    Aunque te he devuelto, alguna columna de más, supongo, ya que no has puesto lo deseado para lo que preguntas en el texto del mensaje.

    Modificar el código para que sea sobre la semana anterior, o incluso sobre ambos, semana anterior y año anterior, podría ser tal que, en el conjunto de obtención de datos, donde el filtro de datepart(week,fecha= @numsemana, incluir or datepart(week,fecha=@numsemana -1)

    En el código que te he expuesto, y conforme a lo que te ha preguntado José en la entrada anterior, las semanas son calculadas por el sistema en función de:

    Select @@datefirst te devuelve cual es el primer día, que tienes por defecto.

    1=lunes, 7=domingo.

    Set DateFirst

    https://docs.microsoft.com/es-es/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-ver15

    Tablas de expresión común

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

    Nota: Las fórmulas que has propuesto como varianza y varianza porcentual, las he copiado literalmente como las has puesto.
    sábado, 14 de diciembre de 2019 9:33

Todas las respuestas

  • En la tabla PreciosProducto, ¿se registran los precios una vez por semana?

    ¿Cómo contar las semanas: por día de la semana (de lunes a domingo, por ejemplo) o por día (de 1 a 7, de 8 a 14, etc.)?

    ¿Cuál es la versión de SQL Server?

    ¿Puede publicar una muestra de los datos para facilitar la comprensión del problema?


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    viernes, 13 de diciembre de 2019 21:24
  • Hola alexabc2019:

    create schema muestreo
    go
    create table muestreo.preciosProducto 
    (id int primary key,
    fecha datetime,
    tipocentro int,
    centrocaptacion nvarchar(70),
    producto nvarchar(70),
    canal nvarchar(50),
    precio decimal (10,4))
    go
    
    insert into muestreo.preciosProducto(id, fecha, tipocentro, centrocaptacion, producto, canal, precio)
    values
    (1 ,'20180101',1,'ORIENTAL','aceite','detalle',13.23),
    (2 ,'20180102',1,'ORIENTAL','aceite','detalle',14.33),
    (3 ,'20180103',1,'ORIENTAL','aceite','detalle',16.41),
    (4 ,'20180104',1,'ORIENTAL','aceite','detalle',13.72),
    (5 ,'20180105',1,'ORIENTAL','aceite','detalle',17.11),
    (6 ,'20180106',1,'ORIENTAL','aceite','detalle',15.04),
    (7 ,'20180107',1,'ORIENTAL','aceite','detalle',13.06),
    (8 ,'20180108',1,'ORIENTAL','aceite','detalle',13.99),
    (9 ,'20180109',1,'ORIENTAL','aceite','detalle',13.23),
    (10,'20180110',1,'ORIENTAL','aceite','detalle',14.31),
    (11,'20180111',1,'ORIENTAL','aceite','detalle',16.18),
    (12,'20180112',1,'ORIENTAL','aceite','detalle',13.55),
    (13,'20180113',1,'ORIENTAL','aceite','detalle',17.69),
    (14,'20180114',1,'ORIENTAL','aceite','detalle',15.14),
    (15,'20180115',1,'ORIENTAL','aceite','detalle',13.12),
    (16,'20180116',1,'ORIENTAL','aceite','detalle',13.78),
    (17,'20190101',1,'ORIENTAL','aceite','detalle',23.23),
    (18,'20190102',1,'ORIENTAL','aceite','detalle',24.33),
    (19,'20190103',1,'ORIENTAL','aceite','detalle',26.41),
    (20,'20190104',1,'ORIENTAL','aceite','detalle',23.72),
    (21,'20190105',1,'ORIENTAL','aceite','detalle',27.11),
    (22,'20190106',1,'ORIENTAL','aceite','detalle',25.04),
    (23,'20190107',1,'ORIENTAL','aceite','detalle',23.06),
    (24,'20190108',1,'ORIENTAL','aceite','detalle',23.32),
    (25,'20190109',1,'ORIENTAL','aceite','detalle',23.44),
    (26,'20190110',1,'ORIENTAL','aceite','detalle',24.41),
    (27,'20190111',1,'ORIENTAL','aceite','detalle',26.67),
    (28,'20190112',1,'ORIENTAL','aceite','detalle',23.33),
    (29,'20190113',1,'ORIENTAL','aceite','detalle',27.12),
    (30,'20190114',1,'ORIENTAL','aceite','detalle',25.15),
    (31,'20190115',1,'ORIENTAL','aceite','detalle',23.09),
    (32,'20190116',1,'ORIENTAL','aceite','detalle',23.01);
    go

    Tu escenario, con datos para la semana 1 y 2 del año 2018 y 2019.

    DECLARE @numSemana INT= 2; -- numero de semana a obtener datos.
    
    DECLARE @fecha DATETIME= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1); -- primer dia del año anterior
    
    DECLARE @fechafin DATETIME= DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1); -- primer dia del año siguiente
    
    WITH cte
         AS (
    	 -- recogemos la media de precios para las senanas que sean igual a la filtrada, para este año y el anterior
    	 SELECT producto
                  , centrocaptacion
                  , canal
                  , DATEPART(week, fecha) semana
                  , YEAR(fecha) anual
                  , AVG(precio) AS precio
             FROM muestreo.PreciosProducto
                    WHERE(DATEPART(week, fecha) = @numSemana)
                         AND fecha >= @fecha
                         AND fecha < @fechafin
                    GROUP BY DATEPART(week, fecha)
                           , YEAR(fecha)
                           , producto
                           , centrocaptacion
                           , canal),
         calculados
         AS (
    	 -- calculamos lo que necesitamos extraido del conjunto cte y mezclado con el mismo pero para el año anterior
    	 SELECT c.producto
                  , c.centrocaptacion
                  , c.canal
                  , c.precio AS preciopost
                  , c.anual AS anualpost
                  , c2.precio AS precioant
                  , c2.anual AS anualant
                  , (c.precio - c2.precio) AS varianza
                  , (c.precio * 100 / c2.precio - 100) AS [varianza%]
             FROM cte c
                  LEFT JOIN cte c2 ON(c.anual = c2.anual + 1)
                                     AND (c.semana = c2.semana)
                    WHERE c2.precio IS NOT NULL)
         SELECT calculados.producto
              , calculados.centrocaptacion
              , calculados.canal
              , calculados.preciopost
              , calculados.anualpost
              , calculados.precioant
              , calculados.anualant
              , calculados.varianza
              , calculados.[varianza%]
         FROM calculados;

    Salida


    Aunque te he devuelto, alguna columna de más, supongo, ya que no has puesto lo deseado para lo que preguntas en el texto del mensaje.

    Modificar el código para que sea sobre la semana anterior, o incluso sobre ambos, semana anterior y año anterior, podría ser tal que, en el conjunto de obtención de datos, donde el filtro de datepart(week,fecha= @numsemana, incluir or datepart(week,fecha=@numsemana -1)

    En el código que te he expuesto, y conforme a lo que te ha preguntado José en la entrada anterior, las semanas son calculadas por el sistema en función de:

    Select @@datefirst te devuelve cual es el primer día, que tienes por defecto.

    1=lunes, 7=domingo.

    Set DateFirst

    https://docs.microsoft.com/es-es/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-ver15

    Tablas de expresión común

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

    Nota: Las fórmulas que has propuesto como varianza y varianza porcentual, las he copiado literalmente como las has puesto.
    sábado, 14 de diciembre de 2019 9:33