sql server - SQL query: finding percentages and prices - Stack Overflow

admin管理员组

文章数量:1024725

I have two tables:

• The first is used to determine the percentage for the supplier according to salesperson, customer, and quantity.
• The second is used to determine the price according to customer, article and quantity. customer is not unique in either tables.

I have created a DBFiddle here.

In the fiddle you can see the results of the join on customer, but it is not entirely correct as some rows are not allowed.

For example the 2nd one: if prices.qty is 50+ then the percentage.qty is >20, and therefore only the second percentage, 0.50 is allowed, not the first (0.25).

The table info:

[percentages]

supplier	salesperson	customer	qty	percent
John	Anne	David	0	0.25
John	Anne	David	20	0.50
John	Anne	Mary	0	0.25
Paul	Andrew	David	0	0.25

I have two tables:

• The first is used to determine the percentage for the supplier according to salesperson, customer, and quantity.
• The second is used to determine the price according to customer, article and quantity. customer is not unique in either tables.

I have created a DBFiddle here.

In the fiddle you can see the results of the join on customer, but it is not entirely correct as some rows are not allowed.

For example the 2nd one: if prices.qty is 50+ then the percentage.qty is >20, and therefore only the second percentage, 0.50 is allowed, not the first (0.25).

The table info:

[percentages]

supplier	salesperson	customer	qty	percent
John	Anne	David	0	0.25
John	Anne	David	20	0.50
John	Anne	Mary	0	0.25
Paul	Andrew	David	0	0.25

[prices]

Customer	article	qty	price
David	X	0	50
David	X	50	40
David	Y	0	50
Mary	X	0	50
Mary	Y	0	55

Quantity (qty) is the lower cut-off of a quantity: it gives the minimum nr of sold items to which the percentage (in the 1st table) or the price (in the 2nd table) can be applied. For exampl,e given John, Anne and David as supplier, salesperson and customer, if the nr of sold item is 0-19 then the percentage is 0.25, if sold items are 20 or more then the percentage is 0.50. Similarly for price: given David and X as customer and article, the price is 50 if the nr of sold items is 0 to 49, otherwise if more the price is 40.

These are the expected results:

[Results]

supplier	salesperson	customer	qty	percent	article	qty	price
John	Anne	David	0	0.25	X	0	50
John	Anne	David	20	0.50	X	0	50
John	Anne	David	20	0.50	X	50	40
John	Anne	David	0	0.25	Y	0	50
John	Anne	David	20	0.50	Y	0	50
John	Anne	Mary	0	0.25	X	0	50
John	Anne	Mary	0	0.25	Y	0	55
Paul	Andrew	David	0	0.25	X	0	50
Paul	Andrew	David	0	0.25	X	50	40
Paul	Andrew	David	0	0,25	Y	0	50

That is, I have all the possible combinations of percentages and prices that make sense. For example, I do not have a row where percentages.qty is 0 and price.qty is 50, if there is a different percentage for a qty higher than 50.

• sql
• sql-server

Share Improve this question Follow edited Nov 22, 2024 at 9:02 Davide asked Nov 18, 2024 at 15:40 DavideDavide 1144 bronze badges 11

• Please provide a Minimal Reproducible Example For questions regarding sql this typically involves, table structure, example data and queries as TEXT and not as image. Additionally to providing these informations within the question, you are also welcome to create a runable fiddle of your problem. Finally, please also tag the respective DBMS you are using ... – derpirscher Commented Nov 18, 2024 at 15:46
• Here is a fiddle to help us collaborate on a solution dbfiddle.uk/2SxOdvL5 – Bart McEndree Commented Nov 18, 2024 at 15:50
• @BartMcEndree What in the question tells you OP is using MS SQL Server? Besides that, I'm not even sure what OP is actually asking ... – derpirscher Commented Nov 18, 2024 at 15:52
• 3 @BartMcEndree No, the correct way would be to ask for clarification, and vote/flag to close as "Needs details or clarity" or needing "Debugging details". – Mark Rotteveel Commented Nov 18, 2024 at 16:10
• 1 Please read this >15 year old article: bad habits to kick using old style joins – Luuk Commented Nov 20, 2024 at 11:48

 |  Show 6 more comments

1 Answer 1

Sorted by: Reset to default 0

Your description is still confusing. However, your expected results omit only one candidate row from a join between the two tables on customer. If the qty values for each of the two tables are converted to quantity ranges, that omitted row seems to be the only row where the ranges do not overlap.

Based on that, I would propose the following logic:

1. Calculate quantity ranges for each row of both tables. The original qty becomes the lower bound and LEAD(qty) - 1 becomes the upper bound. (We can use a closed interval, since we are dealing with discrete integer values.) If LEAD(qty) - 1 is null (no value found) the upper end of the range is unbounded.
2. Join these intermediate results on customer.
3. Further limit the join to overlapping ranges. A standard test for overlapping closed intervals is lower1 <= upper2 AND lower2 <= upper1. To handle null (unbounded) upper range values, we can substitute a large value using ISNULL().

