求 sql2008 考勤报表问题

[Ryan20082009]

有数据库表 HCI_HQITI
数据库字段：select top 10 t.ID,t.HCI_EMP_NO,t.HCI_CARD_TIME from HCI_HQITI t order by ID desc
ID 编号 打卡日期
25746 HQY00151 2017-07-24 08:26:36.000
25745 HQY00729 2017-07-24 19:32:52.000
25744 HQY001124 2017-07-24 18:12:51.000
25743 HQY00712 2017-07-24 18:11:21.000

我想得到结果如下：
编号 打卡日期 早上打开时间 早上是否正常 下午打开时间 下午是否正常
HQY00151 2017-07-24 08:26:36.000 正常 19:32:52.000 正常

备注：一个人可能上午和下午可能打卡很多次，但是我只要一次记录进行比较 比如早上7-9点都是正常，下午6-10点都为正常

[exception92]

查询 sqlserver 行转列

[Ryan20082009]

引用 1 楼 duanzi_peng 的回复:

查询 sqlserver 行转列

。。。这回答

[c02645]

create table HCI_HQITI (ID int,HCI_EMP_NO varchar(50),HCI_CARD_TIME datetime)
insert into HCI_HQITI values(1,'Q123','2017-07-24 08:26:36')
insert into HCI_HQITI values(2,'Q123','2017-07-24 09:26:36')
insert into HCI_HQITI values(2,'Q123','2017-07-24 17:26:36')
insert into HCI_HQITI values(2,'Q123','2017-07-24 19:26:36')
 --select stuff((select ','+convert(char(10),b.HCI_CARD_TIME,108) from HCI_HQITI as b where b.HCI_EMP_NO=a.HCI_EMP_NO for xml path('')),1,1,'') as 打卡 from HCI_HQITI a group by HCI_EMP_NO,convert(char(10),a.HCI_CARD_TIME,120)

  select HCI_EMP_NO,convert(char(10),a.HCI_CARD_TIME,120) as 日期
  ,(select isnull(min(1),0) from HCI_HQITI b where a.HCI_EMP_NO=b.HCI_EMP_NO and datediff(day,b.HCI_CARD_TIME,convert(char(10),a.HCI_CARD_TIME,120))=0 and convert(char(10),b.HCI_CARD_TIME,108)<'08:30:59')  as 早上
  ,(select isnull(min(1),0) from HCI_HQITI b where a.HCI_EMP_NO=b.HCI_EMP_NO and datediff(day,b.HCI_CARD_TIME,convert(char(10),a.HCI_CARD_TIME,120))=0 and convert(char(10),b.HCI_CARD_TIME,108)>'18:00:00')  as 下午
  from HCI_HQITI a group by HCI_EMP_NO,convert(char(10),a.HCI_CARD_TIME,120)

 
drop table HCI_HQITI

[exception92]

引用 2 楼 Ryan20082009 的回复:

[quote=引用 1 楼 duanzi_peng 的回复:] 查询 sqlserver 行转列

。。。这回答[/quote] 不喜欢可以无视的，没事。不如直接贴出代码来的直接，不过下次还碰到这种需求，还是不会。

[c02645]

  select *,(case when 早上<'08:31:00' then 1 else 0 end) as 早上状态,(case when 下午>'18:00:00' then 1 else 0 end) as 下午状态 from
  (select HCI_EMP_NO,convert(char(10),a.HCI_CARD_TIME,120) as 日期
  ,(select top 1 convert(char(10),b.HCI_CARD_TIME,108) from HCI_HQITI b where a.HCI_EMP_NO=b.HCI_EMP_NO and datediff(day,b.HCI_CARD_TIME,convert(char(10),a.HCI_CARD_TIME,120))=0 order by b.HCI_CARD_TIME)  as 早上
  ,(select top 1 convert(char(10),b.HCI_CARD_TIME,108) from HCI_HQITI b where a.HCI_EMP_NO=b.HCI_EMP_NO and datediff(day,b.HCI_CARD_TIME,convert(char(10),a.HCI_CARD_TIME,120))=0 order by b.HCI_CARD_TIME desc)  as 下午
  from HCI_HQITI a group by HCI_EMP_NO,convert(char(10),a.HCI_CARD_TIME,120)
  )t

[c02645]

  select *,(case when 早上<'08:31:00' then 1 else 0 end) as 早上状态,(case when 下午>'18:00:00' then 1 else 0 end) as 下午状态 from
  (select HCI_EMP_NO,convert(char(10),a.HCI_CARD_TIME,120) as 日期
  ,(select top 1 convert(char(10),b.HCI_CARD_TIME,108) from HCI_HQITI b where a.HCI_EMP_NO=b.HCI_EMP_NO and datediff(day,b.HCI_CARD_TIME,convert(char(10),a.HCI_CARD_TIME,120))=0 and datepart(hh,b.HCI_CARD_TIME)<12 order by b.HCI_CARD_TIME)  as 早上
  ,(select top 1 convert(char(10),b.HCI_CARD_TIME,108) from HCI_HQITI b where a.HCI_EMP_NO=b.HCI_EMP_NO and datediff(day,b.HCI_CARD_TIME,convert(char(10),a.HCI_CARD_TIME,120))=0 and datepart(hh,b.HCI_CARD_TIME)>12 order by b.HCI_CARD_TIME desc)  as 下午
  from HCI_HQITI a group by HCI_EMP_NO,convert(char(10),a.HCI_CARD_TIME,120)
  )t