November 2, 2014 at 10:20 am
Hello comunity
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
FROM
(select oristamp,conta, edeb, ecre from ml
where year(ml.data)=2014 and month(ml.data)=7
--group by oristamp, conta,edeb,ecre
) Xctb
inner join fi on fi.ftstamp = Xctb.oristamp
inner join ft on fi.ftstamp = ft.ftstamp
WHERE YEAR(fi.rdata)=2014 AND MONTH(fi.rdata)=7
AND fi.stns <> 1 and fi.epv<> 0
and ft.anulado = 0 and Xctb.conta = '321'
GROUP BY ft.ndoc,ft.fno,Xctb.conta,ft.tipodoc
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
How can solve this problem.
Many thanks
Luis Santos
November 2, 2014 at 10:35 am
1) Please provide sample table(s) and data with your actual output and the expected output.
2) This construct is DEVASTATINGLY BAD!! It prevents getting accurate statistics (which will lead to BAD plans) and will get you index SCANs instead of index SEEKs, another performance killer!
WHERE YEAR(fi.rdata)=2014 AND MONTH(fi.rdata)=7
Code it thusly:
WHERE fi.rdata >= '20140701'
AND fi.rdata < '20140801'
NEVER EVER wrap a column in a function if you can help it, and you almost always can!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 2, 2014 at 10:45 am
Hello Kevin
First thanks for your advise.
My invoice line have this 2 articles
REF DESIGN QTT ECUSTO
A article A 1
November 2, 2014 at 11:01 am
Hello Kevin
First thanks for your advise.
I will go attach a jpg file with my invoice line, and also the accountancy movment.
Of course the values are correct, but i need this query to check if on accountancy the value of account 321 is not correct.
This problem occur only if i have more than on row on my line invoice.
Many thanks for your help.
Luis Santos
November 2, 2014 at 11:27 am
we need something like this:
create table #test (a int, b money, c datetime ...)
insert #test (..) values (1, 1.1, '20140101'...)
.
.
.
select blah from #test where ...
actual output:
...
expected output:
...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 2, 2014 at 12:02 pm
Hello Kevin
This is the script to reproduce the problem:
--Invoice Header
CREATE TABLE dbo.ft (ftstamp VARCHAR(25), ndoc NUMERIC(3,0), fno NUMERIC(10,0), tipodoc NUMERIC(1,0), anulado BIT)
INSERT INTO dbo.ft (ftstamp, ndoc, fno, tipodoc, anulado)
VALUES ('LIL14070164865,029464325', 11, 10, 1, 0)
GO
--Invoice Lines
CREATE TABLE dbo.fi (rdata DATETIME,stns BIT, epv NUMERIC(19,6), qtt NUMERIC(11,3),ecusto NUMERIC(19,6), ftstamp VARCHAR(25))
INSERT INTO dbo.fi (rdata, stns, epv, qtt, ecusto, ftstamp)
VALUES ('2014-07-01', 0, 0, 0, 0, 'LIL14070164865,029464325')
GO
INSERT INTO dbo.fi (rdata, stns, epv, qtt, ecusto, ftstamp)
VALUES ('2014-07-01', 0, 0, 0, 0, 'LIL14070164865,029464325')
GO
INSERT INTO dbo.fi (rdata, stns, epv, qtt, ecusto, ftstamp)
VALUES ('2014-07-01', 0, 1.4, 5, 1.047679, 'LIL14070164865,029464325')
GO
INSERT INTO dbo.fi (rdata, stns, epv, qtt, ecusto, ftstamp)
VALUES ('2014-07-01', 0, 1.4, 2, 1.047881, 'LIL14070164865,029464325')
GO
INSERT INTO dbo.fi (rdata, stns, epv, qtt, ecusto, ftstamp)
VALUES ('2014-07-01', 0, 3.5, 3, 2.569176, 'LIL14070164865,029464325')
GO
INSERT INTO dbo.fi (rdata, stns, epv, qtt, ecusto, ftstamp)
VALUES ('2014-07-01', 0, 3.06, 2, 2.46, 'LIL14070164865,029464325')
GO
--accountancy movment for this invoice:
CREATE TABLE dbo.ml (CONTA VARCHAR(15),edeb NUMERIC(19,6), ecre NUMERIC (19,6), oristamp VARCHAR(25), data DATETIME)
INSERT INTO dbo.ml (CONTA, edeb, ecre, oristamp,data)
VALUES ('7111131', 0, 19.29, 'LIL14070164865,029464325', '2014-07-01')
GO
INSERT INTO dbo.ml (CONTA, edeb, ecre, oristamp,data)
VALUES ('611', 19.97, 0, 'LIL14070164865,029464325','2014-07-01')
GO
INSERT INTO dbo.ml (CONTA, edeb, ecre, oristamp,data)
VALUES ('321', 0, 19.97, 'LIL14070164865,029464325','2014-07-01')
GO
INSERT INTO dbo.ml (CONTA, edeb, ecre, oristamp,data)
VALUES ('24331131', 0, 4.44, 'LIL14070164865,029464325','2014-07-01')
GO
INSERT INTO dbo.ml (CONTA, edeb, ecre, oristamp,data)
VALUES ('111', 30.3, 0, 'LIL14070164865,029464325','2014-07-01')
GO
INSERT INTO dbo.ml (CONTA, edeb, ecre, oristamp,data)
VALUES ('7111111', 0, 5.81, 'LIL14070164865,029464325','2014-07-01')
GO
INSERT INTO dbo.ml (CONTA, edeb, ecre, oristamp,data)
VALUES ('24331111', 0, 0.76, 'LIL14070164865,029464325','2014-07-01')
GO
My atual result is :
DoctypeDocnrtotalcostof my Invoicelineaccountancy accountValue of Cost of invoice in accountancy DIF
11 10 19.961685000 321 79.880000 -59.918315
The expect result are:
DoctypeDocnrtotalcostof my Invoicelineaccountancy accountValue of Cost of invoice in accountancy DIF
11 10 19.961685000 321 19.96 - 0,001685
I hope these script help you.
Many thanks
Luis Santos
November 2, 2014 at 4:38 pm
This looks close, but not exact. Note: the way to solve these Aggregate query issues is to
1) do the select * without groupings so you can see all the data involved and
2) use Actual Query Plan and see where the actual row counts differ from what you expect. That shows exactly where the Cartesian is in this case
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end as [Value of Cost of invoice in accountancy],
--sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
--[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[DIF] = Sum(fi.ecusto*qtt) - Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end
--select xctb.*, fi.*, ft.*
FROM
(select oristamp,conta, edeb, ecre
from #ml ml
where year(ml.data)=2014 and month(ml.data)=7
--group by oristamp, conta,edeb,ecre
) Xctb
inner join #fi fi on fi.ftstamp = Xctb.oristamp
inner join #ft ft on fi.ftstamp = ft.ftstamp
WHERE YEAR(fi.rdata)=2014 AND MONTH(fi.rdata)=7
AND fi.stns <> 1 and fi.epv<> 0
and ft.anulado = 0 and Xctb.conta = '321'
GROUP BY ft.ndoc,ft.fno,Xctb.conta,ft.tipodoc
,Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 2, 2014 at 4:40 pm
Oh, VERY important point: I used TEMP TABLES in my example for you ON PURPOSE. Please don't give people a script that creates permanent tables.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply