Query to display spent credits from transactional table

Question

I am working with a table that contains credit transactions where I want to display who's credits were spent when a sale is made.

In the table:

• Credits are added by an entity using a unique entity code (recorded in column GivenByUserCode)
• Credit additions always have such a code.
• Credits that are spent will always have a negative value.
• Credits that are spent will not have an entity code (value of GivenByUserCode is null).

Using the above data as an example if a user makes a purchase on 2018-01-02 the report should show all these credits originated from BM01. What add's complexity is that a purchase could be split over multiple additions, see the purchase on 2018-02-03 which is divided over 3 additions.

I think the solution will have something to do with using cte and over but I have no experience using these. I did find a similar (not same) problem on SqlServerCentral.

Any help / direction would be most appreciated.

---

Input and DDL

DECLARE @CreditLogs TABLE(CreditLogId int not null identity(1,1), Credits INT NOT NULL, OccurredOn DATETIME2(7) NOT NULL, GivenByUserCode VARCHAR(100) NULL)

INSERT INTO @CreditLogs (Credits, OccurredOn, GivenByUserCode) VALUES
  (10,  '2018-01-01', 'BM01')
, (10,  '2018-01-01', 'BM01')
, (-10, '2018-01-02', NULL)
, (-5,  '2018-01-04', NULL)
, (5,   '2018-02-01', 'SP99')
, (40,  '2018-02-02', 'BM02')
, (-40, '2018-02-03', NULL)
, (-4,  '2018-03-05', NULL)

Input in table form

CreditLogId | Credits | OccurredOn | GivenByUserCode
------------+---------+------------+----------------
          1 |      10 | 2018-01-01 |            BM01
          2 |      10 | 2018-01-01 |            BM01
          3 |     -10 | 2018-01-02 |            NULL
          4 |      -5 | 2018-01-04 |            NULL
          5 |       5 | 2018-02-01 |            SP99
          6 |      40 | 2018-02-02 |            BM02
          7 |     -40 | 2018-02-03 |            NULL
          8 |      -4 | 2018-03-05 |            NULL

Expected output

SELECT *
FROM (VALUES
     (3, '2018-01-02', 10, 'BM01')
    ,(4, '2018-01-04', 5, 'BM01')
    ,(7, '2018-02-03', 5, 'BM01')
    ,(7, '2018-02-03', 5, 'SP99')
    ,(7, '2018-02-03', 30, 'BM02')
    ,(8, '2018-03-05', 4, 'BM02')
) expectedOut (CreditLogId, OccurredOn, Credits, GivenByUserCode)

Produces output

CreditLogId | Occurred on | Credits | GivenByUserCode
------------+-------------+---------+----------------
          3 |  2018-01-02 |      10 |            BM01
          4 |  2018-01-04 |       5 |            BM01
          7 |  2018-02-03 |       5 |            BM01
          7 |  2018-02-03 |       5 |            SP99
          7 |  2018-02-03 |      30 |            BM02
          8 |  2018-03-05 |       4 |            BM02

Code so far

It's not much and I am not sure where to go from here.

WITH totals AS (
    SELECT CreditLogId, OccurredOn, credits, sum(credits) OVER(ORDER BY OccurredOn) AS TotalSpent
    FROM @CreditLogs
    WHERE Credits < 0
)
SELECT *
FROM totals

---

Additional clarification

The expected output is for each spent credit amount where those credits came from. Credits are spent in on a first in first out (FIFO) basis. Here an explanation of each value in the sample output in the hope that this clarifies the desired output.

• For the spending of 10 credits (credit log id 3) can be traced back to an addition from credit log id 1
• For the spending of 5 credits (credit log id 4) can be traced back to an addition from credit log id 2 (as credit log id 1 was "used up")
• For the spending of 40 credits in credit log id 7 can be traced back to
  • Remainder of addition from credit log id 2, 5 credits
  • Credit log id 5 (addition of 5)
  • Credit log id 6 (addition of 40 so 10 remaining)
• For the spending of 4 credits in credit log 8 the balance of credit log id 6 is used

Note that a total balance of 6 credits remains, the balance does not have to zero out but will never be in the negative as users can only spend what they have.

Answer 1

try this:

WITH Credits_added AS (
    SELECT CreditLogId, OccurredOn, credits
    , SUM(credits) OVER (ORDER BY CreditLogId) - credits AS b --before
    , SUM(credits) OVER (ORDER BY CreditLogId) AS a --after
    , GivenByUserCode
    FROM @CreditLogs
    WHERE Credits > 0)
, Credits_spent AS (
    SELECT CreditLogId, OccurredOn, credits
    , SUM(credits) OVER (ORDER BY CreditLogId) * -1 + credits AS b
    , SUM(credits) OVER (ORDER BY CreditLogId) * -1 AS a
    FROM @CreditLogs
    WHERE Credits < 0)
SELECT s.CreditLogId, s.OccurredOn
, CASE WHEN a.a > s.a THEN s.a ELSE a.a END - CASE WHEN a.b > s.b THEN a.b ELSE s.b END AS Credits 
, a.GivenByUserCode
FROM Credits_added AS a
INNER JOIN Credits_spent AS s ON a.a > s.b AND s.a > a.b