CODEWITHSUNDEEP
sqlsql-serveralgorithm
Dustin Kingen
Dustin Kingen

Reputation: 21255

Implementing debit-credit resolution system in SQL query

I'm trying to implement a debit-credit resolution system, but I am having a hard time expressing the logic based on sets.

Suppose I have a table of Orders:

Id    OrderId     Amount     AdjustmentFlag
 1       1           10.00        0
 2       1           10.00        1
 3       1           10.00        2
 4       2           20.00        1
 5       2           20.00        2
 6       2           20.00        2
 7       3           30.00        1
 8       4           40.00        0
 9       4           40.00        0
10       4           40.00        1
11       5           50.00        0
12       5           50.00        1
13       5           60.00        2
14       5           60.00        1
15       5           60.00        2
16       5           70.00        1

I need to pick out the Ids that are still valid based on if they have a matching "cancelled" flag.

0 - Original Order
1 - Cancelled Order
2 - Adjusted Order
  1. A 1 matches a 0 or a 2 with preference to 0.
  2. A 1 flag is ignored if they have no match.

Given the above example:

The possible outcomes would be [1, 2, 4, 5, 7, 8, 10, 11, 12, 13, 14, 16] (lower Id has preference) or [1, 2, 4, 6, 7, 9, 10, 11, 12, 14, 15, 16] (higher Id has preference). Either will work as long as the result is deterministic.

Create script:

CREATE TABLE [Order]
(
     Id INT IDENTITY NOT NULL PRIMARY KEY
    ,OrderId INT NOT NULL
    ,Amount MONEY NOT NULL
    ,AdjustmentFlag TINYINT NOT NULL
);

INSERT INTO [Order](OrderId, Amount, AdjustmentFlag)
SELECT 1, 10.00, 0
UNION ALL
SELECT 1, 10.00, 1
UNION ALL
SELECT 1, 10.00, 2
UNION ALL
SELECT 2, 20.00, 1
UNION ALL
SELECT 2, 20.00, 2
UNION ALL
SELECT 2, 20.00, 2
UNION ALL
SELECT 3, 30.00, 1
UNION ALL
SELECT 4, 40.00, 0
UNION ALL
SELECT 4, 40.00, 0
UNION ALL
SELECT 4, 40.00, 1
UNION ALL
SELECT 5, 50.00, 0
UNION ALL
SELECT 5, 50.00, 1
UNION ALL
SELECT 5, 60.00, 2
UNION ALL
SELECT 5, 60.00, 1
UNION ALL
SELECT 5, 60.00, 2
UNION ALL
SELECT 5, 70.00, 1

Here is my current partial solution:

WITH Orders AS
(
    SELECT
        Id,
        OrderId,
        Amount,
        AdjustmentFlag,
        EffectiveOrder = ROW_NUMBER() OVER (PARTITION BY OrderId, Amount ORDER BY AdjustmentFlag DESC),
        UnmatchedOrder = CASE WHEN EXISTS(SELECT 1 FROM [Order] uo WHERE uo.OrderId = o.OrderId GROUP BY uo.OrderId HAVING(COUNT(uo.OrderId) = 1)) THEN 1 ELSE 0 END,
        OriginalWithoutAdjustment = CASE WHEN EXISTS(SELECT 1 FROM [Order] uo WHERE uo.OrderId = o.OrderId AND uo.Amount = o.Amount GROUP BY uo.OrderId, uo.Amount HAVING (MAX(uo.AdjustmentFlag) = 1)) THEN 1 ELSE 0 END,
        AdjustmentWithoutOriginal = CASE WHEN EXISTS(SELECT 1 FROM [Order] uo WHERE uo.OrderId = o.OrderId AND uo.Amount = o.Amount GROUP BY uo.OrderId, uo.Amount HAVING (MIN(uo.AdjustmentFlag) = 1)) THEN 1 ELSE 0 END
    FROM [Order] o
)
,MatchedOrders AS
(
    SELECT
        Id
    FROM Orders
    WHERE
    -- Assume AdjustmentFlag = 2 and take everything else
    EffectiveOrder <> 1
    OR
    (
        -- Assume AdjustmentFlag = 2 and there is no Order with AdjustmentFlag = 0
        -- Take everything since the MIN AdjustmentFlag = 1
        AdjustmentWithoutOriginal = 1
        AND EffectiveOrder > 1
    )
    OR
    (
        -- Assume AdjustmentFlag = 1 and there are no other Orders, so ignore it
        AdjustmentFlag = 1
        AND UnmatchedOrder = 1
    )
    OR
    (
        -- We don't care about the orders if they don't have any Amount
        Amount = 0
        AND EffectiveOrder = 1
    )
    AND NOT
    (
        -- We have an Original without any other Orders
        EffectiveOrder = 1
        AND UnmatchedOrder = 1
        AND AdjustmentFlag = 0
    )
)
SELECT
    o.OrderId,
    o.AdjustmentFlag,
    o.Amount,
    o.EffectiveOrder,
    o.UnmatchedOrder,
    Excluded = CASE WHEN mo.Id IS NULL THEN 0 ELSE 1 END
FROM Orders o
LEFT OUTER JOIN MatchedOrders mo
ON o.Id = mo.Id
ORDER BY OrderId, Amount, AdjustmentFlag

Result:

Result

Upvotes: 1

Views: 857

Answers (1)

user359040
user359040

Reputation:

Try:

with cte as
(select o.*, 
        case AdjustmentFlag when 1 then -1 else 1 end DrCr,
        row_number() over (partition by OrderId, Amount, case AdjustmentFlag when 1 then 1 end
                           order by AdjustmentFlag, Id) Rn
 from [Order] o)
select OrderId,
       max(case DrCr when 1 then Id end) DrId,
       sum(case DrCr when 1 then Amount else 0 end) DrAmount,
       max(case DrCr when 1 then AdjustmentFlag end) DrAdjustmentFlag,
       max(case DrCr when -1 then Id end) CrId,
       sum(case DrCr when -1 then Amount else 0 end) CrAmount,
       max(case DrCr when -1 then AdjustmentFlag end) CrAdjustmentFlag,
       sum(DrCr * Amount) BalanceAmount
from cte
group by OrderId, Amount, Rn
having sum(DrCr * Amount) >= 0 /* excludes unmatched cancelled orders */

- If you only want to see unmatched Original/Amended orders, change the having clause condition to > 0.

SQLFiddle here.

Upvotes: 2

Related Questions