Which of these units should be free? Pricing every second item requires an ordered list of individual units inside each basket. SQL Server can number those units, identify the discounted positions, and return a total you can explain line by line.
Agree on the Every Second Item Rule First
The business rule in this example pairs items from highest price to lowest price. The cheaper item in each pair becomes free. An unpaired final item remains payable. Other pairing rules produce different totals, so agree on the rule before writing the query.
I ask for worked examples before touching a discount calculation. Include an odd basket, equal prices, separate product groups, and a line containing multiple units. The phrase buy one get one does not settle all those details by itself. The receipt printer cannot attend your requirements meeting.
Prices here are synthetic inputs, not measured results. Store money with a deliberate decimal precision and scale. Avoid approximate floating-point types for amounts that must reconcile. Also decide whether pairing occurs before or after other discounts. A calculation using list price and a calculation using the already-discounted unit price answer different questions.
Pricing every second item as free requires a stable rule for equal prices, returns, and incomplete pairs.
Expand Quantity Into Individual Units
ROW_NUMBER numbers rows, not the units hidden inside a quantity column. A line with Quantity equal to three needs three positions in the pairing sequence. SQL Server 2022 provides GENERATE_SERIES for that expansion at database compatibility level 160 or higher. On older supported versions, join to a permanent tally table containing positive integers.
The temporary tables make this example self-contained in one SSMS connection. Each unit retains its basket, group, source line, and unit position. Those identifiers form the tie breakers when prices match. Keep them through the discount calculation so the result can be traced back to a receipt line.
Reject zero or negative quantities before expansion unless returns have their own explicit rule. Do not let a negative quantity become an unexpected descending series. This example sets a positive step and filters positive quantities. Real checkout validation should reject invalid lines rather than quietly losing them.
CREATE TABLE #BasketLines
(BasketID int, LineID int, ProductGroup int, Price decimal(12,2), Quantity int);
INSERT #BasketLines VALUES
(1,1,10,30.00,1),(1,2,10,20.00,2),(1,3,20,12.00,1);
SELECT l.BasketID,l.LineID,l.ProductGroup,l.Price,
g.value AS UnitPosition
INTO #BasketUnits
FROM #BasketLines AS l
CROSS APPLY GENERATE_SERIES(1,l.Quantity,1) AS g
WHERE l.Quantity > 0;Number the Basket and Mark Every Second Item
Descending price order places the expensive item before the cheaper item in each pair. Even positions receive the discount. The modulo operator supplies that position check without a procedural loop. Partitioning by BasketID prevents a unit in one basket from pairing with another basket's unit.
Equal prices need deterministic tie breakers. Price alone produces the same total discount for equal values, but the identity of the free unit becomes unstable. That matters when refunds, tax, or reporting refer to a particular line. Include LineID and UnitPosition after Price.
What should happen to the last unit in an odd basket? In this rule, its odd position makes it payable. Show the line-level output before reducing everything to one total. I review the numbered sequence first because an attractive total can hide incorrect pairing. The sequence is the explanation your support team will eventually need.
WITH Ranked AS
(
SELECT *,ROW_NUMBER() OVER
(PARTITION BY BasketID ORDER BY Price DESC,LineID,UnitPosition) AS UnitNumber
FROM #BasketUnits
)
SELECT BasketID,LineID,UnitPosition,Price,UnitNumber,
CASE WHEN UnitNumber % 2 = 0 THEN Price ELSE 0 END AS DiscountAmount,
CASE WHEN UnitNumber % 2 = 0 THEN 0 ELSE Price END AS PayableAmount
FROM Ranked
ORDER BY BasketID,UnitNumber;
Total the Discount and Change the Cycle
To calculate a basket total, aggregate the discount amounts and original unit prices over the same ranked data. Subtract the discount from the original total, or sum the payable amount directly. Compare those two expressions during testing to catch mismatched filters.
A buy-two-get-one rule changes the free position to every third item. Keep descending order so the third item is the cheapest member of its group. The modulo test becomes UnitNumber % 3 equal to zero. An incomplete final group receives no free item under this rule.
The next query returns both discount alternatives for comparison. It does not apply both promotions together. A production calculation must choose the applicable promotion. Keep that choice explicit in the data or procedure rather than stacking two discount totals accidentally. Rounding belongs at the agreed business boundary, especially if later rules introduce percentages or tax calculations.
WITH Ranked AS
(
SELECT *,ROW_NUMBER() OVER
(PARTITION BY BasketID ORDER BY Price DESC,LineID,UnitPosition) AS UnitNumber
FROM #BasketUnits
)
SELECT BasketID,SUM(Price) AS OriginalAmount,
SUM(CASE WHEN UnitNumber % 2 = 0 THEN Price ELSE 0 END) AS PairDiscount,
SUM(CASE WHEN UnitNumber % 3 = 0 THEN Price ELSE 0 END) AS TripleDiscount
FROM Ranked
GROUP BY BasketID;Keep Product Groups From Sharing a Pair
If eligibility is limited to one product group, add ProductGroup to the PARTITION BY clause. The numbering then restarts within each basket and eligible group. A unit from another group cannot complete the pair merely because its price is convenient.
Be precise about the phrase one offer per group. Partitioning restarts the calculation but still applies the promotion repeatedly within that group. If the rule allows only one free item per group, also restrict the qualifying position to UnitNumber equal to two. Those are separate policies.
Keep ineligible products out of the ranked set, then combine their payable amounts with the discounted units afterward. Filtering eligibility after numbering shifts the positions and produces incorrect discounts. Save the promotion identifier with the calculation when historical receipts must remain reproducible. Future rule changes should not recalculate an old receipt under today's settings.
Test Refunds and Reconciliation Explicitly
The unit expansion grows with total quantity. Large bulk baskets deserve realistic tests and an appropriate tally-table design on older versions. The ranking needs a sort unless an existing access path provides the required order. Read the plan before optimizing a calculation that already returns the wrong units.
Test the rule's boundaries: no items, one item, an incomplete group, tied prices, and multiple baskets. Then verify that original amount equals payable amount plus discount for every basket. Also reconcile grouped totals with the original receipt lines.
Refunds require a separate policy. Removing an item can change which remaining unit would have been free, but the original sale must remain auditable. Store the original allocation when the business needs that trace. Number individual units, keep the pairing rule explicit, and let every second item become free only within the basket and eligibility rules you approved.
Related reading on this blog: What’s the Difference between ROW_NUMBER, RANK, and DENSE_RANK? Notes from the Field #096 and Adding Values WITH OVER and PARTITION BY.
A basket discount is not a slogan, it is an ordering rule applied to units.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
