問題描述

如何通過 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;

這篇關于上次值由負變為正的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！