也许您可以使用以下解决方案 **[See working demo](http://rextester.com/KPPS98807)**
declare @d date='2018-Jun-03'
; with Indexer as
(
select
*,
rn= row_number() over(partition by CustName order by RecordedTime),
rn2=row_number() over(partition by CustName order by RecordedTime desc)
from records
)
,GetValidCustomerRecords as
(
select
CustName,
Country,
RecordedTime,
Audit = case when cast(RecordedTime as date)=@d and rn=1 then 'add' else 'change' end,
History = case
when cast(RecordedTime as date)=@d and rn=1
then 'new'
when cast(RecordedTime as date)<@d and rn=1
then 'before'
else 'current' end
from Indexer i
where CustName in
(
select
distinct CustName
from records
where cast(RecordedTime as date)=@d
)
and (rn=1 or rn2=1) and cast(RecordedTime as date)<=@d
)
select * from GetValidCustomerRecords
order by CustName, RecordedTime
602392714
清零编程群