按多个时间段分组，然后求每个时段的最后一笔记录

Question

如下表:
需按时间段来查询出每个时段的最后一笔记录..
如何来建立一个查询语句 ? 请协助, 谢谢 !
时间段为: 01:00:00 ~ 07:00:00:00 , 07:00:01~12:00:00, 13:00:00~19:00:00, 19:00:01~23:59:59  (时段可自行变更) 
 
cpid name1 department section_group date_time checker_no wrist l_shoe r_shoe result
ID004 Berry PEDD 　 2012-1-12 1:03 F1-1-1 FAIL FAIL PASS PASS
ID004 Berry PEDD 　 2012-1-12 6:03 F1-1-1 FAIL FAIL PASS PASS
ID004 Berry PEDD 　 2012-1-12 7:58 F1-1-1 FAIL FAIL PASS FAIL
ID004 Berry PEDD 　 2012-1-12 9:58 F1-1-1 FAIL FAIL PASS FAIL
ID004 Berry PEDD 　 2012-1-12 15:58 F1-1-1 FAIL FAIL PASS FAIL
ID004 Berry PEDD 　 2012-1-12 16:05 F1-1-1 FAIL FAIL PASS FAIL
ID004 Berry PEDD 　 2012-1-12 19:03 F1-1-1 FAIL FAIL PASS FAIL
ID004 Berry PEDD 　 2012-1-12 23:03 F1-1-1 FAIL FAIL PASS FAIL
ID005 Joe PD 　 2012-1-12 1:03 F1-1-1 FAIL FAIL PASS PASS
ID005 Joe PD 　 2012-1-12 6:03 F1-1-1 FAIL FAIL PASS PASS
ID005 Joe PD 　 2012-1-12 7:58 F1-1-1 FAIL FAIL PASS FAIL
ID005 Joe PD 　 2012-1-12 9:58 F1-1-1 FAIL FAIL PASS FAIL
ID005 Joe PD 　 2012-1-12 15:58 F1-1-1 FAIL FAIL PASS FAIL
ID005 Joe PD 　 2012-1-12 16:05 F1-1-1 FAIL FAIL PASS FAIL
ID005 Joe PD 　 2012-1-12 19:03 F1-1-1 FAIL FAIL PASS FAIL
ID005 Joe PD 　 2012-1-12 23:03 F1-1-1 FAIL FAIL PASS FAIL
 
查询后的结果要如下:

ID004 Berry PEDD 　 2012-1-12 6:03 F1-1-1 FAIL FAIL PASS PASS
ID004 Berry PEDD 　 2012-1-12 9:58 F1-1-1 FAIL FAIL PASS FAIL
ID004 Berry PEDD 　 2012-1-12 16:05 F1-1-1 FAIL FAIL PASS FAIL
ID004 Berry PEDD 　 2012-1-12 23:03 F1-1-1 FAIL FAIL PASS FAIL
ID005 Joe PD 　 2012-1-12 6:03 F1-1-1 FAIL FAIL PASS PASS
ID005 Joe PD 　 2012-1-12 9:58 F1-1-1 FAIL FAIL PASS FAIL
ID005 Joe PD 　 2012-1-12 16:05 F1-1-1 FAIL FAIL PASS FAIL
ID005 Joe PD 　 2012-1-12 23:03 F1-1-1 FAIL FAIL PASS FAIL
 
 

Answer 1

