This is a SQL performance issue.
Back then, I had to write a set of queries to extract data from an ERP system. Most of them were quite simple, but one of them led to a rather vague request, and since then he has beaten me because we needed better.
The problem is not complicated. You have sales data. On each line, you can also indicate the quantity, sale price and seller code.
The commission is paid on the basis of a stepped sliding scale. The more they sell, the better the commission. Steps can be $ 1,000, $ 10,000, $ 10,000, and so on. The problem with the real world is more complicated, but, in fact, it is.
The only way to do this is to do something like this (obviously not a real request)
select qty, price, salesman, (select top 1 percentage from comissions where comisiones.salesman = saleslines.salesman and saleslines.qty > comisiones.qty order by comissiones.qty desc ) percentage from saleslines
this leads to the right commission, but terribly hard.
Is there a better way to do this? I'm not looking for someone to rewrite my sql, more “take a look at foobar queries”, and I can take it from there.
The structure of real life can be specified for different sellers, articles and customers, and even sales dates. It also changes from time to time, so everything should be controlled by the data in the tables ... iee I cannot set fixed ranges in sql. The current query returns about 3-400000 rows and takes about 20-30 seconds. Fortunately, it is used only monthly, but the slowness annoys me.
This is on mssql.
Yang
change
I had to give a more complex example from the very beginning. Now I understand that in my original example, there are a few essential elements of complexity, apologizing for everyone.
It may better capture him
select client-code, product, product-family, qty, price, discount, salesman, (select top 1 percentage from comissions where comisiones.salesman = saleslines.salesman and saleslines.qty > comisiones.qty and [ a collection of conditions which may or may not apply: Exclude rows if the salesman has offered discounts above max discounts which appear in each row in the commissions table There may be a special scale for the product family There may be a special scale for the product There may be a special scale for the client A few more cases ] order by [ The user can control the order though a table which can prioritize by client, family or product It normally goes from most to least specific. ] ) percentage from saleslines
This is not to say that a real request is not easy to follow. Just to make life more interesting, its naming is multilingual.
Thus, for each line of the sales line, the commission may differ.
This may seem overly complicated, but if you are thinking about how you pay a commission, that makes sense. You don’t want to pay anyone for selling goods at high discounts, you also want to be able to offer a particular customer a discount on a particular product if they buy X units. The seller must earn more if they sell more.
In all of the above cases, I exclude special offers with a limited duration.
I think partitions may be the solution, but I need to investigate this more indepth, since I know nothing about partitions. This gave me some ideas.