Duda con Query para Ventas mensuales

[Former Member]

Estoy haciendo un query que me traiga las Ventas por determinada fecha, en este caso mensuales en donde traiga todas las facturas digamos del mes de agosto (no importa si fueron canceladas) y despues me traiga todas las notas de credito tambien de ese mes y me de un gran total con todo y margenes, grpo de articulos, etc.

Hay un caso similar en este link

Ejemplo, para facturas tengo

SELECT distinct t3.slpname,T0.cardname,T0.docnum, T0.docdate,
 T5.[ItmsGrpNam], sum(T1.[LineTotal]) AS Total1, sum(T1.grssprofit) as grssprofit, T7.[SeriesName] 
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
INNER JOIN OITM T4 on T1.ItemCode = t4.ItemCode 
INNER JOIN OITB T5 on t4.itmsgrpcod = t5.itmsgrpcod 
INNER JOIN NNM1 T7 ON T0.Series = T7.Series 
WHERE T0.[DocDate] >='20080901' AND  T0.[DocDate] <='20080930' AND T5.[ItmsGrpNam] = 'IMP')

Para notas de credito tengo

SELECT distinct t3.slpname,T0.cardname,T0.docnum, T0.docdate,
 T5.[ItmsGrpNam], sum(T1.[LineTotal]) AS Total1, sum(T1.grssprofit) as grssprofit, T7.[SeriesName] 
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
INNER JOIN OITM T4 on T1.ItemCode = t4.ItemCode 
INNER JOIN OITB T5 on t4.itmsgrpcod = t5.itmsgrpcod 
INNER JOIN NNM1 T7 ON T0.Series = T7.Series 
WHERE T0.[DocDate] >='20080901' AND  T0.[DocDate] <='20080930' AND T5.[ItmsGrpNam] = 'IMP'

Intente con este:

SELECT distinct T0.[DocNum], T0.[DocTotal], T2.[DocNum], T2.[DocTotal] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry LEFT  JOIN ORIN T2 ON T1.TrgetEntry = T2.DocEntry INNER JOIN RIN1 T3 ON T2.DocEntry = T3.DocEntry WHERE T3.[DocDate] >='[%0]' AND  T3.[DocDate] <='[%1]'

Pero el LEFT JOIN no me funciona. Solo me trae registros que tengan ambas tablas, de ahi yo pensaba simplemente hacer una resta y despues sumatoria.

Al final lo importante es que me de VTAS X GPO de ART X MARGEN en determinado mes, sumando notas de credito de facturas del mes aterior y sumando facturas de ese mes que hayan sido canceladas en el siguiente mes.

Ya se que se puede hacer algo con analisis de ventas pero no salen todos los campos que requieren y acomodado como quieren en una matrix tabulada, por el momento solo requiero eso.

Algun norte?

Gracias

[Former Member]

Este query es algo parecido a lo que tu buscas, sin embargo no es exactamente ......

Este te da los saldos por cliente por moneda, fecha, ventas, saldo y notas de credito

Este como tal en SAP no sirve de mucho pero al meterlo en ReportingServices cambia completamente por el acomodo que se le puede hacer.....da notas de credito, facturas, facturas de reserva, anticipos, notas de debito.

Creo que si quitas Docnum el tipo de doc y clase de doc podria salirte algo de lo que buscas.....

checalo y me dices.....

