Geek Answers Handbook

Rows of pairs without overlap

I have items from an order, some of which are on Buy one to receive one free offer. The business logic for this is:

  • Cheapest item pair free
  • Best offer for customer selected
  • Once an item has been used in a proposal, it can no longer be used.

Here is an example of some positions ( OrderItemID) that have been combined with all their offers.

╔═════════════╦═════════╦═══════╗
β•‘ OrderItemID β•‘ OfferID β•‘ Value β•‘
╠═════════════╬═════════╬═══════╣
β•‘ 1           β•‘ 1       β•‘ 22.50 β•‘
β•‘ 2           β•‘ 1       β•‘ 25.00 β•‘
β•‘ 3           β•‘ 2       β•‘ 14.99 β•‘
β•‘ 4           β•‘ 1       β•‘ 5.00  β•‘
β•‘ 5           β•‘ 1       β•‘ 7.00  β•‘
β•‘ 5           β•‘ 2       β•‘ 7.00  β•‘
β•‘ 5           β•‘ 3       β•‘ 7.00  β•‘
β•‘ 6           β•‘ 3       β•‘ 7.50  β•‘
β•šβ•β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•

From this, I can get a complete set of valid combinations using the above rules, ordered by saving (i.e. the price of the second element):

╔═════════════════╦═════════════════╦═════════╦═══════╦════════╗
β•‘ PaidOrderItemID β•‘ FreeOrderItemID β•‘ OfferID β•‘ Price β•‘ Saving β•‘
╠═════════════════╬═════════════════╬═════════╬═══════╬════════╣
β•‘ 2               β•‘ 1               β•‘ 1       β•‘ 25.00 β•‘ 22.50  β•‘
β•‘ 1               β•‘ 5               β•‘ 1       β•‘ 22.50 β•‘ 7.00   β•‘
β•‘ 2               β•‘ 5               β•‘ 1       β•‘ 25.00 β•‘ 7.00   β•‘
β•‘ 3               β•‘ 5               β•‘ 2       β•‘ 14.99 β•‘ 7.00   β•‘
β•‘ 6               β•‘ 5               β•‘ 3       β•‘ 7.50  β•‘ 7.00   β•‘
β•‘ 1               β•‘ 4               β•‘ 1       β•‘ 22.50 β•‘ 5.00   β•‘
β•‘ 2               β•‘ 4               β•‘ 1       β•‘ 25.00 β•‘ 5.00   β•‘
β•‘ 5               β•‘ 4               β•‘ 1       β•‘ 7.00  β•‘ 5.00   β•‘
β•šβ•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•

Now I want to extract combinations without overlapping order elements. The following table shows what I need

╔═════════════════╦═════════════════╦═════════╦═══════╦════════╗
β•‘ PaidOrderItemID β•‘ FreeOrderItemID β•‘ OfferID β•‘ Price β•‘ Saving β•‘
╠═════════════════╬═════════════════╬═════════╬═══════╬════════╣
β•‘ 2               β•‘ 1               β•‘ 1       β•‘ 25.00 β•‘ 22.50  β•‘
β•‘ 3               β•‘ 5               β•‘ 2       β•‘ 14.99 β•‘ 7.00   β•‘
β•šβ•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•

- . - , PaidOrderItemID FreeOrderItemID ItemID, .

, temp, , OrderItemID s, , .

, , , , ? CTE . SQL Server 2008 R2.

:

DECLARE @orderItemOffers TABLE (OrderItemID INT, OfferID INT, Value MONEY)
INSERT INTO @orderItemOffers
        (OrderItemID, OfferID, Value)
VALUES  (1, 1, 22.50),
        (2, 1, 25.00),
        (3, 2, 14.99),
        (4, 1, 5.00),
        (5, 1, 7.00),
        (5, 2, 7.00),
        (5, 3, 7.00),
        (6, 3, 7.50)

SELECT * FROM @orderItemOffers

DECLARE @offerCombos TABLE (
    PaidOrderItemID INT,
    FreeOrderItemID INT,
    OfferID INT,
    Price MONEY,
    Saving MONEY
)
INSERT INTO @offerCombos (
    PaidOrderItemID,
    FreeOrderItemID,
    OfferID,
    Price,
    Saving
)
SELECT PaidItem.OrderItemID,
       FreeItem.OrderItemID,
       FreeItem.OfferID,
       PaidItem.Value,
       FreeItem.Value
FROM @orderItemOffers PaidItem
INNER JOIN @orderItemOffers FreeItem
    ON FreeItem.OrderItemID <> PaidItem.OrderItemID
       AND FreeItem.OfferID = PaidItem.OfferID
       AND (FreeItem.Value < PaidItem.Value 
            OR (FreeItem.Value = PaidItem.Value
                AND FreeItem.OrderItemID > PaidItem.OrderItemID
               )
           )
ORDER BY FreeItem.Value DESC

SELECT * FROM @offerCombos

DECLARE @takenOffers TABLE (
    PaidOrderItemID INT,
    FreeOrderItemID INT,
    OfferID INT,
    Price MONEY,
    Saving MONEY
)
DECLARE offerCursor CURSOR FAST_FORWARD FOR SELECT * FROM @offerCombos
OPEN offerCursor

DECLARE @PaidOrderItemID INT, @FreeOrderItemID INT,
        @OfferID INT, @Price MONEY, @Saving MONEY
FETCH NEXT FROM offerCursor
INTO @PaidOrderItemID, @FreeOrderItemID, @OfferID, @Price, @Saving

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @takenOffers
           (PaidOrderItemID, FreeOrderItemID, OfferID, Price, Saving)
    VALUES (@PaidOrderItemID, @FreeOrderItemID, @OfferID, @Price, @Saving)

    DELETE FROM @offerCombos
    WHERE PaidOrderItemID IN (@PaidOrderItemID, @FreeOrderItemID)
       OR FreeOrderItemID IN (@PaidOrderItemID, @FreeOrderItemID)

    FETCH NEXT FROM offerCursor
    INTO @PaidOrderItemID, @FreeOrderItemID, @OfferID, @Price, @Saving
END 

CLOSE offerCursor
DEALLOCATE offerCursor

SELECT * FROM @takenOffers
+4
2

-

WITH Offer AS (
SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY OfferID ORDER BY Value DESC)
FROM @orderItemOffers
)
SELECT 
    o1.OrderItemID AS PaidOrderItemID 
    ,o2.OrderItemID AS FreeOrderItemID
    ,o1.OfferID
    ,o1.Value AS Price
    , o2.Value as Saving
FROM Offer o1
LEFT JOIN Offer o2 
    ON o1.OfferID = o2.OfferID
    AND o1.rn = o2.rn -1
WHERE o1.rn % 2 = 1
ORDER BY OfferID,PaidOrderItemID

sql

0

?

SELECT *, (ROW_NUMBER() OVER(PARTITION BY OfferID ORDER BY Value DESC, OrderItemID) - 1) % 2 IsFree
FROM @orderItemOffers

; , .

0

More articles:


All Articles