Ayuda con query mostrar registros completos

[Former Member]

buen dia,

Tengo un query que me trae los grupos de articulos con su total de venta en dolares.

El query es este:

SELECT T3.ItmsGrpNam as Name, sum(T1.[TotalSumSy]) as Total, T3.U_gpo1 as ProdA

FROM
OITB T3
INNER JOIN OITM T2 ON T2.ItmsGrpCod = T3.ItmsGrpCod
LEFT JOIN INV1 T1 ON T1.ItemCode = T2.ItemCode
LEFT outer JOIN OSLP T4 ON T1.[SlpCode] = T4.[SlpCode]

WHERE
T1.[DocDate] >=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AND T1.[DocDate] <=(DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))))
AND T1.TargetType <> '14' AND T4.Memo='VentasCHI' AND T3.U_gpo1 IS NULL
GROUP BY T3.ItmsGrpNam,T3.U_gpo1

Mi problema es que solo me trae registros que tengan ventas, es decir quiero que me traiga todas las familias aunque no tengan venta, que me muestre en el campo Total 0

[Former Member]

El o los moderadores tardan demasiado en publicar las preguntas, no se por que debe haber un moderador. La manera como se tenia antes el foro trabajaba sin problemas.

En fin, tuve que investigar y buscar en otros foros por que aqui ya no es de ayuda (por los moderadores)

El como lo resolvi fue asi:

SELECT
T3.ItmsGrpNam Name,sum(T1.[TotalSumSy]) as Total,
sum(T1.GrssProfSC) as 'GBruta', T3.U_gpo1 as ProdA,
(sum(T1.GrssProfSC)/NULLIF(sum(T1.[TotalSumSy]),0))*100 as Margen,
(datepart(day,getdate())) as NumDia,
DAY(EOMONTH(DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))))) as NumDiasMes,
DATEPART(dayofyear, getdate()) as NumDiaAno,
DATEDIFF(DAY, getdate(),(DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))))) as DiasRestantes
FROM
OITB T3
INNER JOIN OITM T2 ON T2.ItmsGrpCod = T3.ItmsGrpCod
LEFT JOIN INV1 T1 ON T1.ItemCode = T2.ItemCode
AND T1.[DocDate] >= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND T1.[DocDate] <= (DATEADD(ms, -3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1,0))))
AND T1.TargetType <> '14' AND EXISTS (SELECT 1 FROM OSLP T4 WHERE (T1.[SlpCode] = T4.[SlpCode])
AND (T4.Memo<>'VentasOEM'))

WHERE
T3.U_gpo1='A'
GROUP BY T3.ItmsGrpNam, T3.U_gpo1

[gonzalogomez]

sustituye INNER JOIN OITM por LEFT JOIN OITM