SELECT DISTINCT T0.[CardName],T0.[DocNum], 
CASE 
WHEN T0.[ObjType] = '13' AND T0.[isIns] = 'N' AND T0.[DocSubType] = '--' THEN 'FACTURA'  
WHEN T0.[ObjType] = '13' AND T0.[isIns] = 'Y' THEN 'F.RESERVA' 
WHEN T0.[ObjType] = '13' AND T0.[DocSubType] = 'DN' THEN 'N.DEBITO' END AS TIPODOC,
CASE WHEN T0.[ObjType] = '14' AND T0.[DocCur] = 'USD' THEN T0.[DocTotalFC]  END AS NCUSD,
CASE WHEN T0.[ObjType] = '14' AND T0.[DocCur] = '$' THEN T0.[DocTotal] END AS NCMXP,
CASE T0.[docType]
WHEN 'I' THEN 'ARTICULOS'
WHEN 'S' THEN 'SERVICIO' END AS CLASEdeDOC,
T2.[BaseRef],T0.[DocDate], T0.[DocDueDate], T0.[NumAtCard], T0.[FolioNum],
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[DocTotalFC] else T0.[DocTotal] END AS DocTotal,
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[DocTotalFC] END AS DocTotalUSD, CASE T0.[DocCur] WHEN '$' THEN T0.[DocTotal] END AS DocTotalMXP,
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[PaidFC] else T0.[PaidToDate] END AS PaidToDate,
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[PaidFC] END AS PaidToDateUSD, CASE T0.[DocCur] WHEN '$' THEN T0.[PaidToDate] END AS PaidToDateMXP,
'Saldo'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC) else (DocTotal-PaidToDate) end), CASE DocCur WHEN '$' THEN 'PESOS' WHEN 'USD' THEN 'DOLARES' WHEN 'xGF' THEN 'xGF'  END as Moneda,
'Por Vencer' = (case when (Doccur='$' and getdate() <=  DocDueDate)  then (DocTotal-PaidToDate) else (case when getdate() <=  DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC) else 0 end) end),
' 0-30' = (case when (Doccur='$' and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30)  then (T0.DocTotal-T0.PaidToDate) else (case when  DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30 and Doccur='USD'  then (DocTotalFC-PaidFC) else 0 end) end)
FROM OINV T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry WHERE T0.DocDate >= @FechaInicial AND T0.DocDate <= @FechaFinal AND T0.[CardName] in (@Clientes) AND T0.DocTotal-T0.PaidToDate <> '0'
UNION ALL

SELECT DISTINCT T0.[CardName],T0.[DocNum], 
CASE 
WHEN T0.[ObjType] = '14' THEN 'N.CREDITO' END AS TIPODOC,
CASE WHEN T0.[ObjType] = '14' AND T0.[DocCur] = 'USD' THEN T0.[DocTotalFC]  END AS NCUSD,
CASE WHEN T0.[ObjType] = '14' AND T0.[DocCur] = '$' THEN T0.[DocTotal] END AS NCMXP,
CASE T0.[docType]
WHEN 'I' THEN 'ARTICULOS'
WHEN 'S' THEN 'SERVICIO' END AS CLASEdeDOC,
T2.[BaseRef],T0.[DocDate], T0.[DocDueDate], T0.[NumAtCard], T0.[FolioNum],
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[DocTotalFC] else T0.[DocTotal] END AS DocTotal,
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[DocTotalFC] END AS DocTotalUSD, CASE T0.[DocCur] WHEN '$' THEN T0.[DocTotal] END AS DocTotalMXP,
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[PaidFC] else T0.[PaidToDate] END AS PaidToDate,
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[PaidFC] END AS PaidToDateUSD, CASE T0.[DocCur] WHEN '$' THEN T0.[PaidToDate] END AS PaidToDateMXP,
'Saldo'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC) else (DocTotal-PaidToDate) end), CASE DocCur WHEN '$' THEN 'PESOS' WHEN 'USD' THEN 'DOLARES' WHEN 'xGF' THEN 'xGF'  END as Moneda,
'Por Vencer' = (case when (Doccur='$' and getdate() <=  DocDueDate)  then (DocTotal-PaidToDate) else (case when getdate() <=  DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC) else 0 end) end),
' 0-30' = (case when (Doccur='$' and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30)  then (T0.DocTotal-T0.PaidToDate) else (case when  DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30 and Doccur='USD'  then (DocTotalFC-PaidFC) else 0 end) end) 
FROM ORIN T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN RIN1 T2 ON T0.DocEntry = T2.DocEntry WHERE T0.DocDate >= @FechaInicial AND T0.DocDate <= @FechaFinal AND T0.[CardName] in (@Clientes) AND T0.DocTotal-T0.PaidToDate <> '0'

UNION ALL

