时间戳

首页 > 解决方案 > MS SQL FIFO 部分传输

sql - MS SQL FIFO 部分传输

问题描述

我有许多将库存从一个帐户转移到另一个帐户的交易。我可以转移所有库存，也可以转移部分库存。

我需要在我的佣金日期向库存所在账户的所有者支付佣金。

我的报告需要显示库存项目的原始来源（如果它们已转移）并提供我可以从中计算佣金的 unit_balance。

示例交易：

Account 100
Account, trxid, transacted_units, transactiontype, transferfrom, transferto, date
100, 1, 100, buy, NULL, NULL, 1/1/2020
100, 2, 50, transfer in, 200, NULL, 1/2/2020

账户 200
账户、trxid、transacted_units、transactiontype、transferfrom、transferto、date
200、3、40
、buy、NULL、NULL、12/1/2019 200、4、30、buy、NULL、NULL、12/2/2019
200 , 5, 7, 卖出, NULL, NULL, 12/3/2019
200, 6, 50, 转出, NULL, 100, 1/2/2020

我的报告输出需要显示与与 unit_balance 相关的库存关联的帐户的完整详细信息

报告输出：
[level]、Account、trxid、parenttrxid、transacted_units、transactiontype、transferfrom、transferto、date、units_balance 0、100、1 、NULL 、
100、buy、NULL、NULL、1/1/2020、100 0、100
, 2, NULL, 50, 转入, 200, NULL, 1/2/2020, NULL
1, 200, 3, 2, 40, 买, NULL, NULL, 12/1/2019, 33
1, 200, 4, 2, 30, 买入, NULL, NULL, 12/2/2019, 17
1, 200, 5, 2, 7, 卖出, NULL, NULL, 12/3/2019, 0
1, 200, 6, 2, 50,转出, NULL, 100, 1/2/2020, 0

*FIFO 逻辑将售出的 7 个单位应用于账户 200 的第一次购买。然后转出应计算剩余合格交易的 units_balance。

我今天的 SQL 代码仅在我转出全部库存金额时有效，而不是部分转出：

    select
        [level],
        parentid,
        trxid,
        account,
        transactiontype,
        date,
        rnk,
        transacted_units,
        cumulative,
        CASE 
            WHEN cumulative>0 and transacted_units>=cumulative THEN cumulative
            WHEN cumulative>0 and transacted_units<cumulative THEN transacted_units 
            ELSE 0 
        END units_bal
    from (
        select
            *, 
            sum(transacted_units*Positive_Negative_Indicator) over (partition by parenttrxid, account order by rnk, date, trxid RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) cumulative
        from (

            select *,       
                CASE 
                    WHEN transacted_units*Positive_Negative_Indicator < 0 THEN 0 
                    ELSE ROW_NUMBER() OVER (PARTITION BY parenttrxid, account ORDER BY Positive_Negative_Indicator ASC, date ASC, trxid ASC) 
                END rnk
            from Transactions

        ) a
    ) a

positive_negative_indicator 字段表示交易的方向。卖出或转出是负面的，而其他都是正面的。

标签： sqlsql-serverfifo

解决方案

对于每个当前的“in”交易，计算之前“in”交易的总（单位）。然后分配先前“输入”事务未消耗的尽可能多的“输出”单元（尽可能多的“输出”单元==当前“输入”事务可以消耗的“输出”单元的运行总数） .

declare @t table
(
    Account int, 
    trxid int,
    trunits int,
    trtype varchar(20),
    transfrom int,
    transto int,
    thedate date
);

insert into @t(Account, trxid, trunits, trtype, transfrom, transto, thedate)
values
(100, 1, 100, 'buy', NULL, NULL, '20200101'),
(100, 2, 50, 'transfer in', 200, NULL, '20200201'),
(200, 3, 40, 'buy', NULL, NULL, '20190112'),
(200, 4, 30, 'buy', NULL, NULL, '20190213'),
(200, 5, 10, 'buy', NULL, NULL, '20190214'),
(200, 6, 7, 'sell', NULL, NULL, '20190315'),
(200, 7, 9, 'sell', NULL, NULL, '20190316'),
(200, 8, 25, 'buy', NULL, NULL, '20190317'),
(200, 9, 39, 'sell', NULL, NULL, '20190318'),
(200, 10, 18, 'sell', NULL, NULL, '20190319'),
(200, 11, 14, 'sell', NULL, NULL, '20190320'),
(200, 11, 50, 'transfer out', NULL, 100, '20200201');



select *, case when t.trtype not in ('sell', 'transfer out') then t.trunits -isnull(otu.out_units, 0) else null end as leftover_units
from 
(
select *, sum(case when trtype not in ('sell', 'transfer out') then trunits else 0 end) over (partition by Account order by thedate rows between unbounded preceding and 1 preceding) as previous_in_running_units
from @t
) as t
outer apply
(
    select top (1) ort.out_units_running_total - isnull(t.previous_in_running_units, 0) as out_units
    from
    (
        select sum(o.trunits) over(order by o.thedate) as out_units_running_total
        from @t as o
        where o.trtype in ('sell', 'transfer out')
        and o.Account = t.Account
        and t.trtype not in ('sell', 'transfer out') --no calculations needed when cross applying for "out" transactions
    ) as ort --out running totals
    where ort.out_units_running_total-isnull(t.previous_in_running_units, 0) <= t.trunits --<-- ("in") use as many out units as can be consumed by current t.transaction/date after deducting what has been consumed by the previous t.transaction/date
    and ort.out_units_running_total-isnull(t.previous_in_running_units, 0) > 0 --not needed(?) if balance is guaranteed.. total_out = total_in 
    order by ort.out_units_running_total desc 
) as otu; --"out units"

推荐阅读