Pessoal Bom dia,
Preciso de uma ideia para o caso, tenho uma consulta que roda no SQL Server (2104), quando transfiro o código para rodar dentro do meu ERP (SAP B1 - Gerador de Consultas) me retorna o seguinte erro:
1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Must specify table to select from. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.
no final do código tem um linha que realizo o período manual: and b.[Data da Venda] Between '2017-10-01' and '2017-10-31'
no SAP tenho que trocar essa informação por uma variável e ficará assim:
and b.[Data da Venda] Between [%0] and [%1]
Segue abaixo o código completo;
with CTE_RN as
(
select
--a.DocDate,
b.ItemCode as [Código do Item], /*Codigo do Item*/
--b.Dscription,
b.Price as [Vlr NF Entrada], /*Preço do NF Entrada*/
--a.Serial,
ROW_NUMBER() OVER(PARTITION BY b.ItemCode ORDER BY a.DocDate DESC) as RN
from OPCH a
inner join PCH1 b
on b.DocEntry = a.DocEntry
where a.CardName != 'BIODENTAL MEDICAL DIST. LTDA'
--and b.ItemCode Between '000001' and '060000'
), CTE_RN1 as
(
select
c.DocDate as [Data da Venda],
b.ItemCode as [Código do Item],
b.Dscription as [Descrição],
e.FirmName as [Fornecedor],
b.Quantity as [Quantidade Vendida],
a.AvgPrice as [Vlr do Item NF Entrada],
b.Price as [Vlr do Item NF Saida (Venda)],
b.LineTotal as [Vlr Total do Item],
c.Serial as [Numero NF],
f.NfmName as [Modelo NF],
b.CFOPCode as [CFOP],
(8.00) as 'Desp. ADM %',
(a.[AvgPrice]*0.08) as 'Vlr Desp. ADM',
(1.08) as 'Cont. Social %',
(a.[AvgPrice]*0.0108) as 'Vlr Cont. Social',
(4.08) as 'IRPL %',
(a.[AvgPrice]*0.0408) as 'Vlr IRPL',
(3.00) as 'Comissão %',
(a.[AvgPrice]*0.03) as 'Vlr Comissão',
(1.80) as 'Tx Cartão %',
(a.[AvgPrice]*0.0180) as 'Vlr Tx Cartão',
(5.00) as 'RT %',
(a.[AvgPrice] * 5.00) as 'Vlr RT',
(2.00) as 'Desconto MKT%',
(a.[AvgPrice] * 0.02) as 'Vlr Desconto MKT',
(3.00) as 'Antecipação %',
(a.[AvgPrice] * 0.03) as 'Vlr Antecipação',
((a.[AvgPrice]*0.08) + (a.[AvgPrice]*0.0108) + (a.[AvgPrice]*0.0408) + (a.[AvgPrice]*0.03) + (a.[AvgPrice]*0.0108) +
(a.[AvgPrice] * 0.05) + (a.[AvgPrice] * 0.02) + (a.[AvgPrice] * 0.03)) as 'Vlr Despesas Vendas',
(a.[AvgPrice] - ((a.[AvgPrice]*0.08) + (a.[AvgPrice]*0.0108) + (a.[AvgPrice]*0.0408) + (a.[AvgPrice]*0.03) +
(a.[AvgPrice]*0.0108) + (a.[AvgPrice]*0.05) + (a.[AvgPrice]*0.02) + (a.[AvgPrice]*0.06))) as 'Ponto de Equilibrio'
from OITW a
inner join INV1 b
on b.ItemCode = a.ItemCode
inner join OINV c
on c.DocEntry = b.DocEntry
inner join OITM d
on d.ItemCode = a.ItemCode
inner join OMRC e
on e.FirmCode = d.FirmCode
inner join ONFM f
on f.AbsEntry = c.Model
where a.WhsCode = '01'
--and c.DocDate Between '2017-10-01' and '2017-10-31'
and c.CANCELED = 'N'
and b.CFOPCode != '5405, 5117, 5152, 5910, 6403, 1152, 1914'
and c.CardName != 'BIODENTAL MEDICAL DIST LTDA'
and c.CardName != 'BIODENTAL MEDICAL DISTRIBUIDORA LTDA'
and c.CardName != 'SERGIO ROBERTO MELO BRINGEL'
and c.CardName != 'SEBASTIAO RAMILO BULCÃO BRINGEL'
and c.CardName != 'CD IRANDUBA SRB COMERCIO E SERVIÇOS LTDA'
and c.CardName != 'CD Iranduba - SRB COMERCIO E SERVICOS DE MATERIAIS DE CONSTRUCAO LTDA'
and c.CardName != 'SRB COMERCIO CONSTRUCAO'
--order by c.DocDate asc
)
select *
from CTE_RN a
full join CTE_RN1 b
on b.[Código do Item] = a.[Código do Item]
where b.[Data da Venda] != ''
and RN = 1
--and b.[Código do Item] = '016017'
and b.[Data da Venda] Between '2017-10-01' and '2017-10-31'
order by b.[Data da Venda], b.[Código do Item] asc