SELECT DISTINCT T0.[CardName],T0.[DocNum], 
CASE 
WHEN T0.[ObjType] = '203' THEN 'F.ANTICIPO' END AS TIPODOC,
CASE WHEN T0.[ObjType] = '14' AND T0.[DocCur] = 'USD' THEN T0.[DocTotalFC]  END AS NCUSD,
CASE WHEN T0.[ObjType] = '14' AND T0.[DocCur] = '$' THEN T0.[DocTotal] END AS NCMXP,
CASE T0.[docType]
WHEN 'I' THEN 'ARTICULOS'
WHEN 'S' THEN 'SERVICIO' END AS CLASEdeDOC,
T2.[BaseRef],T0.[DocDate], T0.[DocDueDate], T0.[NumAtCard], T0.[FolioNum],
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[DocTotalFC] else T0.[DocTotal] END AS DocTotal,
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[DocTotalFC] END AS DocTotalUSD, CASE T0.[DocCur] WHEN '$' THEN T0.[DocTotal] END AS DocTotalMXP,
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[PaidFC] else T0.[PaidToDate] END AS PaidToDate,
CASE T0.[DocCur]
WHEN 'USD' THEN T0.[PaidFC] END AS PaidToDateUSD, CASE T0.[DocCur] WHEN '$' THEN T0.[PaidToDate] END AS PaidToDateMXP,
'Saldo'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC) else (DocTotal-PaidToDate) end), CASE DocCur WHEN '$' THEN 'PESOS' WHEN 'USD' THEN 'DOLARES' WHEN 'xGF' THEN 'xGF'  END as Moneda,
'Por Vencer' = (case when (Doccur='$' and getdate() <=  DocDueDate)  then (DocTotal-PaidToDate) else (case when getdate() <=  DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC) else 0 end) end),
' 0-30' = (case when (Doccur='$' and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30)  then (T0.DocTotal-T0.PaidToDate) else (case when  DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30 and Doccur='USD'  then (DocTotalFC-PaidFC) else 0 end) end)
FROM ODPI T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN DPI1 T2 ON T0.DocEntry = T2.DocEntry WHERE T0.DocDate >= @FechaInicial AND T0.DocDate <= @FechaFinal AND T0.[CardName] in (@Clientes) AND T0.DocTotal-T0.PaidToDate <> '0'

[former_member203638]

Bueno, trabajare en mi query solo para anexarle esa parte, apoyandome de aqui.

Si no funciona molestare de nuevo.

Alessandro.

Edited by: Alessandro Santinelli on Nov 21, 2008 12:46 AM

[Former Member]

Se me hace que te estas complicando de mas, el union no creo que aplica en este caso.

Puedes hacer un INNER JOIN a la ORIN para traerte las Notas de credito y que te aparezca el monto de la NC en dado casi que esa factura haya sido cancelada.....

Por que no pones que es lo que quieres y veo si tengo algo parecido o mas o menos te digo que hacer.

De esos he hecho ya varios.

[former_member203638]

Sencillo, la perte que se me esta complicando es en las Notas de Credito.

El query esta estruturado de la sig manera

Serie | Ventas Sin IVA | Descuento Ventas |____________ | Fletes Cobrados | Facturas x Serie | Vendedor

La busqueda por fecha y vendedor la hago.

En el espacio en blanco, me gustaría anexar una columna que me de el monto que hay en Notas de credito.

Saludos

Alessandro.

[former_member203638]

Me contesto:

Si anexo a mi query --> INNER JOIN ORIN T3 ON T2.SlpCode = T3.SlpCode y pido que me sumarize montos de nota de credito --> SUM(T3.Doctotal-T3.VatSum-T3.TotalExpns) me arroja una cantidad extraorbitante.

Que estare haciendo mal?

[Former Member]

Ya te funciono??

El 203 creo que es de facturas de anticipo algo asi....necesitaria meterme otra vez....

Claro esta que ese query funciona mucho mucho mejor si lo pones en Reportingservices, se ve mejor y mas funcional.

Si fui de tu ayuda unos puntitos no serian malos!

😛

[former_member203638]

De mucha ayuda Carlos, de echo aprovecho tu tema para preguntar, sobre lo mismo pero con mi query:

Me saca el monto de venta por rango de fecha con descuentos, flete e IVA, y por vendedor, uso el "union" por que tengo 4 series (disintos giros de negocio) aqui proyecto 2 nada mas para que no quede tan largo.

De que manera podria yo anexar al query otra columna metiendo los montos en notas de credito.

