Obtener resultados semanales con filtro de fecha en dos columnas distintas

Question

Buen día expertos, me estoy enfrentando con lo siguiente:

Requiero generar un query para un reporte, en el que se debe visualizar el número de semana, la suma de los montos de las facturas que se vencen en esa semana, y la suma de las promesas de pago que se agendaron para esa semana.

Mi primer problema fue que si uso una cláusula WHERE normal, sólo toma las facturas que serán pagadas en la misma semana que se vencen, y el resto las deja fuera. Hice el intento con una tabla temporal y un ciclo WHILE:

CREATE TABLE #TempTable (Contador tinyint, SumaPorVencer varchar(20), NuevaPrueba varchar(20))

DECLARE @Counter INT 
SET @Counter = 25
WHILE (@Counter <= 30)
BEGIN

INSERT INTO #TempTable

SELECT @Counter AS [Contador], SUM(SumaPorVencer) AS [Suma por Vencer], SUM(NuevaPrueba) AS [Suma Nueva Prueba] FROM
(
       SELECT DISTINCT
             CASE
                    WHEN DATEPART(WEEK, T0.DocDueDate) BETWEEN ISNULL(@Counter, 1) AND ISNULL(@Counter, 53) THEN DATEPART(WEEK,T0.DocDueDate)
             END AS [PruebaVencimiento], 
             DATEPART(WEEK,T0.DocDueDate) AS SemanaVencimiento,
             CASE 
                    WHEN T0.DocCur = 'USD' AND DATEPART(WEEK, T0.DocDueDate) BETWEEN ISNULL(@Counter, 1) AND ISNULL(@Counter, 53) THEN SUM(T0.DocTotalFC - T0.PaidFC) 
                    WHEN T0.DocCur = '$' AND DATEPART(WEEK, T0.DocDueDate) BETWEEN ISNULL(@Counter, 1) AND ISNULL(@Counter, 53) THEN SUM(T0.DocTotal - T0.PaidToDate) 
             END AS 'SumaPorVencer',
             A1.FechaCompromiso, DATEPART(WEEK,A1.FechaCompromiso) AS SemanaCompromiso,
             CASE
                    WHEN DATEPART(WEEK, A1.FechaCompromiso) BETWEEN ISNULL(@Counter, 1) AND ISNULL(@Counter, 53) THEN A1.Saldo
             END AS [NuevaPrueba] 
       FROM GLASSFIBER.dbo.OINV T0
             INNER JOIN GLASSFIBER.dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry
             INNER JOIN GLASSFIBER.dbo.OSLP T2 ON T0.SlpCode = T2.SlpCode
             LEFT JOIN INDICADORES.dbo.Pagos_PP_Facturas A1 ON T0.DocNum = A1.Factura
       WHERE
             T1.[TargetType] <> '14' AND 
             T0.Canceled NOT IN ('C', 'Y') AND 
             (T0.DocTotal - T0.PaidToDate <> 0) AND 
             (DATEPART(WEEK, T0.DocDueDate) BETWEEN ISNULL(@Counter, 1) AND ISNULL(@Counter, 53)) OR
             (DATEPART(WEEK, A1.FechaCompromiso) BETWEEN ISNULL(@Counter, 1) AND ISNULL(@Counter, 53)) AND
             DATEPART(YEAR, T0.DocDueDate) = 2020
       GROUP BY T0.DocDueDate, T0.DocCur, A1.FechaCompromiso, A1.Saldo
       
) Tabla

SET @Counter  = @Counter  + 1

END

SELECT Contador, SumaPorVencer, NuevaPrueba FROM #TempTable

DROP TABLE #TempTable

En principio cumple con su función, pero en cuanto quiero obtener el resultado de más de 5 semanas (en el ejemplo puse de la 25 a la 30), demora arriba de 5 segundos en ejecutarse, lo que me hace pensar que está mal hecho y puede optimizarse.

El resultado esperado es el siguiente:

Los campos con los que hago el filtro de las semanas son T0.DocDueDate (Fecha de vencimiento de Factura) y A1.FechaCompromiso (Fecha en la que se hará el pago).

Espero puedan orientarme, cualquier comentario o sugerencia serán de gran ayuda. Muchas gracias!

El problema está interesante para escribir una respuesta, pero dejas todo el trabajo del lado de quien quiere ayudarte. Mi recomendación es crear un [mcve] con una sola tabla y los campos necesarios para comprender tu problema y escribir una solución que ya luego tu adaptes a tu necesidad (de trabajo) real. — jachguate, Jul 02 '20 at 19:29
Me quedé picado y escribí una respuesta. Déjame saber si no interpreté bien tu pregunta para eliminarla... pues leí un par de veces tu código, pero no estoy seguro que se corresponda con lo que interpreto que realmente necesitas. — jachguate, Jul 02 '20 at 20:14
@jachguate Estimado muchisimas gracias por tomarte el tiempo de responderme, incluso recrear el escenario. Me da bastante pena y tienes mucha razón debí simplificarlo en algo más digerible, me sentía muy presionado por resolverlo y no pensé en esto. Tu solución es exactamente lo que estaba buscando. De nuevo mil gracias! — Bis V, Jul 02 '20 at 23:08

score 1 · Accepted Answer · answered Jul 02 '20 at 20:13

Según logro interpretar, lo que pretendes es calcular por separado el valor de cada campo en cada semana del año.

Para realizar un ejemplo digerible, he reducido tu problema al mínimo, con una sola tabla (creada al vuelo) llamada Deuda y que contiene algunos datos que me he inventado, con los campos FechaVencimiento y FechaPromesa, además de ValorTotal y ValorPagado (según puedo interpretar en tu SQL, la idea va por allí, con cosas de más, lo sé, pero básicamente se reduce a esto).

Lo que hago con ello es realizar dos consultas separadas, una para calcular la SumaPorVencer cada semana y otra para calcular la SumaPromesa de cada semana también. Finalmente hago un full outer join de los resultados de ambas consultas para construir el resultado final.

with
Deuda as (
select *
  from (values (1, cast('20200105' as date), cast('20200115' as date), cast(1000.0 as money), cast(100.0 as money))
             , (2, '20200105', '20200131', 300, 0)
             , (3, '20200108', '20200108', 500, 150)
             , (4, '20200115', '20200115', 1000, 0)
             , (4, '20200209', '20200221', 1000, 0)
       ) q1 (NumeroFactura, FechaVencimiento, FechaPromesa, ValorTotal, ValorPagado)
)
,
PorVencer as (
select   year(t0.FechaVencimiento) Anio
       , datepart(week, t0.FechaVencimiento) Semana
       , sum(t0.ValorTotal - t0.ValorPagado) SumaPorVencer
  from Deuda t0
 group by year(t0.FechaVencimiento), datepart(week, FechaVencimiento)
)
,
Promesa as (
select   year(t0.FechaPromesa) Anio
       , datepart(week, t0.FechaPromesa) Semana
       , sum(t0.ValorTotal - t0.ValorPagado) SumaPromesa
  from Deuda t0
 group by year(t0.FechaPromesa), datepart(week, FechaPromesa)
)
select   coalesce(v.Anio, p.Anio) Anio
       , coalesce(v.Semana, p.Semana) Semana
       , v.SumaPorVencer
       , p.SumaPromesa
  from PorVencer v
       full outer join Promesa p on p.Anio = v.Anio and p.Semana = v.Semana

El full outer join es necesario, pues puede ser que haya semanas que tengan un dato pero no el otro. Con este join nos aseguramos que aparezcan todos en el resultado final.

Advertencia de rendimiento

Esperaría que la consulta tenga un rendimiento aceptable en conjuntos de datos medianos y pequeños. Si hablamos de un conjunto de datos grande, dado que se utilizan partes de la fecha y el motor difícilmente puede tirar de los índices necesarios, si el rendimiento es pobre, puedes voltear cada uno de los resultados intermedios en tablas temporales, indizarlas y sobre este construir el resultado final.

No suelo optimizar prematuramente así que esto vendría solamente si la experiencia te enseña que es necesario.

Obtener resultados semanales con filtro de fecha en dos columnas distintas

1 Answers1

Advertencia de rendimiento