The result would be something like:

WITH PercentagesQtyRange AS (
    SELECT
        supplier, salesperson, customer, qty, [percent],
        qty AS QtyLower,
        LEAD(Qty) OVER(PARTITION BY supplier, salesperson, customer ORDER BY qty)
            - 1 AS QtyUpper
    FROM percentages
),
PricesQtyRange AS (
    SELECT
        customer, article, qty, price, qty AS QtyLower,
        LEAD(Qty) OVER(PARTITION BY customer, article ORDER BY qty)
            - 1 AS QtyUpper
    FROM prices
)
SELECT *,
    GREATEST(PER.QtyLower, PR.QtyLower) AS OverlapLower,
    LEAST(PER.QtyUpper, PR.QtyUpper) AS OverlapUpper
FROM PercentagesQtyRange PER
JOIN PricesQtyRange PR
    ON PR.customer = PER.customer
    AND PR.QtyLower <= ISNULL(PER.QtyUpper, 999999999)  -- or ISNULL(..., PR.QtyLower)
    AND PER.QtyLower <= ISNULL(PR.QtyUpper, 999999999)  -- or ISNULL(..., PER.QtyLower)
ORDER BY
    PER.customer, PER.supplier, PER.salesperson, PR.article,
    OverlapLower

LEAD() function is a SQL window function. Window functions allow access to and calculations on values from other rows in the results. using OVER(...) to define the scope of the rows that they access without using a GROUP BY. Typically they are aggregation functions like COUNT(), SUM() or MAX(), but may also be any of LEAD(), LAG(), FIRST_VALUE(), and LAST_VALUE() that are defined specifically as a window function. The OVER(...) clause defines the scope of the rows being accessed. The PARTITION BY specifies the logical grouping of data, and limits the window function to just one group of rows. The ORDER BY defines an ordering within each partition, for those functions (like LEAD()) that depend on order. An OVER() clause may also contain a ROWS ... or RANGE ... clause to include multiple rows in the window function calculation, but we don't need that for the LEAD() function. Wee only need to access one value on one following row.

The CTE calculated ranges are as follows:

supplier	salesperson	customer	qty	percent	QtyLower	QtyUpper
John	Anne	David	0	0.25	0	19
John	Anne	David	20	0.5	20	null
Paul	Andrew	David	0	0.25	0	null
John	Anne	Mary	0	0.25	0	null

customer	article	qty	price	QtyLower	QtyUpper
David	X	0	50	0	49
David	X	50	40	50	null
David	Y	0	50	0	null
Mary	X	0	50	0	null
Mary	Y	0	55	0	null

Final Results:

supplier	salesperson	customer	qty	percent	QtyLower	QtyUpper	customer	article	qty	price	QtyLower	QtyUpper	OverlapLower	OverlapUpper
John	Anne	David	0	0.25	0	19	David	X	0	50	0	49	0	19
John	Anne	David	20	0.5	20	null	David	X	0	50	0	49	20	49
John	Anne	David	20	0.5	20	null	David	X	50	40	50	null	50	null
John	Anne	David	0	0.25	0	19	David	Y	0	50	0	null	0	19
John	Anne	David	20	0.5	20	null	David	Y	0	50	0	null	20	null
Paul	Andrew	David	0	0.25	0	null	David	X	0	50	0	49	0	49
Paul	Andrew	David	0	0.25	0	null	David	X	50	40	50	null	50	null
Paul	Andrew	David	0	0.25	0	null	David	Y	0	50	0	null	0	null
John	Anne	Mary	0	0.25	0	null	Mary	X	0	50	0	null	0	null
John	Anne	Mary	0	0.25	0	null	Mary	Y	0	55	0	null	0	null

Omitted row (customer matches, but ranges do not overlap):

supplier	salesperson	customer	qty	percent	QtyLower	QtyUpper	customer	article	qty	price	QtyLower	QtyUpper	OverlapLower	OverlapUpper
John	Anne	David	0	0.25	0	19	David	X	50	40	50	null	50	19

See this db<>fiddle for a demo.

The overlap range calculations in the result are for information only, but they do make use of the LEAST() and GREATEST() functions introduced in SQL Server 2022. For earlier SQL Server versions, you can use an IIF() equivalent together with COALESCE() to handle null upper bound values.

    IIF(PER.QtyLower > PR.QtyLower, PER.QtyLower, PR.QtyLower) AS OverlapLower,
    COALESCE(IIF(PER.QtyUpper < PR.QtyUpper, PER.QtyUpper, PR.QtyUpper), PER.QtyUpper, PR.QtyUpper) AS OverlapUpper

