上次值由负变为正

2021-09-10 00:00:00 sql tsql sql-server

如何通过 PersonID 返回上次余额字段从负值变为正值的日期?

How can you return the date by PersonID when the last time the Balance field went from a negative value to a positive value?

在下面的示例数据中，PersonID 1 发生在 2019 年 7 月 8 日，PersonID 2 发生在 2019 年 8 月 8 日.值可以多次从负数变为正数，但应仅引用最近一次发生.

In the sample data below, for PersonID 1 it happened for 8th July 2019, for PersonID 2 it happened for 8th August 2019. There can be multiple times the value changes from negative to positive but it should only reference the latest time it happens.

预期输出:

我有以下示例数据

Create Table #temp
(
    PersonID int,
    ActionDate date,
    Balance money
)
insert into #temp
(
    PersonID,
    ActionDate,
    Balance
)
select
    1,
    '01 Jul 2019',
    -100
union all
select
    1,
    '02 Jul 2019',
    -45
union all
select
    1,
    '03 Jul 2019',
    -80
union all
select
    1,
    '04 Jul 2019',
    -20
union all
select
    1,
    '05 Jul 2019',
    40
union all
select
    1,
    '06 Jul 2019',
    -40
union all
select
    1,
    '07 Jul 2019',
    -90
union all
select
    1,
    '08 Jul 2019',
    -150
union all
select
    1,
    '09 Jul 2019',
    100
union all
select
    1,
    '10 Jul 2019',
    120
union all
select
    1,
    '11 Jul 2019',
    130
union all
select
    1,
    '12 Jul 2019',
    140
--
union all
select
    2,
    '01 Aug 2019',
    -100
union all
select
    2,
    '02 Aug 2019',
    -45
union all
select
    2,
    '03 Aug 2019',
    80
union all
select
    2,
    '04 Aug 2019',
    20
union all
select
    2,
    '05 Aug 2019',
    -40
union all
select
    2,
    '06 Aug 2019',
    -40
union all
select
    2,
    '07 Aug 2019',
    40
union all
select
    2,
    '08 Aug 2019',
    -40
union all
select
    2,
    '09 Aug 2019',
    45
union all
select
    2,
    '10 Aug 2019',
    65
union all
select
    2,
    '11 Aug 2019',
    23
union all
select
    2,
    '12 Aug 2019',
    105

推荐答案

使用 notexists 这可能会做你想做的事.它找到最后一个负余额:

This might do what you want using not exists. It finds the last negative balance:

select t.*
from #temp t
where t.balance < 0 and
      not exists (select 1
                  from #temp t2
                  where t2.personid = t.personid and
                        t2.actiondate > t.actiondate and
                        t2.balance < 0
                 );

如果你想要最后的变化，你可以使用窗口函数过滤:

If you want the last change, you can filter using window functions:

select t.*
from (select t.*,
             row_number() over (partition by personid order by actiondate desc) as seqnum
      from (select t.*,
                   lead(balance) over (partition by personid order by actiondate) as next_balance
            from #temp t
           ) t
      where t.balance < 0 and
            t.next_balance > 0
     ) t
where seqnum = 1;

相关文章