I have the following query to make a revenue report based on area groups from Exact Online:
select itemgroupcode
, itemgroupdescription
, code
, description
, country
, postcodegebied
, name
, financialyear
, financialperiod
, sum(quantity)
aantal
, sum(amountdc)
omzet
, sum(quantity2jaar)
aantal2014
, sum(omzet2jaar)
omzet2014
, sum(quantity1jaar)
aantal2015
, sum(omzet1jaar)
omzet2015
, sum(quantityhuidigejaar)
aantal2016
, sum(omzethuidigejaar)
omzet2016
from ( select substr(act.postcode, 1, 2)
, case
when financialyear = year(getdate()) - 0
then amountdc
else 0
end
* -1
omzethuidigejaar
, case
when financialyear = year(getdate()) - 1
then amountdc
else 0
end
* -1
omzet1jaar
, case
when financialyear = year(getdate()) - 2
then amountdc
else 0
end
* -1
omzet2jaar
, case
when financialyear = year(getdate()) - 0
then quantity
else 0
end
quantityhuidigejaar
, case
when financialyear = year (getdate()) - 1
then quantity
else 0
end
quantity1jaar
, case
when financialyear = year(getdate()) - 2
then quantity
else 0
end
quantity2jaar
, case
when substr(act.postcode, 1, 2) >= '10'
and substr(act.postcode, 1, 2) < '20'
then '1000-1999'
when substr(act.postcode, 1, 2) >= '20'
and substr(act.postcode, 1, 2) < '30'
then '2000-2999'
when substr(act.postcode, 1, 2) >= '30'
and substr(act.postcode, 1, 2) < '40'
then '3000-3999'
when substr(act.postcode, 1, 2) >= '40'
and substr(act.postcode, 1, 2) < '50'
then '4000-4999'
when substr(act.postcode, 1, 2) >= '50'
and substr(act.postcode, 1, 2) < '60'
then '5000-5999'
when substr(act.postcode, 1, 2) >= '60'
and substr(act.postcode, 1, 2) < '70'
then '6000-6999'
when substr(act.postcode, 1, 2) >= '70'
and substr(act.postcode, 1, 2) < '80'
then '7000-7999'
when substr(act.postcode, 1, 2) >= '80'
and substr(act.postcode, 1, 2) <= '89'
then '8000-8999'
when substr(act.postcode, 1, 2) >= '90'
and substr(act.postcode, 1, 2) <= '99'
then '9000-9999'
else 'unknown'
end
postcodegebied
, -1 * tle.amountdc
, tle.financialperiod
, tle.financialyear
, act.country
, act.name
, itm.code
, itm.description
, tle.quantity
, itm.itemgroupdescription
, itm.itemgroupcode
from transactionlines tle
join exactonlinerest.crm.accounts act
on act.code = tle.accountcode
join exactonlinerest.financial.glaccounts glt
on glt.code = tle.glaccountcode
--
-- Type 110: grootboekrekening van het type omzet
--
and glt.type = 110
join exactonlinerest.logistics.items itm
on tle.itemcode = itm.code
--
-- zodat er alleen transacties worden meegenomen die op een artikel geboekt zijn.
--
where tle.itemcode is not null
) tle2
group
by itemgroupcode
, itemgroupdescription
, code
, description
, country
, postcodegebied
, name
, financialyear
, financialperiod
order
by itemgroupcode
, financialyear
, itemgroupdescription
, code
, description
, country
, postcodegebied
, name
, financialperiod
When executing this query on the machine of a customer I expect the same result. But the outcome is different. Each amount and quantity is multiplied by a factor eight depending on whether the query is run as Exact Online user 'ME@acme.com' or 'OTHERPERSON@acme.com'.
How can this be occuring and how would I be able to fix this?
Your query misses a number of optimizations and does not include the full join conditions.
Please note that the unique natural or business key of for instance an item is both division (company) and the item code, so always include both in the join. When you forget to include division where applicable, you multiply the number of rows if you use identical (copies) divisions. With non-copies you get varying results.
Giving that you get 8 times as much and the presence of 3 joins, I guess you are running sometimes with 2 identical/copy divisions selected in Exact Online and that causes power(2, 3) = 8 times as high values.
Also, you are retrieving many general ledger transaction lines that you do not use in the aggregation like those of 3 years old or older. It is better to exclude those early with a clause like financialyear >= ....
The correct query should be something like:
select itemgroupcode
, itemgroupdescription
, code
, description
, country
, postcodegebied
, name
, financialyear
, financialperiod
, sum(quantity)
aantal
, sum(amountdc)
omzet
, sum(quantity2jaar)
aantal2014
, sum(omzet2jaar)
omzet2014
, sum(quantity1jaar)
aantal2015
, sum(omzet1jaar)
omzet2015
, sum(quantityhuidigejaar)
aantal2016
, sum(omzethuidigejaar)
omzet2016
from ( select substr(act.postcode, 1, 2)
, case
when financialyear = year(getdate()) - 0
then amountdc
else 0
end
* -1
omzethuidigejaar
, case
when financialyear = year(getdate()) - 1
then amountdc
else 0
end
* -1
omzet1jaar
, case
when financialyear = year(getdate()) - 2
then amountdc
else 0
end
* -1
omzet2jaar
, case
when tle.financialyear = year(getdate()) - 0
then tle.quantity
else 0
end
quantityhuidigejaar
, case
when tle.financialyear = year (getdate()) - 1
then tle.quantity
else 0
end
quantity1jaar
, case
when tle.financialyear = year(getdate()) - 2
then tle.quantity
else 0
end
quantity2jaar
, case
when substr(act.postcode, 1, 2) >= '10'
and substr(act.postcode, 1, 2) < '20'
then '1000-1999'
when substr(act.postcode, 1, 2) >= '20'
and substr(act.postcode, 1, 2) < '30'
then '2000-2999'
when substr(act.postcode, 1, 2) >= '30'
and substr(act.postcode, 1, 2) < '40'
then '3000-3999'
when substr(act.postcode, 1, 2) >= '40'
and substr(act.postcode, 1, 2) < '50'
then '4000-4999'
when substr(act.postcode, 1, 2) >= '50'
and substr(act.postcode, 1, 2) < '60'
then '5000-5999'
when substr(act.postcode, 1, 2) >= '60'
and substr(act.postcode, 1, 2) < '70'
then '6000-6999'
when substr(act.postcode, 1, 2) >= '70'
and substr(act.postcode, 1, 2) < '80'
then '7000-7999'
when substr(act.postcode, 1, 2) >= '80'
and substr(act.postcode, 1, 2) <= '89'
then '8000-8999'
when substr(act.postcode, 1, 2) >= '90'
and substr(act.postcode, 1, 2) <= '99'
then '9000-9999'
else 'unknown'
end
postcodegebied
, -1 * tle.amountdc
, tle.financialperiod
, tle.financialyear
, act.country
, act.name
, itm.code
, itm.description
, tle.quantity
, itm.itemgroupdescription
, itm.itemgroupcode
from transactionlines tle
join exactonlinerest..accounts act
on act.code = tle.accountcode
and act.division = tle.division
join exactonlinerest..glaccounts glt
on glt.code = tle.glaccountcode
--
-- Type 110: GL Account of type Revenue.
--
and glt.type = 110
and glt.division = tle.division
join exactonlinerest.logistics.items itm
on itm.code = tle.itemcode
and itm.division = tle.division
--
-- Only transaction lines with an item.
--
where tle.itemcode is not null
--
-- Only journal for revenues.
--
and tle.journalcode = '70'
--
-- Optimization: not interested in older transactions than 2 years.
--
and tle.financialyear >= year(getdate()) - 2
) tle2
group
by itemgroupcode
, itemgroupdescription
, code
, description
, country
, postcodegebied
, name
, financialyear
, financialperiod
order
by itemgroupcode
, financialyear
, itemgroupdescription
, code
, description
, country
, postcodegebied
, name
, financialperiod