November 17, 2018 at 8:28 am
Hello community,
I have the following query that returns for each purchase the invoice of transportation expenses in order to calculate the percentage of additional expenses to be attributed to the Product.
The problem I have is when 2 purchase invoices have the same shipping expense invoice.
What happens is that in all the lines I have to concatenate all the invoices of purchases and not the number of each of them, as well as the measurements and the pallets.
I send you attach Excel File in this topic.
How can I resolve this situation?
This is my Query:
select
(select paises.NOME
from paises
where paises.NOMEABRV=(select fl.pncont from fl
where fl.no=fo.no and fo.estab=fl.estab)) as 'Country'
,fo.docdata as 'Invoice Date (Products)'
,fo2.u_nome as 'Transporter (Freight)'
,fo.nome as 'Supplier of Products'
,fo.u_tipo as 'Type of Product'
, ( SELECT SUM(fo.u_paletes)
FROM FO (NoLock)
inner join fo2 (NoLock) on fo2.fo2stamp=fo.fostamp
Where
fo2.u_tstamp<>''
and year(fo.docdata)='2018'
and fo2.u_nome like 'Transportes J. Morais' +'%'
and fo.no<>152
and fo.no<>157
) AS 'pallets'
,[dbo].[FnPesoTransporte](fo2.u_tstamp)[Peso]
,CAST(stuff(
( SELECT DISTINCT ','+Rtrim(fo.u_medidas)
FROM FO (NoLock)
inner join fo2 (NoLock) on fo2.fo2stamp=fo.fostamp
Where
fo2.u_tstamp<>''
and year(fo.docdata)='2018'
and fo2.u_nome like 'Transportes J. Morais' +'%'
and fo.no<>152
and fo.no<>157
FOR XML PATH('') ),1,1,'') AS VARCHAR(200)) AS 'Measures'
,fo.u_cubicage as 'cubic meters'
,fo2.u_adoc as 'freight invoice'
,AVG(fo2.u_valor) as 'Freight Value'
,CAST(stuff(
( SELECT DISTINCT ' | '+Rtrim(foo.adoc)
FROM FO FOO (NoLock)
inner join fo2 (NoLock) on fo2.fo2stamp=foo.fostamp
inner join fn (NoLock) on fn.fostamp=foo.fostamp
Where
fo2.u_tstamp<>''
and year(foo.docdata)='2018'
and fo2.u_nome like 'Transportes J. Morais' +'%'
and foo.no<>152
and foo.no<>157
AND FOO.fostamp IN(SELECT fo.fostamp FROM fo )
AND FOO.adoc in(SELECT fo.adoc FROM fo)
FOR XML PATH('') ),1,1,'') AS VARCHAR(200)) AS 'invoice of Products'
,sum(fn.etiliquido) as 'Invoice Products Value'
,ROUND(AVG(fo2.u_valor) / SUM(fn.etiliquido) * 100,2) as 'Rate of Costs'
from fo (NoLock)
inner join fo2 (NoLock) on fo2.fo2stamp=fo.fostamp
inner join fn (NoLock) on fn.fostamp=fo.fostamp
where fo2.u_tstamp<>''
and year(fo.docdata)='2018'
and fo2.u_nome like 'Transportes J. Morais' +'%'
and fo.no<>152
and fo.no<>157
group by fo2.u_adoc, fo.docdata,fo2.u_nome,fo.nome,fo.zona,fo.no,fo.estab,fo.u_tipo,fo.u_cubicage, fo2.u_tstamp
Many thanks,
Luis
November 17, 2018 at 8:37 am
Luis,
with over 6100 points, you've been around awhile. You know we need DDL and data with expected results to help.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 17, 2018 at 11:07 am
Hello Mike01
You ´r right , i will go to create DDL script for all tables and fields that i use on my Query.
Thanks
Luis
November 17, 2018 at 1:45 pm
hello Mike
I send in attach the entire script to create temp tables with data, i hope don´t forget any field.
i send you my function above : [dbo].[FnPesoTransporte](fo2.u_tstamp)[Peso] :
CREATE FUNCTION [dbo].[FnPesoTransporte](
@ftstampT VARCHAR(25)
--@PesoT int)
RETURNS INT
AS
BEGIN
DECLARE @Pesot NUMERIC (18,3)
SELECT @PesoT = fo.u_pesoFROM fo JOIN FO2 ON fo2.fo2stamp = Fo.fostamp
WHERE fo.fostamp = @ftstampTAND fo.no<>152 AND fo.no<>157
AND EXISTS (SELECT TOP 1 * FROM fo2 foo2 JOIN fo foo ON FOO2.fo2stamp = foo.fostampWHERE foo.fostamp = FO.fostamp)
RETURN @PesoTENDGO
END
GO
What i want to do is if for 2 invoices of products i have the same invoice freight invoice then concate the invoice product in one line and sum all the values. If i have a relation 1 to 1 , 1 invoice of product for 1 freight invoice then don´t concate the nº of my invoice of products.
See the excel file in attach, please.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply