Skip to Content
0

Help with query item family

Oct 18, 2016 at 09:02 PM

67

avatar image
Former Member

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

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

3 Answers

Gaurav Bali Oct 19, 2016 at 04:20 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Oct 19, 2016 at 01:32 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 07, 2016 at 03:54 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded