Skip to Content
avatar image
Former Member

Ayuda con query mostrar registros completos

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Nov 02, 2016 at 08:42 AM

    sustituye INNER JOIN OITM por LEFT JOIN OITM

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 07, 2016 at 03:56 PM

    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

    Add comment
    10|10000 characters needed characters exceeded