Skip to Content
avatar image
Former Member

Help with query item family

Hi,

I have a query that show item families with total sales.

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

My problem is that only show registries that sales <>0, and i want all families even if the total is 0, can anyone help me with this.

tnks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Oct 19, 2016 at 04:20 AM

    Hi Carlos

    Try below query

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

    FROM
    OITB T3
    INNER JOIN OITM T2 ON T2.ItmsGrpCod = T3.ItmsGrpCod AND T3.U_gpo1 IS NULL
    LEFT JOIN INV1 T1 ON T1.ItemCode = T2.ItemCode and 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'
    LEFT outer JOIN OSLP T4 ON T1.[SlpCode] = T4.[SlpCode] AND T4.Memo='VentasCHI'

    GROUP BY T3.ItmsGrpNam,T3.U_gpo1

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2016 at 01:32 PM

    Hi,

    Try below query:

    SELECT T3.ItmsGrpNam as Name, sum(T1.[TotalSumSy]) as Total, T3.U_gpo1 as ProdA FROM OITB T3 LEFT JOIN OITM T2 ON T2.ItmsGrpCod = T3.ItmsGrpCod LEFT JOIN INV1 T1 ON T1.ItemCode = T2.ItemCode LEFT 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

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

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

    It Works with this query:

    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