Skip to Content
0

Ayuda con query mostrar registros completos

Nov 01, 2016 at 08:26 PM

31

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Gonzalo Gomez Nov 02, 2016 at 08:42 AM
0

sustituye INNER JOIN OITM por LEFT JOIN OITM

Share
10 |10000 characters needed characters left characters exceeded
Carlos Salcedo Nov 07, 2016 at 03:56 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded