Reporte de Ventas Totales por Fecha

[Former Member]

Buenas les escribo para solicitarles ayuda con repecto a un informe de ventas. Actualmente en donde trabajo se manejaban algunos reportes con XL Reporter pero estamos en version 8.82 y ahora vamos a actualizar a 9.1 necesitamos una manera de migrar los reportes del XL a Crystal Reports, o por lo menos necesito en este caso conseguir un query con el que pueda sacar las ventas mensuales por lineas, realice una consulta por generador pero no me arroja el mismo resultado que el reporte que siempre hemos usado hay ventas que no salen reflejadas y si en el XL, ademas aquí no me aparecen las notas. en el que yo realice filtro las notas por [TargetType], si es 14 son notas. les agradezco su ayuda para conseguir un query y asi exportarlo al crystal.

SELECT T1.[DocStatus], T1.[DocNum], T1.[DocDate], T1.[CardCode], T2.[CardName], T0.[ItemCode], T0.[Dscription], T0.[Quantity], T0.[LineTotal], T0.[TargetType], T0.[TrgetEntry], T3.[U_BKV_LINEA], T3.[U_BKV_SERIE], T3.[U_BKV_COLECCION], T4.[SlpName], T4.[Memo]

FROM INV1 T0  INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.BaseCard = T2.CardCode INNER JOIN OITM T3 ON T0.ItemCode = T3.ItemCode INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode

WHERE T1.[CardCode] =  T2.[CardCode] AND  T3.[ItemCode] =  T0.[ItemCode] AND  T0.[SlpCode] =  T4.[SlpCode] AND  T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1]

de antemano muchas gracias.

[former_member227744]

Pedro:

Prueba con estas dos consultas:

----- QUERY 1-------

SELECT

T1.DocNum 'N° Interno SAP',

T1.CardCode AS 'Código de deudor/acreedor',

T1.CardName AS 'Nombre de deudor/acreedor',

T3.StateB 'Región',

T1.FolioNum, month(T1.DocDate) AS 'mes de contabilización',

year(T1.DocDate) 'Año',Max (T0.ItemCode) AS 'Número de artículo',

T0.Dscription AS 'Descripción artículo/serv.',

sum(T0.Quantity) AS 'Cantidad',

AVG(T0.Price) AS 'Precio',

sum(T0.LineTotal) AS 'Total líneas'

FROM  [dbo].[INV1] T0 

INNER  JOIN [dbo].[OINV] T1  ON  T1.DocEntry = T0.DocEntry

inner join [dbo].[OITM] T2 on T0.ItemCode = t2.ItemCode

INNER JOIN [dbo].[INV12] T3 on T1.[DocEntry] = T3.[DocEntry]

WHERE (T1.docdate  >= '[%1]') AND (T1.docdate  <= '[%2]')  

group by T1.CardCode, T1.CardName, month(T1.DocDate), year(T1.DocDate),T0.ItemCode,T0.Dscription, T1.FolioNum, T3.StateB, T1.DocNum

Union All

SELECT

T1.DocNum 'N° Interno SAP',

T1.CardCode AS 'Código de deudor/acreedor',

T1.CardName AS 'Nombre de deudor/acreedor', 

T3.StateB 'Región',

T1.FolioNum, month(T1.DocDate) AS 'mes de contabilización',

year(T1.DocDate) 'Año',max (T0.ItemCode) AS 'Número de artículo',

T0.Dscription AS 'Descripción artículo/serv.',

sum(T0.Quantity *-1) AS 'Cantidad',

AVG(T0.Price) AS 'Precio',

sum(T0.LineTotal *-1) AS 'Total líneas'

FROM  [dbo].[rin1] T0 

INNER  JOIN [dbo].[orin] T1  ON  T1.DocEntry = T0.DocEntry

inner join [dbo].[OITM] T2 on T0.ItemCode = t2.ItemCode

INNER JOIN [dbo].[INV12] T3 on T1.[DocEntry] = T3.[DocEntry]

WHERE   (T1.docdate  >= '[%1]' )and (T1.docdate  <= '[%2]')

group by T1.CardCode, T1.CardName, month(T1.DocDate), year(T1.DocDate),T0.ItemCode,T0.Dscription, T1.FolioNum, T3.StateB, T1.DocNum

-------QUERY 2------

/* Reporte de Facturas y Notas de Crédito por Fecha con el costo del articulo*/

select

CASE T0.DocSubType

      WHEN 'DN' THEN 'Nota de Débito'

    WHEN 'IB' THEN 'Boleta'

    WHEN 'IE' THEN 'Factura Exenta'

    WHEN 'IX' THEN 'Factura de Exportación'

    ELSE 'Factura'

