也许您可以使用以下解决方案
**[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 GetValidCustomerRecordsorder by CustName, RecordedTime