See this db<>fiddle.

I have two tables:

• The first is used to determine the percentage for the supplier according to salesperson, customer, and quantity.
• The second is used to determine the price according to customer, article and quantity. customer is not unique in either tables.

I have created a DBFiddle here.

In the fiddle you can see the results of the join on customer, but it is not entirely correct as some rows are not allowed.

For example the 2nd one: if prices.qty is 50+ then the percentage.qty is >20, and therefore only the second percentage, 0.50 is allowed, not the first (0.25).

The table info:

[percentages]

supplier	salesperson	customer	qty	percent
John	Anne	David	0	0.25
John	Anne	David	20	0.50
John	Anne	Mary	0	0.25
Paul	Andrew	David	0	0.25

I have two tables:

• The first is used to determine the percentage for the supplier according to salesperson, customer, and quantity.
• The second is used to determine the price according to customer, article and quantity. customer is not unique in either tables.

I have created a DBFiddle here.

In the fiddle you can see the results of the join on customer, but it is not entirely correct as some rows are not allowed.

For example the 2nd one: if prices.qty is 50+ then the percentage.qty is >20, and therefore only the second percentage, 0.50 is allowed, not the first (0.25).

The table info:

[percentages]

supplier	salesperson	customer	qty	percent
John	Anne	David	0	0.25
John	Anne	David	20	0.50
John	Anne	Mary	0	0.25
Paul	Andrew	David	0	0.25

[prices]

Customer	article	qty	price
David	X	0	50
David	X	50	40
David	Y	0	50
Mary	X	0	50
Mary	Y	0	55

Quantity (qty) is the lower cut-off of a quantity: it gives the minimum nr of sold items to which the percentage (in the 1st table) or the price (in the 2nd table) can be applied. For exampl,e given John, Anne and David as supplier, salesperson and customer, if the nr of sold item is 0-19 then the percentage is 0.25, if sold items are 20 or more then the percentage is 0.50. Similarly for price: given David and X as customer and article, the price is 50 if the nr of sold items is 0 to 49, otherwise if more the price is 40.

These are the expected results:

[Results]

supplier	salesperson	customer	qty	percent	article	qty	price
John	Anne	David	0	0.25	X	0	50
John	Anne	David	20	0.50	X	0	50
John	Anne	David	20	0.50	X	50	40
John	Anne	David	0	0.25	Y	0	50
John	Anne	David	20	0.50	Y	0	50
John	Anne	Mary	0	0.25	X	0	50
John	Anne	Mary	0	0.25	Y	0	55
Paul	Andrew	David	0	0.25	X	0	50
Paul	Andrew	David	0	0.25	X	50	40
Paul	Andrew	David	0	0,25	Y	0	50

That is, I have all the possible combinations of percentages and prices that make sense. For example, I do not have a row where percentages.qty is 0 and price.qty is 50, if there is a different percentage for a qty higher than 50.

• sql
• sql-server

Share Improve this question Follow edited Nov 22, 2024 at 9:02 Davide asked Nov 18, 2024 at 15:40 DavideDavide 1144 bronze badges 11

• Please provide a Minimal Reproducible Example For questions regarding sql this typically involves, table structure, example data and queries as TEXT and not as image. Additionally to providing these informations within the question, you are also welcome to create a runable fiddle of your problem. Finally, please also tag the respective DBMS you are using ... – derpirscher Commented Nov 18, 2024 at 15:46
• Here is a fiddle to help us collaborate on a solution dbfiddle.uk/2SxOdvL5 – Bart McEndree Commented Nov 18, 2024 at 15:50
• @BartMcEndree What in the question tells you OP is using MS SQL Server? Besides that, I'm not even sure what OP is actually asking ... – derpirscher Commented Nov 18, 2024 at 15:52
• 3 @BartMcEndree No, the correct way would be to ask for clarification, and vote/flag to close as "Needs details or clarity" or needing "Debugging details". – Mark Rotteveel Commented Nov 18, 2024 at 16:10
• 1 Please read this >15 year old article: bad habits to kick using old style joins – Luuk Commented Nov 20, 2024 at 11:48

 |  Show 6 more comments

1 Answer 1

Sorted by: Reset to default 0

Your description is still confusing. However, your expected results omit only one candidate row from a join between the two tables on customer. If the qty values for each of the two tables are converted to quantity ranges, that omitted row seems to be the only row where the ranges do not overlap.

Based on that, I would propose the following logic:

1. Calculate quantity ranges for each row of both tables. The original qty becomes the lower bound and LEAD(qty) - 1 becomes the upper bound. (We can use a closed interval, since we are dealing with discrete integer values.) If LEAD(qty) - 1 is null (no value found) the upper end of the range is unbounded.
2. Join these intermediate results on customer.
3. Further limit the join to overlapping ranges. A standard test for overlapping closed intervals is lower1 <= upper2 AND lower2 <= upper1. To handle null (unbounded) upper range values, we can substitute a large value using ISNULL().

