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

Question

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=1 

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

Answer 1

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.

Answer 2

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

Answer 3

Gracias lo intentaré. Me podrías explicar un poco acerca del porque pones coalescence?

---

Michel Cochegrus

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

> 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

Answer 8

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=1 

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