Ayuda con query para ventas x familia

[Former Member]

Tengo este query:

SELECT ItmsGrpNam,Total,GBruta,Numdia,NumDiasMes,Pronostico FROM (

SELECT T3.[ItmsGrpNam], sum(T1.[TotalSumSy]) as Total, sum(T1.GrssProfSC) as 'GBruta',

(sum(T1.GrssProfSC)/NULLIF(sum(T1.[TotalSumSy]),0))*100 as Margen,

DAY(GETDATE()) as NumDia,

DAY(EOMONTH(getdate())) as NumDiasMes,

CAST(T5.U_VentasMTY as numeric) AS Pronostico

FROM OINV T0

LEFT JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

LEFT JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]

LEFT JOIN [dbo].[@PRONOSTICOS] T5 ON T3.ItmsGrpNam = T5.Name

WHERE T0.[DocDate] >='20160501' AND  T0.[DocDate] <='20160531' AND T1.TargetType <> '14' AND T4.Memo='VentasMTY'

GROUP BY T3.[ItmsGrpNam],T5.U_VentasMTY  ) x

Funciona pero quiero que me traiga todas las familias de mi tabla @PRONOSTICOS y no solo las que tuvieron ventas, que me falta modificar?

[juancarlos_huerta]

Hay varias formas de lograrlo, intenta con este query:

SELECT ItmsGrpNam,Total,GBruta,Numdia,NumDiasMes,Pronostico FROM (
SELECT T3.[ItmsGrpNam], sum(T1.[TotalSumSy]) as Total, sum(T1.GrssProfSC) as 'GBruta',
(sum(T1.GrssProfSC)/NULLIF(sum(T1.[TotalSumSy]),0))*100 as Margen,
DAY(GETDATE()) as NumDia,
DAY(EOMONTH(getdate())) as NumDiasMes,
CAST(T5.U_VentasMTY as numeric) AS Pronostico
FROM OITM T2
LEFT JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
LEFT JOIN [dbo].[@PRONOSTICOS] T5 ON T3.ItmsGrpNam = T5.Name
LEFT JOIN INV1 T1 T1.ItemCode = T2.ItemCode
LEFT JOIN OINV T0 ON T0.DocEntry = T1.DocEntry AND T0.[DocDate] >='20160501' AND  T0.[DocDate] <='20160531' AND T1.TargetType <> '14'
INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode] AND T4.Memo='VentasMTY'
GROUP BY T3.[ItmsGrpNam],T5.U_VentasMTY) x

Saludos y suerte,

JC:

[Former Member]

Siguen saliendo solo las familias que tienen movimeinto.....

Tengo al rededor de 50 familias y solo me salen 26 que son las que tienen movimiento.