检索历史表中记录的最近实例

Question

How Results Currrently Look Like:
Id:	  EmpName:	    Date:		             Status:
1234	John Doe	    2018-12-20 02:00:13.000	     Ready To Work
1234	Mary Joe	    2018-12-20 02:00:13.000	     On Hold
1234	Mary Joe	    2019-01-12 15:21:08.000          Delayed
1234	John Doe	    2019-01-28 16:32:25.000	     Submitted
1234	John Doe	    2019-01-28 16:33:01.000	     Delayed
1234	John Doe    	    2019-01-28 16:33:11.000	     Submitted
1234	Mary Ann            2019-02-11 12:23:49.000          Completed
How I Want Results to Look Like:
Id:	EmployeeName:	Date:		           Status:
1234	John Doe	2018-12-20 02:00:13.000	   Ready To Work
1234	Mary Joe	2018-12-20 02:00:13.000	   On Hold
1234	Mary Joe	2019-01-12 15:21:08.000    Delayed
1234	John Doe	2019-01-28 16:33:11.000	   Submitted
1234	Mary Ann	2019-02-11 12:23:49.000    Completed

大家好

我正在尝试在历史记录表中创建一些SQL逻辑，如果状态在同一天为"已提交"和"延迟"，它将获取任何ID，那么我想要它的最新实例。如下面的代码所示：1 月 28 日会弹出 3 行，该 ID 正在提交、延迟，然后再次提交。我只想在表中显示提交的最后一个实例以及 ID 与之关联的其余历史记录。我正在尝试创建将其应用于表的其余部分和所有其他 Id 的逻辑。

我尝试创建一个 TwentyFourHourInterval列，并创建了逻辑，该逻辑指出 Date 何时介于日期和 TwentyFourHourInterval THEN MAX(Date( 之间。但这只是拉出最大日期，这不是我想要的。

Answer 1

使用 row_number((

with cte as
(
select *,row_number() over(partition by EmpName,cast (date as date) order by date) rn
from tablename
) select * from cte where rn=1

Answer 2

那个应该给你你想要的。

drop table if exists #t;
create table #t (Id int, EmpName varchar(100), Date datetime, Status varchar(100));
insert into #t values
(1234,  'John Doe',     '2018-12-20 02:00:13.000' ,   'Ready To Work'),
(1234,  'Mary Joe',     '2018-12-20 02:00:13.000'   ,     'On Hold'),
(1234,  'Mary Joe',     '2019-01-12 15:21:08.000'   ,      'Delayed'),
(1234,  'John Doe',     '2019-01-28 16:32:25.000'   ,     'Submitted'),
(1234,  'John Doe',     '2019-01-28 16:33:01.000'   ,     'Delayed'),
(1234,  'John Doe',     '2019-01-28 16:33:11.000'   ,     'Submitted'),
(1234,  'Mary Ann',     '2019-02-11 12:23:49.000'   ,       'Completed');
with t as (
select #t.*,
case when Status in ('Submitted', 'Delayed') then 1 else 0 end as SubDelFlag,
ROW_NUMBER() over (partition by id, cast(Date as date) 
order by case when Status in ('Submitted', 'Delayed') then 1 else 0 end, Date desc) as SubDelRow
from #t)
select Id, EmpName, Date, Status
from t
where SubDelFlag=0 or (SubDelFlag=1 and SubDelRow=1)

Answer 3

只是not exists怎么样：

select t.*
from t
where t.status not in ('Submitted', 'Delayed') or
t.date = (select max(t2.date)
from t t2
where t2.id = t.id and
convert(date, t2.date) = convert(date, t.date) and
t2.status in ('Submitted', 'Delayed')
);

你可以用row_number()做到这一点，尽管我不确定它在任何意义上都更简单：

select t.*
from (select t.*,
row_number() over (partition by id, convert(date, date), (case when status in ('Submitted', 'Delayed') then 1 else 2 end)
order by date desc
) as seqnum
from t
) t
where t.status not in ('Submitted', 'Delayed') or
t.seqnum = 1;