if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([cpid] nvarchar(5),[name1] nvarchar(5),[department] nvarchar(4),[section_group] Datetime,[date_time] Datetime,[checker_no] nvarchar(6),[wrist] nvarchar(4),[l_shoe] nvarchar(4),[r_shoe] nvarchar(4),[result] nvarchar(4))
Insert #t
select N'ID004',N'Berry',N'PEDD','2012-1-12','1:03',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'PASS' union all
select N'ID004',N'Berry',N'PEDD','2012-1-12','6:03',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'PASS' union all
select N'ID004',N'Berry',N'PEDD','2012-1-12','7:58',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID004',N'Berry',N'PEDD','2012-1-12','9:58',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID004',N'Berry',N'PEDD','2012-1-12','15:58',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID004',N'Berry',N'PEDD','2012-1-12','16:05',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID004',N'Berry',N'PEDD','2012-1-12','19:03',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID004',N'Berry',N'PEDD','2012-1-12','23:03',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID005',N'Joe',N'PD','2012-1-12','1:03',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'PASS' union all
select N'ID005',N'Joe',N'PD','2012-1-12','6:03',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'PASS' union all
select N'ID005',N'Joe',N'PD','2012-1-12','7:58',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID005',N'Joe',N'PD','2012-1-12','9:58',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID005',N'Joe',N'PD','2012-1-12','15:58',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID005',N'Joe',N'PD','2012-1-12','16:05',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID005',N'Joe',N'PD','2012-1-12','19:03',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL' union all
select N'ID005',N'Joe',N'PD','2012-1-12','23:03',N'F1-1-1',N'FAIL',N'FAIL',N'PASS',N'FAIL'
Go
;with tmp
as
(
select a.*,
       b.grp,
       row_number()over(partition by b.grp,a.cpid order by a.[date_time])rnt
from #t a,
(
select cast('01:00:00' as datetime)dts,cast('07:00:00' as datetime) dte,1 grp
union all
select cast('07:00:01' as datetime)dts,cast('12:00:00' as datetime) dte,2
union all
select cast('13:00:00' as datetime)dts,cast('19:00:00' as datetime) dte,3
union all
select cast('19:00:01' as datetime)dts,cast('23:59:59' as datetime) dte,4
)b
where a.[date_time] between b.dts and b.dte)
select *
from tmp
where rnt=2
order by cpid
/*
cpid  name1 department section_group           date_time               checker_no wrist l_shoe r_shoe result grp         rnt
----- ----- ---------- ----------------------- ----------------------- ---------- ----- ------ ------ ------ ----------- --------------------
ID004 Berry PEDD       2012-01-12 00:00:00.000 1900-01-01 06:03:00.000 F1-1-1     FAIL  FAIL   PASS   PASS   1           2
ID004 Berry PEDD       2012-01-12 00:00:00.000 1900-01-01 09:58:00.000 F1-1-1     FAIL  FAIL   PASS   FAIL   2           2
ID004 Berry PEDD       2012-01-12 00:00:00.000 1900-01-01 16:05:00.000 F1-1-1     FAIL  FAIL   PASS   FAIL   3           2
ID004 Berry PEDD       2012-01-12 00:00:00.000 1900-01-01 23:03:00.000 F1-1-1     FAIL  FAIL   PASS   FAIL   4           2
ID005 Joe   PD         2012-01-12 00:00:00.000 1900-01-01 23:03:00.000 F1-1-1     FAIL  FAIL   PASS   FAIL   4           2
ID005 Joe   PD         2012-01-12 00:00:00.000 1900-01-01 16:05:00.000 F1-1-1     FAIL  FAIL   PASS   FAIL   3           2
ID005 Joe   PD         2012-01-12 00:00:00.000 1900-01-01 09:58:00.000 F1-1-1     FAIL  FAIL   PASS   FAIL   2           2
ID005 Joe   PD         2012-01-12 00:00:00.000 1900-01-01 06:03:00.000 F1-1-1     FAIL  FAIL   PASS   PASS   1           2(8 row(s) affected)
*/

Answer 2

--调整下语句
;with tmp
as
(
select a.*,
       b.grp,
       row_number()over(partition by b.grp,a.cpid order by a.[date_time] desc)rnt
from #t a,
(
select cast('01:00:00' as datetime)dts,cast('07:00:00' as datetime) dte,1 grp
union all
select cast('07:00:01' as datetime)dts,cast('12:00:00' as datetime) dte,2
union all
select cast('13:00:00' as datetime)dts,cast('19:00:00' as datetime) dte,3
union all
select cast('19:00:01' as datetime)dts,cast('23:59:59' as datetime) dte,4
)b
where a.[date_time] between b.dts and b.dte)
select *
from tmp
where rnt=1
order by cpid