none
Consulta para incluir todas las semanas del año por cada producto dentro del rango. RRS feed

  • Pregunta

  • Buenas tardes,

    Tengo una tabla que contiene movimientos de productos. La tabla tiene un campo para id de producto (C3), el campo de fecha (C9) y el campo de cantidad (C11).

    Quiero hacer una consulta que me diga cuantos movimientos tuvo el producto en cada semana usando datepart. El resultado esperado de la consulta es un registro por cada semana por cada producto.

    Mi problema es que solamente salen las semanas donde hay producto.

    Este es el ejemplo de consulta:

    declare @fec_fin datetime

    declare @fec_ini datetime

    declare @linea varchar(5)

    set @fec_ini= '14/05/2017';

    set @fec_fin= '24/06/2017';

    set @linea='33';


    select datepart(year,j.c10) as Anio,datepart(week,j.c10) as Semana,j.c3 as Clave,

    sum(case when j.c5='A' then j.c11 when j.c5='D' then j.c11*(-1) end) as Unidades

    from kdij j  

    left join kdii i on (j.c3=i.c1)

    where j.c10>=@fec_ini and j.C10<=@fec_fin and j.c1>'01' and j.c1<'06'

    and j.c2=

    and i.c3=@linea

    group by datepart(year,j.c10),datepart(week,j.c10),j.c3

    order by j.c3,datepart(year,j.c10),datepart(week,j.c10)



    Como se puede ver, los primeros 3 productos solo salen en las semanas donde si hubo movimientos, pero no puedo incluir el 0 donde no hubo movimientos. 

    ¿Que puedo hacer?

    Agradezco sus sugerencias


    Michel Cochegrus



    • Editado mcochegrus viernes, 23 de junio de 2017 21:04
    viernes, 23 de junio de 2017 21:03

Respuestas

  • Vaya identificadores para descriptivos.

    Imagino que se trata de una base de datos heredada porque hoy en día hacer esto es una locura sin sentido alguno.

    Intenta ejecutar la siguiente consulta:

    DECLARE @fec_ini date = '20170514';
    DECLARE @fec_fin date = '20170624';
    DECLARE @linea varchar(5) = '33';
    
    WITH T AS
    (
        SELECT DATEPART(WEEK, @fec_ini) AS Semana
        UNION ALL
        SELECT Semana + 1 FROM T WHERE Semana < DATEPART(WEEK, @fec_fin)
    )
    SELECT 
        DATEPART(YEAR, j.c10) AS Anio,
        t1.Semana AS Semana,
        j.c3 AS Clave,
        COALESCE(SUM(CASE WHEN j.c5 = 'A' THEN j.c11 
    	   WHEN j.c5 = 'D' THEN j.c11 * (-1) 
        END), 0) AS Unidades
    FROM 
        T t1
        LEFT JOIN kdij j ON t1.Semana = DATEPART(WEEK, j.c10)
        LEFT JOIN kdii i ON j.c3 = i.c1
    WHERE 
        j.c10 >= @fec_ini AND j.c10 <= @fec_fin
        AND CONVERT(int, j.c1) BETWEEN 2 AND 5
        AND j.c2 = 1
        AND i.c3 = @linea
    GROUP BY 
        DATEPART(YEAR, j.c10), t1.Semana, j.c3
    ORDER BY 
        j.c3, DATEPART(YEAR, j.c10), t1.Semana;
    GO


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    • Propuesto como respuesta Joyce_ACModerator lunes, 26 de junio de 2017 15:13
    • Marcado como respuesta mcochegrus lunes, 26 de junio de 2017 21:35
    viernes, 23 de junio de 2017 22:16
  • Mi recomendación es que inicies obteniendo de forma independiente el listado de las semanas, en estos casos por lo general yo uso una función que desarrollo Itzik Ben-Gan, a partir de ese resultado tu podrás ponerlo en el FROM y hacer LEFT JOIN con el resto de tablas para que en caso de que el producto no tenga valores en dicha semana simplemente te lo muestre vació o 0.

    Funcion de Itzik:

    CREATE FUNCTION [dbo].[GetNums](@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
    AS
    RETURN
      WITH
        L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
        L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
        L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
        L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
        L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
        L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
        Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
                 FROM L5)
      SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
      FROM Nums
      ORDER BY rownum;
    

    Esta funcion permite que le suministres el valor mas bajo y el mas alto y te retorna un DataSet, en este caso usamos dicha funcion en conjunto con el rango de fechas, a partir de ahi obtenemos el numero de semanas de diferencia entre fecha inicial y fecha final, el resultado es el que puedes poner en un CTE y realizar un ajuste en tu consulta para que se hagan los left join contra esta tabla.

    DECLARE @InitialDate DATETIME2 = '20170401' ,
        @EndDate DATETIME2 = '20170901';
    
    SELECT  *
    FROM    dbo.GetNums(1, DATEDIFF(WEEK, @InitialDate, @EndDate)) AS Number
            CROSS APPLY ( VALUES
            ( DATEPART(WEEK, ( DATEADD(WEEK, Number.n - 1, @InitialDate) ))) ) AS SecondPart ( Increase ); 


    "Oh, the wind, the wind is blowing,through the graves the wind is blowing,Freedom soon will come; then well come from the shadows".The Partisan(Leonard Cohen) Email: me[at]geohernandez.net Blog:www.geohernandez.net

    • Propuesto como respuesta Joyce_ACModerator lunes, 26 de junio de 2017 15:13
    • Marcado como respuesta mcochegrus lunes, 26 de junio de 2017 21:35
    viernes, 23 de junio de 2017 22:22