The result would be something like:

WITH PercentagesQtyRange AS (
    SELECT
        supplier, salesperson, customer, qty, [percent],
        qty AS QtyLower,
        LEAD(Qty) OVER(PARTITION BY supplier, salesperson, customer ORDER BY qty)
            - 1 AS QtyUpper
    FROM percentages
),
PricesQtyRange AS (
    SELECT
        customer, article, qty, price, qty AS QtyLower,
        LEAD(Qty) OVER(PARTITION BY customer, article ORDER BY qty)
            - 1 AS QtyUpper
    FROM prices
)
SELECT *,
    GREATEST(PER.QtyLower, PR.QtyLower) AS OverlapLower,
    LEAST(PER.QtyUpper, PR.QtyUpper) AS OverlapUpper
FROM PercentagesQtyRange PER
JOIN PricesQtyRange PR
    ON PR.customer = PER.customer
    AND PR.QtyLower <= ISNULL(PER.QtyUpper, 999999999)  -- or ISNULL(..., PR.QtyLower)
    AND PER.QtyLower <= ISNULL(PR.QtyUpper, 999999999)  -- or ISNULL(..., PER.QtyLower)
ORDER BY
    PER.customer, PER.supplier, PER.salesperson, PR.article,
    OverlapLower

LEAD() function is a SQL window function. Window functions allow access to and calculations on values from other rows in the results. using OVER(...) to define the scope of the rows that they access without using a GROUP BY. Typically they are aggregation functions like COUNT(), SUM() or MAX(), but may also be any of LEAD(), LAG(), FIRST_VALUE(), and LAST_VALUE() that are defined specifically as a window function. The OVER(...) clause defines the scope of the rows being accessed. The PARTITION BY specifies the logical grouping of data, and limits the window function to just one group of rows. The ORDER BY defines an ordering within each partition, for those functions (like LEAD()) that depend on order. An OVER() clause may also contain a ROWS ... or RANGE ... clause to include multiple rows in the window function calculation, but we don't need that for the LEAD() function. Wee only need to access one value on one following row.

The CTE calculated ranges are as follows:

supplier	salesperson	customer	qty	percent	QtyLower	QtyUpper
John	Anne	David	0	0.25	0	19
John	Anne	David	20	0.5	20	null
Paul	Andrew	David	0	0.25	0	null
John	Anne	Mary	0	0.25	0	null

customer	article	qty	price	QtyLower	QtyUpper
David	X	0	50	0	49
David	X	50	40	50	null
David	Y	0	50	0	null
Mary	X	0	50	0	null
Mary	Y	0	55	0	null

Final Results:

supplier	salesperson	customer	qty	percent	QtyLower	QtyUpper	customer	article	qty	price	QtyLower	QtyUpper	OverlapLower	OverlapUpper
John	Anne	David	0	0.25	0	19	David	X	0	50	0	49	0	19
John	Anne	David	20	0.5	20	null	David	X	0	50	0	49	20	49
John	Anne	David	20	0.5	20	null	David	X	50	40	50	null	50	null
John	Anne	David	0	0.25	0	19	David	Y	0	50	0	null	0	19
John	Anne	David	20	0.5	20	null	David	Y	0	50	0	null	20	null
Paul	Andrew	David	0	0.25	0	null	David	X	0	50	0	49	0	49
Paul	Andrew	David	0	0.25	0	null	David	X	50	40	50	null	50	null
Paul	Andrew	David	0	0.25	0	null	David	Y	0	50	0	null	0	null
John	Anne	Mary	0	0.25	0	null	Mary	X	0	50	0	null	0	null
John	Anne	Mary	0	0.25	0	null	Mary	Y	0	55	0	null	0	null

Omitted row (customer matches, but ranges do not overlap):

supplier	salesperson	customer	qty	percent	QtyLower	QtyUpper	customer	article	qty	price	QtyLower	QtyUpper	OverlapLower	OverlapUpper
John	Anne	David	0	0.25	0	19	David	X	50	40	50	null	50	19

See this db<>fiddle for a demo.

The overlap range calculations in the result are for information only, but they do make use of the LEAST() and GREATEST() functions introduced in SQL Server 2022. For earlier SQL Server versions, you can use an IIF() equivalent together with COALESCE() to handle null upper bound values.

    IIF(PER.QtyLower > PR.QtyLower, PER.QtyLower, PR.QtyLower) AS OverlapLower,
    COALESCE(IIF(PER.QtyUpper < PR.QtyUpper, PER.QtyUpper, PR.QtyUpper), PER.QtyUpper, PR.QtyUpper) AS OverlapUpper

See this db<>fiddle.

本文标签： sql serverSQL query finding percentages and pricesStack Overflow