END as TipoDoc,

T0.DocNum as '[Num Interno]',

T0.FolioNum as '[Folio Fiscal]',

T1.DocDate as '[Fecha Contabilización]',

T6.GroupName as '[Grupo Cliente]',

T2.CardCode as '[Codigo Cliente]',

T2.CardName as '[Nombre Cliente]',

T1.itemcode as '[Codigo Items]',

T1.Dscription as '[Descripcion de Articulo]',

T1.WhsCode as '[sucursal]',

T1.PriceBefDi as '[Precio Unitario]',

T1.DiscPrcnt as '[Descuento]',

T1.Price as '[Precio Final]',

T1.Quantity as '[Cantidad]',

T1.LineTotal as '[Total]',

T8.SlpName as '[Vendedor]',

T0.DiscPrcnt as '[porcentaje Total Dcto]',

((100-T0.DiscPrcnt)/100)*T1.LineTotal as '[Total_Final]',

T1.[StockPrice] as '[Costo_Articulo]',

T1.[StockPrice]*T1.Quantity as '[Total_Costo]',

T0.[GrosProfit] as '[Ganancia Bruta]'

from INV1 T1 inner join OINV T0 on T1.docentry = T0.docentry

inner join OCRD T2 on T0.CardCode = T2.CardCode

inner join OCRG T6 on T2.GroupCode = T6.GroupCode

inner join OCTG T7 on T0.GroupNum = T7.GroupNum

inner join OSLP T8 on T0.SlpCode = T8.SlpCode

where T0.CANCELED =  'N'

and( T0.docdate >= '[%0]' and T0.docdate <= '[%1]'  )

UNION ALL

/* NOTAS DE CREDITO */

select

'Nota de Crédito' as TipoDoc,

T0.DocNum as '[Num Interno]',

T0.FolioNum as '[Folio Fiscal]',

T1.DocDate as '[Fecha Contabilización]',

T6.GroupName as '[Grupo Cliente]',

T2.CardCode as '[Codigo Cliente]',

T2.CardName as '[Nombre Cliente]',

T1.itemcode as '[Codigo Items]',

T1.Dscription as '[Descripcion de Articulo]',

T1.WhsCode as '[sucursal]',

T1.PriceBefDi as '[Precio Unitario]',

T1.DiscPrcnt as '[Descuento]',

T1.Price as '[Precio Final]',

(T1.Quantity*-1) as '[Cantidad]',

(T1.LineTotal*-1) as '[Total]',

T8.SlpName as '[Vendedor]',

T0.DiscPrcnt as '[porcentaje Total Dcto]',

(((100-T0.DiscPrcnt)/100)*T1.LineTotal)*-1 as '[Total_Final]',

T1.[StockPrice] as '[Costo_Articulo]',

T1.[StockPrice]*(T1.Quantity*-1) as '[Total_Costo]',

T0.[GrosProfit] as '[Ganancia Bruta]'

from RIN1 T1 inner join ORIN T0 on T1.docentry = T0.docentry

inner join OCRD T2 on T0.CardCode = T2.CardCode

inner join OCRG T6 on T2.GroupCode = T6.GroupCode

inner join OCTG T7 on T0.GroupNum = T7.GroupNum

inner join OSLP T8 on T0.SlpCode = T8.SlpCode

where T0.CANCELED =  'N'

and( T0.docdate >= '[%0]' and T0.docdate <= '[%1]'  )

Espero sean de utilidad.

Saludos,

Manuel Díaz G.

[Former Member]

Buenas ya los probe y excelente me arroja la cantidad y monto correcto  me sirvieron de mucho los dos, ahora solo  voy a agregar algunos campos que me hacen falta para las estadísticas y listo. muchas gracias de verdad

[former_member227744]

Perfecto, no olvides cerrar la discusión y puntuar las mejores respuestas.

Saludos,

Manuel Díaz

[gonzalogomez]

Prueba con este código:

SELECT T1.[DocStatus], T1.[DocNum], T1.[DocDate], T1.[CardCode], T2.[CardName], T0.[ItemCode], T0.[Dscription], T0.[Quantity], T0.[LineTotal], T0.[TargetType], T0.[TrgetEntry], T3.[U_BKV_LINEA], T3.[U_BKV_SERIE], T3.[U_BKV_COLECCION], T4.[SlpName], T4.[Memo]

FROM INV1 T0  INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T1.Cardcode = T2.CardCode LFT JOIN OITM T3 ON T0.ItemCode = T3.ItemCode INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode

WHERE  T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1]

[Former Member]

lo probé muchas gracias pero me sigue arrojando diferencias