Todas las respuestas

  • Vaya identificadores para descriptivos.

    Imagino que se trata de una base de datos heredada porque hoy en día hacer esto es una locura sin sentido alguno.

    Intenta ejecutar la siguiente consulta:

    DECLARE @fec_ini date = '20170514';
    DECLARE @fec_fin date = '20170624';
    DECLARE @linea varchar(5) = '33';
    
    WITH T AS
    (
        SELECT DATEPART(WEEK, @fec_ini) AS Semana
        UNION ALL
        SELECT Semana + 1 FROM T WHERE Semana < DATEPART(WEEK, @fec_fin)
    )
    SELECT 
        DATEPART(YEAR, j.c10) AS Anio,
        t1.Semana AS Semana,
        j.c3 AS Clave,
        COALESCE(SUM(CASE WHEN j.c5 = 'A' THEN j.c11 
    	   WHEN j.c5 = 'D' THEN j.c11 * (-1) 
        END), 0) AS Unidades
    FROM 
        T t1
        LEFT JOIN kdij j ON t1.Semana = DATEPART(WEEK, j.c10)
        LEFT JOIN kdii i ON j.c3 = i.c1
    WHERE 
        j.c10 >= @fec_ini AND j.c10 <= @fec_fin
        AND CONVERT(int, j.c1) BETWEEN 2 AND 5
        AND j.c2 = 1
        AND i.c3 = @linea
    GROUP BY 
        DATEPART(YEAR, j.c10), t1.Semana, j.c3
    ORDER BY 
        j.c3, DATEPART(YEAR, j.c10), t1.Semana;
    GO


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    • Propuesto como respuesta Joyce_ACModerator lunes, 26 de junio de 2017 15:13
    • Marcado como respuesta mcochegrus lunes, 26 de junio de 2017 21:35
    viernes, 23 de junio de 2017 22:16
  • Mi recomendación es que inicies obteniendo de forma independiente el listado de las semanas, en estos casos por lo general yo uso una función que desarrollo Itzik Ben-Gan, a partir de ese resultado tu podrás ponerlo en el FROM y hacer LEFT JOIN con el resto de tablas para que en caso de que el producto no tenga valores en dicha semana simplemente te lo muestre vació o 0.

    Funcion de Itzik:

    CREATE FUNCTION [dbo].[GetNums](@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
    AS
    RETURN
      WITH
        L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
        L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
        L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
        L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
        L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
        L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
        Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
                 FROM L5)
      SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
      FROM Nums
      ORDER BY rownum;
    

    Esta funcion permite que le suministres el valor mas bajo y el mas alto y te retorna un DataSet, en este caso usamos dicha funcion en conjunto con el rango de fechas, a partir de ahi obtenemos el numero de semanas de diferencia entre fecha inicial y fecha final, el resultado es el que puedes poner en un CTE y realizar un ajuste en tu consulta para que se hagan los left join contra esta tabla.

    DECLARE @InitialDate DATETIME2 = '20170401' ,
        @EndDate DATETIME2 = '20170901';
    
    SELECT  *
    FROM    dbo.GetNums(1, DATEDIFF(WEEK, @InitialDate, @EndDate)) AS Number
            CROSS APPLY ( VALUES
            ( DATEPART(WEEK, ( DATEADD(WEEK, Number.n - 1, @InitialDate) ))) ) AS SecondPart ( Increase ); 


    "Oh, the wind, the wind is blowing,through the graves the wind is blowing,Freedom soon will come; then well come from the shadows".The Partisan(Leonard Cohen) Email: me[at]geohernandez.net Blog:www.geohernandez.net

    • Propuesto como respuesta Joyce_ACModerator lunes, 26 de junio de 2017 15:13
    • Marcado como respuesta mcochegrus lunes, 26 de junio de 2017 21:35
    viernes, 23 de junio de 2017 22:22
  • Gracias lo intentaré. Me podrías explicar un poco acerca del porque pones coalescence?

    Michel Cochegrus

    viernes, 23 de junio de 2017 22:26
  • Gracias lo intentaré. Me podrías explicar un poco acerca del porque pones coalescence?

    Michel Cochegrus

    La función COALESCE() retorna el primer valor no NULL, que nos sirve para escribir cero en las semanas donde no hubo movimientos, es similar a la función ISNULL() que quizá sí la conoces.


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    viernes, 23 de junio de 2017 22:32
  • Hola buenos dias a todos,

    Ya intenté la consulta pero aún así no salen las semanas. Intenté usar la primer respuesta pero solo me daba como resultado la semana actual no se porque, usando la otra función si me da el listado de semanas. pero aún así no hace la inclusión de aquellas semanas que no hay movimientos, 

    Ejemplo del codigo simplificado:

    declare @fec_ini date='14/05/2017';

    declare @fec_fin date='26/06/2017';

    declare @linea varchar(5)='33';

    with t as (

    SELECT  increase

    FROM    dbo.GetNums(1, DATEDIFF(WEEK, @fec_ini, @fec_fin)) AS Number

            CROSS APPLY ( VALUES

            ( DATEPART(WEEK, ( DATEADD(WEEK, Number.n - 1, @fec_ini) ))) ) AS SecondPart ( Increase )

    )

    select

    t1.increase as Semana

    ,j.c3 as Clave


    from 

    t t1

    left join kdij j on (t1.increase=datepart(week,j.c10))


    where

    j.c10>=@fec_ini and j.C10<=@fec_fin 

    and j.c3='BLABOXSER20'

    order by t1.increase

    Resultados obtenidos:


    Michel Cochegrus

    lunes, 26 de junio de 2017 15:19
  • ademas creo que el problema es que aún que salieran las semanas, la columna de clave saldría null no? Como se puede resolver eso?

    Michel Cochegrus

    lunes, 26 de junio de 2017 15:24
  • Ya intenté la consulta pero aún así no salen las semanas.

    Es logico que no te aparezcan todas las semanas ya que has cambiado la union de OUTER a INNER al filtrar en la clausula WHERE el lado que no se preserva (j).

    declare @fec_ini date='14/05/2017';
    declare @fec_fin date='26/06/2017';
    declare @linea varchar(5)='33';
    with t as (
    SELECT  increase
    FROM    dbo.GetNums(1, DATEDIFF(WEEK, @fec_ini, @fec_fin)) AS Number
            CROSS APPLY ( VALUES
            ( DATEPART(WEEK, ( DATEADD(WEEK, Number.n - 1, @fec_ini) ))) ) AS SecondPart ( Increase )
    )
    select
    t1.increase as Semana
    ,j.c3 as Clave
    
    from 
    	t t1
    	LEFT join kdij j 
    	ON t1.increase = DATEPART(week,j.c10)
    	AND j.c10 >= @fec_ini and j.C10<=@fec_fin 
    	AND j.c3 = 'BLABOXSER20'
    order by t1.increase


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    lunes, 26 de junio de 2017 16:16
  • Ya logré hacer una consulta que me arroje la combinación de todos los productos con todas las semanas:

    declare @fec_ini date='14/05/2017';

    declare @fec_fin date='26/06/2017';

    declare @linea varchar(5)='33';

    with t as (

    SELECT  increase

    FROM    dbo.GetNums(1, DATEDIFF(WEEK, @fec_ini, @fec_fin)) AS Number

            CROSS APPLY ( VALUES

            ( DATEPART(WEEK, ( DATEADD(WEEK, Number.n , @fec_ini) ))) ) AS SecondPart ( Increase )

    )

    select datepart(year,@fec_fin) as Anio,

    t1.increase as Semana,i.c1

    from 

    t t1

    cross join KDII i 

    where i.c3=@linea

    order by i.c1,anio,semana

    Eso me trae por cada semana y cada producto un renglón.

    Ahora como uno eso con lo que quería hacer anteriormente?

    select datepart(year,j.c10) as Anio,datepart(week,j.c10) as Semana,j.c3 as Clave,

    sum(case when j.c5='A' then j.c11 when j.c5='D' then j.c11*(-1) end) as Unidades

    from kdij j  

    left join kdii i on (j.c3=i.c1)

    where j.c10>=@fec_ini and j.C10<=@fec_fin and j.c1>'01' and j.c1<'06'

    and j.c2=

    and i.c3=@linea

    group by datepart(year,j.c10),datepart(week,j.c10),j.c3

    order by j.c3,datepart(year,j.c10),datepart(week,j.c10)


    Michel Cochegrus

    lunes, 26 de junio de 2017 16:31