查询以显示交易表中的已用积分 [英] Query to display spent credits from transactional table

问题描述

我正在处理一个包含信用交易的表，我想在其中显示在进行销售时花费了谁的信用.

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.

在表中:

• Credits 由实体使用唯一实体代码添加(记录在 GivenByUserCode 列中)
• 信用添加总是有这样的代码.
• 花费的积分将始终为负值.
• 花费的积分不会有实体代码(GivenByUserCode 的值为 null).

• 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).

以上述数据为例，如果用户在 2018-01-02 进行购买，报告应显示所有这些来自 BM01 的积​​分.增加的复杂性是一次购买可以分为多次添加，请参阅 2018-02-03 上的购买，分为 3 次添加.

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.

我认为该解决方案与使用 cte 和 over 但我没有使用这些的经验.我确实在 SqlServerCentral 上发现了一个类似(不一样)的问题.

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.

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)

表格形式输入

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

预期输出

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)

产生输出

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

<小时>

补充说明

预期输出是针对这些积分来自的每个花费的积分金额.积分以先进先出 (FIFO) 的方式使用.此处对示例输出中的每个值进行了说明，希望能阐明所需的输出.

---

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.

• 对于消费 10 个信用(信用日志 ID 3)可以追溯到信用日志 ID 1 的添加
• 对于 5 个信用的消费(信用日志 ID 4)可以追溯到信用日志 ID 2 的添加(因为信用日志 ID 1 已用完")
• 在信用记录 id 7 中消费 40 个信用点可以追溯到
  • 从信用记录 id 2 中添加的剩余部分，5 个信用
  • 信用记录 ID 5(5 的加法)
  • 信用记录 ID 6(加上 40，所以剩余 10)

  请注意，总余额为 6 个积分，余额不必归零，但永远不会为负数，因为用户只能花掉他们拥有的东西.

  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.

  推荐答案

  试试这个:

  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
  

  这篇关于查询以显示交易表中的已用积分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！