Pedro:
Teste estas duas consultas:
----- CONSULTA 1-------
SELECT
T1.DocNum 'Número Interno SAP',
T1.CardCode AS 'Código de deudor/acredor',
T1.CardName AS 'Nombre de deudor/acredor',
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úmero Interno SAP',
T1.CardCode AS 'Código de deudor/acredor',
T1.CardName AS 'Nombre de deudor/acredor',
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------
/* Relatório de Faturas e Notas de Crédito por Data com o custo do artigo*/
select
CASE T0.DocSubType
WHEN 'DN' THEN 'Nota de Débito'
WHEN 'IB' THEN 'Boleto'
WHEN 'IE' THEN 'Fatura Isenta'
WHEN 'IX' THEN 'Fatura de Exportação'
ELSE 'Fatura'
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 '[Nome Cliente]',
T1.itemcode as '[Código Items]',
T1.Dscription as '[Descrição de Artigo]',
T1.WhsCode as '[Sucursal]',
T1.PriceBefDi as '[Preço Unitário]',
T1.DiscPrcnt as '[Desconto]',
T1.Price as '[Preço Final]',
T1.Quantity as '[Quantidade]',
T1.LineTotal as '[Total]',
T8.SlpName as '[Vendedor]',
T0.DiscPrcnt as '[Porcentagem Total Desconto]',
((100-T0.DiscPrcnt)/100)*T1.LineTotal as '[Total Final]',
T1.[StockPrice] as '[Custo Artigo]',
T1.[StockPrice]*T1.Quantity as '[Total Custo]',
T0.[GrossProfit] as '[Lucro Bruto]'
from INV1 T1 inner join
