当“余额”字段上次从负值变为正值时，如何通过PersonID返回日期？
在下面的示例数据中，对于PersonID 1，它发生在2019年7月8日，对于PersonID 2，它发生在2019年8月8日。值可能从负变为正，可能有很多倍，但仅应引用它的最新时间。
预期产量：
我有以下示例数据
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
最佳答案
这可以使用not exists完成您想要的操作。它找到最后一个负余额：
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
);
如果要进行最后更改，可以使用窗口函数进行过滤：
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;