He leido muchos tema que usan el "union" a la tabla ORIN, pero si yo uso el union para otra serie, viendo tu query me da ideas pero no me queda claro de que manera podria realizarlo.

SELECT Distinct TIPO = '*SERIE1*', SUM(T0.DocTOTAL-T0.VatSum-T0.TotalExpns) 'Ventas S/IVA(Desc Aplicados)',sum(T0.Discsum) 'Desc Ventas',sum(T0.TotalExpns) 'Fletes Cobrados',sum(T0.vatsum) 'IVA (Vta+Flete)', Count(T0.Docnum) '# Fact x Serie',t2.slpname Vendedor 
FROM OINV T0 
INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES 
INNER JOIN OSLP T2 ON T0.SLPCODE=T2.SLPCODE
WHERE T0.DocDate BETWEEN [%0] AND [%1] 
AND T0.CANCELED != 'Y' AND T0.DocType = 'I' AND (T2.Slpname='[%2]' or T2.Slpname= '[%3]' )  AND T1.SeriesName = 'SELLOS_M' AND
T0.DocEntry IN (Select Distinct DocEntry FROM INV1 WHERE TargetType != 14)
GROUP BY t2.slpname
union
SELECT Distinct TIPO = '*SERIE2*', SUM(T0.DocTOTAL-T0.VatSum-T0.TotalExpns) 'Ventas S/IVA(Desc Aplicados)',sum(T0.Discsum) 'Desc Ventas',sum(T0.TotalExpns) 'Fletes Cobrados',sum(T0.vatsum) 'IVA (Vta+Flete)', Count(T0.Docnum) '# Fact x Serie',t2.slpname Vendedor
FROM OINV T0 
INNER JOIN NNM1 T1 ON T0.SERIES = T1.SERIES 
INNER JOIN OSLP T2 ON T0.SLPCODE=T2.SLPCODE
WHERE T0.DocDate BETWEEN [%0] AND [%1] 
AND T0.CANCELED != 'Y' AND T0.DocType = 'I' AND (T2.Slpname='[%2]' or T2.Slpname= '[%3]' ')   AND T1.SeriesName = 'SELLOS' AND
T0.DocEntry IN (Select Distinct DocEntry FROM INV1 WHERE TargetType != 14)
GROUP BY t2.slpname
union

Saludos.

Alessandro

[Former Member]

Bueno ya me funciono....

SELECT DISTINCT X.SLPNAME,X.CARDNAME,X.QUANTITY,X.TRGETENTRY,W.DOCENTRY,X.DOCNUM,X.DOCDATE,X.ITMSGRPNAM,X.TOTAL1,ISNULL(W.TOTAL1,0) AS TOT,
(X.GRSSPROFIT-isnull(W.GRSSPROFIT,0)) AS GRSSPROFIT, X.SERIESNAME FROM

(SELECT DISTINCT SLPNAME,CARDNAME,DOCNUM,QUANTITY,TRGETENTRY,DOCDATE,ITMSGRPNAM,SUM(ISNULL(TOTAL1,0)) AS TOTAL1,
SUM(ISNULL(GRSSPROFIT,0)) AS GRSSPROFIT,SERIESNAME FROM(
SELECT distinct t3.slpname,T0.cardname,T1.QUANTITY,T0.docnum, T0.docdate,T1.[TRGETENTRY],
 T5.[ItmsGrpNam], sum(T1.[LineTotal]) AS Total1, sum(T1.grssprofit) as grssprofit, T7.[SeriesName] 
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
INNER JOIN OITM T4 on T1.ItemCode = t4.ItemCode 
INNER JOIN OITB T5 on t4.itmsgrpcod = t5.itmsgrpcod 
INNER JOIN NNM1 T7 ON T0.Series = T7.Series 
WHERE T1.[BaseType] <> '203' AND T0.[DocDate] >='20081001' AND  T0.[DocDate] <='20081022'
GROUP BY t3.slpname,T0.cardname,T0.docnum,T1.QUANTITY,T1.TRGETENTRY,T0.docdate, T5.[ItmsGrpNam],T1.[LineTotal],T1.grssprofit, T7.[SeriesName]
)A GROUP BY SLPNAME,CARDNAME,DOCNUM,QUANTITY,TRGETENTRY,DOCDATE,ITMSGRPNAM,TOTAL1,GRSSPROFIT,SERIESNAME ) X LEFT JOIN 

(SELECT DISTINCT SLPNAME,CARDNAME,DOCNUM,QUANTITY,DOCENTRY,DOCDATE,ITMSGRPNAM,SUM(ISNULL(TOTAL1,0)) AS TOTAL1,
SUM(ISNULL(GRSSPROFIT,0)) AS GRSSPROFIT,SERIESNAME FROM(
SELECT distinct t3.slpname,T0.cardname,T0.docnum,T1.QUANTITY,T0.docdate,T0.[DOCENTRY],
 T5.[ItmsGrpNam], sum(T1.[LineTotal]) AS Total1, sum(T1.grssprofit) as grssprofit, T7.[SeriesName] 
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
INNER JOIN OITM T4 on T1.ItemCode = t4.ItemCode 
INNER JOIN OITB T5 on t4.itmsgrpcod = t5.itmsgrpcod 
INNER JOIN NNM1 T7 ON T0.Series = T7.Series 
WHERE T1.[BaseType] <> '203' AND T0.[DocDate] >='20081001' AND  T0.[DocDate] <='20081022' 
GROUP BY t3.slpname,T0.cardname,T0.docnum, T1.QUANTITY,T0.DOCENTRY,T0.docdate, T5.[ItmsGrpNam],T1.[LineTotal],T1.grssprofit, T7.[SeriesName]
)B GROUP BY SLPNAME,CARDNAME,DOCNUM,QUANTITY,DOCENTRY,DOCDATE,ITMSGRPNAM,TOTAL1,GRSSPROFIT,SERIESNAME )W 
ON X.TRGETENTRY=W.DOCENTRY AND X.ITMSGRPNAM=W.ITMSGRPNAM AND X.QUANTITY=W.QUANTITY
ORDER BY X.SERIESNAME

Fueron muchas horas nacha, pero como quiero aqui se los dejo por si alguien tiene algo similar.

PD: No me puedo dar puntos a mi mismo?? jejejej

[former_member203638]

Hola Carlos:

Muy bueno el query, una pregunta me marca un error incorrect syntax near '203'

Si me funciona yo te doy los puntitos...ejjeje

Saludos

Ya me funciono

Edited by: Alessandro Santinelli on Nov 20, 2008 7:32 PM

Edited by: Alessandro Santinelli on Nov 20, 2008 7:36 PM

[Former Member]

Bueno aqui yo otra vez.....

Pude hacer todo en un solo query:

SELECT DISTINCT X.SLPNAME,X.CARDNAME,X.DOCNUM,X.DOCDATE,X.ITMSGRPNAM,X.TOTAL1,ISNULL(W.TOTAL1,0) AS TOT,
(X.GRSSPROFIT-isnull(W.GRSSPROFIT,0)) AS GRSSPROFIT, X.SERIESNAME FROM

(SELECT DISTINCT SLPNAME,CARDNAME,DOCNUM,DOCDATE,ITMSGRPNAM,SUM(ISNULL(TOTAL1,0)) AS TOTAL1,
SUM(ISNULL(GRSSPROFIT,0)) AS GRSSPROFIT,SERIESNAME FROM(
SELECT distinct t3.slpname,T0.cardname,T0.docnum, T0.docdate,
 T5.[ItmsGrpNam], sum(T1.[LineTotal]) AS Total1, sum(T1.grssprofit) as grssprofit, T7.[SeriesName] 
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
INNER JOIN OITM T4 on T1.ItemCode = t4.ItemCode 
INNER JOIN OITB T5 on t4.itmsgrpcod = t5.itmsgrpcod 
INNER JOIN NNM1 T7 ON T0.Series = T7.Series 
WHERE T1.[BaseType] <> '203' AND T0.[DocDate] >='20080901' AND  T0.[DocDate] <='20080930' AND T5.[ItmsGrpNam] = 'IMP'
GROUP BY t3.slpname,T0.cardname,T0.docnum, T0.docdate, T5.[ItmsGrpNam],T1.[LineTotal],T1.grssprofit, T7.[SeriesName]
)A GROUP BY SLPNAME,CARDNAME,DOCNUM,DOCDATE,ITMSGRPNAM,TOTAL1,GRSSPROFIT,SERIESNAME ) X LEFT JOIN 

(SELECT DISTINCT SLPNAME,CARDNAME,DOCNUM,DOCDATE,ITMSGRPNAM,SUM(ISNULL(TOTAL1,0)) AS TOTAL1,
SUM(ISNULL(GRSSPROFIT,0)) AS GRSSPROFIT,SERIESNAME FROM(
SELECT distinct t3.slpname,T0.cardname,T0.docnum, T0.docdate,
 T5.[ItmsGrpNam], sum(T1.[LineTotal]) AS Total1, sum(T1.grssprofit) as grssprofit, T7.[SeriesName] 
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
INNER JOIN OITM T4 on T1.ItemCode = t4.ItemCode 
INNER JOIN OITB T5 on t4.itmsgrpcod = t5.itmsgrpcod 
INNER JOIN NNM1 T7 ON T0.Series = T7.Series 
WHERE T1.[BaseType] <> '203' AND T0.[DocDate] >='20080901' AND  T0.[DocDate] <='20080930' AND T5.[ItmsGrpNam] = 'IMP'
GROUP BY t3.slpname,T0.cardname,T0.docnum, T0.docdate, T5.[ItmsGrpNam],T1.[LineTotal],T1.grssprofit, T7.[SeriesName]
)B GROUP BY SLPNAME,CARDNAME,DOCNUM,DOCDATE,ITMSGRPNAM,TOTAL1,GRSSPROFIT,SERIESNAME )W ON X.SERIESNAME=W.SERIESNAME 
ORDER BY SERIESNAME

El detalle viene al ultimo del query, donde tengo la W ON x.seriesname=w.seriesname, con este no obtengo los resultados correctos, veo que si pongo un x.docnum=w.docnum toda la columna de Notas de Credito me sale NULL o 0, si pongo cualquier otro como slpname,itmsgrpname o hasta el total me salen diferentes, se repiten notas de credito y demas y el valor al final no es correcto.

algun comentario?

[Former Member]

Les comento que he afinado el query y los resultados por separado son identicos a lo que dice Analisis de Ventas, por lo cual solo tengo duda de como integrarlo como uno solo y que haga la resta de los totales.

Es decir total1 de facturas menos Total1 de Notas de Credito, grssprofit de facturas menos grssprofit de notas de credito.

El query de arriba es el de facturas y el de abajo es de notas de credito.

SELECT distinct t3.slpname,T0.cardname,T0.docnum, T0.docdate,
 T5.[ItmsGrpNam], sum(T1.[LineTotal]) AS Total1, sum(T1.grssprofit) as grssprofit, T7.[SeriesName] 
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
INNER JOIN OITM T4 on T1.ItemCode = t4.ItemCode 
INNER JOIN OITB T5 on t4.itmsgrpcod = t5.itmsgrpcod 
INNER JOIN NNM1 T7 ON T0.Series = T7.Series 
WHERE T1.[BaseType] <> '203' AND T0.[DocDate] >='20080901' AND  T0.[DocDate] <='20080930'
 AND T5.[ItmsGrpNam] = 'POEM'
GROUP BY t3.slpname,T0.cardname,T0.docnum, T0.docdate, T5.[ItmsGrpNam],T1.[LineTotal],T1.grssprofit, T7.[SeriesName]

SELECT distinct t3.slpname,T0.cardname,T0.docnum, T0.docdate,
 T5.[ItmsGrpNam], sum(T1.[LineTotal]) AS Total1, sum(T1.grssprofit) as grssprofit, T7.[SeriesName] 
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
INNER JOIN OITM T4 on T1.ItemCode = t4.ItemCode 
INNER JOIN OITB T5 on t4.itmsgrpcod = t5.itmsgrpcod 
INNER JOIN NNM1 T7 ON T0.Series = T7.Series 
WHERE T1.[BaseType] <> '203' AND T0.[DocDate] >='20080901' AND  T0.[DocDate] <='20080930'
 AND T5.[ItmsGrpNam] = 'POEM'
GROUP BY t3.slpname,T0.cardname,T0.docnum, T0.docdate, T5.[ItmsGrpNam],T1.[LineTotal],T1.grssprofit, T7.[SeriesName]

Algun tip...