La empresa quiere un reporte de compras vs ventas por línea de producto, respecto a ventas facturas en 2016 en su momento, estas se crearon sin el asistente. y no encuentro la forma de relacionarlos.
Tengo este query:
select distinct t.Invoice,i.BaseCard AS [Customer/VendorCode],b.CardName as [Customer/VendorName],b.Country,upper(c.Name) as Country1,i.itemcode,g.ItmsGrpNam,o.ItemName,o.U_PRODUCT_FAMILY,o.U_PRODUCTGROUP,
i.DocDate as PostingDate,dbo.GetMonthNameByNum(month(i.DocDate))as NameMonth,month(i.DocDate)as Month,p.Quantity as QuantityPO,i.Quantity as QuantitySo,
p.Price as FOB, case when p.Price>0 then i.Quantity*p.Price else 0 end as TotalFOB,
i.price as PFD ,i.Quantity *i.Price as TotalPFD,
case when p.Price>0 and i.Price>0 then cast((case when i.TargetType=-1 or i.TargetType=14 then -1 else 1 end ) *cast(round((1-( (i.Quantity*p.Price)/(i.Quantity*i.Price)))*100,0) as int)as varchar(15)) +' %'
else 'NA' end as GP,p.Currency as CurrencyPo,i.Currency as CurrencySo,pc.DocNum as DocnumPo,t.DocNumSo,t.DocentryPo as ReceivPo,i.BaseEntry as DeliverySo,i.LineNum as LineNumSo,t.NumInvoices,
case i.TargetType when 14 then 'Crédito' when 15 then 'Entregado' when 165 then 'Corrección Fctura' when -1 then 'Cancelado'end as StatusFacura
from (
select distinct (select count(p.Itemcode)from pdn1 p where p.DocEntry=t.DocentryPo)as Total,t.DocentryPo,t.BaseDocNum as DocNumSo,t.invoice,(select count(p.Itemcode)from inv1 p where p.DocEntry=t.invoice and p.AcctCode!=411211)as Total2,
(select count(distinct TrgetEntry) from dln1 d1 where d1.BaseDocNum=t.BaseDocNum or d1.DocEntry=t.DocentrySo)as NumInvoices
from (
select distinct m.DocEntry As DocentryPo,d.DocEntry as DocentrySo,d.TrgetEntry as invoice,m.itemcode,(select sum(p1.Quantity)from pdn1 p1 where p1.DocEntry=m.DocEntry and p1.itemcode=m.itemcode)as Suma1,
(select sum(p1.Quantity)from dln1 p1 where p1.DocEntry=d.DocEntry and p1.itemcode=m.itemcode)as Suma2,d.BaseDocNum
from oitl m inner join
itl1 l on l.LogEntry=m.LogEntry and l.ItemCode=m.ItemCode and m.BaseType=22
inner join itl1 l2 on l2.MdAbsEntry=l.MdAbsEntry and l.ItemCode=l2.itemcode
inner join itl1 m2 on m2.MdAbsEntry=l2.MdAbsEntry and m2.itemcode=m.ItemCode
inner join oitl o on o.LogEntry=m2.LogEntry and m2.itemcode=o.itemcode
and o.BaseType in(17,-1) and o.CreateDate>=m.CreateDate
inner join dln1 d on d.DocEntry=o.DocEntry and d.itemcode=m2.ItemCode
and l2.SysNumber=m2.SysNumber
group by m.DocEntry,d.DocEntry,m.itemcode,d.TrgetEntry,d.BaseDocNum) t
where t.suma1>=t.suma2)t
inner join inv1 i on i.DocEntry=t.invoice
left join pdn1 p on p.DocEntry=t.DocentryPo and i.ItemCode=p.ItemCode
left join opor pc on pc.DocEntry=p.DocEntry
inner join oitm o on i.ItemCode=o.itemcode
inner join oitb g on g.ItmsGrpCod=o.ItmsGrpCod
inner join ocrd b on b.CardCode=i.BaseCard
inner join ocry c on c.Code=b.Country
where t.invoice>0 and t.Total=t.Total2 and t.NumInvoices=1
and i.AcctCode!=411211 and p.Quantity>0 and pc.docnum is not null
and i.docentry=30 or t.Total=t.Total2
or t.Total=t.Total2 and t.NumInvoices=1
and i.AcctCode=411211 and i.docentry=30