Pedro:
Prueba con estas dos consultas:
----- CONSULTA 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
-------CONSULTA 2------
/* Reporte de Facturas y Notas de Crédito por Fecha con el costo del artículo*/
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 '[Código Cliente]',
T2.CardName as '[Nombre Cliente]',
T1.itemcode as '[Código Items]',
T1.Dscription as '[Descripción de Artículo]',
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 Descuento]',
((100-T0.DiscPrcnt)/100)*T1.LineTotal as '[Total Final]',
T1.[StockPrice] as '[Costo Artículo]',
T1.[StockPrice]*T1.Quantity as '[Total Costo]',
T0.[GrossProfit] as '[Ganancia Bruta]'
from INV1 T1 inner join
