求一考勤的存储过程－－－100分－－－急急

Question

数据乱了，再贴一次！！Login_usr     login_date               login_time         login_flag
acoo 2004-12-08 00:00:00.000 1899-12-30 17:06:38.187 下班
acoo 2004-12-08 00:00:00.000 1899-12-30 17:06:40.547 下班
acoo 2004-12-08 00:00:00.000 1899-12-30 17:06:41.467 下班
acoo 2004-12-08 00:00:00.000 1899-12-30 17:06:42.297 下班
acoo 2004-12-08 00:00:00.000 1899-12-30 17:06:43.280 下班
acoo 2004-12-09 00:00:00.000 1899-12-30 08:31:32.890 上班
acoo 2004-12-09 00:00:00.000 1899-12-30 17:09:49.327 下班
acoo 2004-12-09 00:00:00.000 1899-12-30 17:09:50.390 下班
acoo 2004-12-09 00:00:00.000 1899-12-30 17:09:51.517 下班
acoo 2004-12-09 00:00:00.000 1899-12-30 17:09:52.547 下班
acoo 2004-12-09 00:00:00.000 1899-12-30 17:09:53.750 下班
acoo 2004-12-10 00:00:00.000 1899-12-30 08:25:57.203 上班
acoo 2004-12-10 00:00:00.000 1899-12-30 08:25:58.407 上班
acoo 2004-12-10 00:00:00.000 1899-12-30 17:07:35.860 下班
acoo 2004-12-10 00:00:00.000 1899-12-30 17:07:36.920 下班
acoo 2004-12-10 00:00:00.000 1899-12-30 17:07:37.843 下班
acoo 2004-12-10 00:00:00.000 1899-12-30 17:07:38.750 下班
acoo 2004-12-10 00:00:00.000 1899-12-30 17:10:12.187 下班
acoo 2004-12-10 00:00:00.000 1899-12-30 17:10:13.217 下班
acoo 2004-12-13 00:00:00.000 1899-12-30 17:05:37.437 下班
acoo 2004-12-13 00:00:00.000 1899-12-30 17:05:39.327 下班
acoo 2004-12-13 00:00:00.000 1899-12-30 17:05:40.420 下班
acoo 2004-12-13 00:00:00.000 1899-12-30 17:05:41.547 下班
acoo 2004-12-13 00:00:00.000 1899-12-30 17:05:42.517 下班
acoo 2004-12-13 00:00:00.000 1899-12-30 17:05:43.407 下班
acoo 2004-12-13 00:00:00.000 1899-12-30 17:05:44.420 下班
acoo 2004-12-13 00:00:00.000 1899-12-30 08:27:21.687 上班
fred 2004-12-08 00:00:00.000 1899-12-30 17:03:08.703 下班
fred 2004-12-08 00:00:00.000 1899-12-30 17:03:09.797 下班
fred 2004-12-08 00:00:00.000 1899-12-30 17:03:22.313 下班
fred 2004-12-09 00:00:00.000 1899-12-30 08:09:36.767 上班
fred 2004-12-09 00:00:00.000 1899-12-30 08:09:42.267 上班
fred 2004-12-09 00:00:00.000 1899-12-30 17:02:52.187 下班
fred 2004-12-10 00:00:00.000 1899-12-30 08:06:28.610 上班
fred 2004-12-10 00:00:00.000 1899-12-30 08:06:29.983 上班
fred 2004-12-10 00:00:00.000 1899-12-30 17:06:40.170 下班
fred 2004-12-10 00:00:00.000 1899-12-30 17:06:41.360 下班
fred 2004-12-11 00:00:00.000 1899-12-30 08:00:04.907 上班
fred 2004-12-11 00:00:00.000 1899-12-30 08:00:06.017 上班
fred 2004-12-11 00:00:00.000 1899-12-30 17:02:05.797 下班
fred 2004-12-13 00:00:00.000 1899-12-30 08:09:44.467 上班
fred 2004-12-13 00:00:00.000 1899-12-30 08:09:45.610 上班
fred 2004-12-13 00:00:00.000 1899-12-30 17:03:54.717 下班
fred 2004-12-13 00:00:00.000 1899-12-30 17:03:55.640 下班
fred 2004-12-14 00:00:00.000 1899-12-30 17:04:23.313 下班
fred 2004-12-14 00:00:00.000 1899-12-30 08:00:39.610 上班
fred 2004-12-15 00:00:00.000 1899-12-30 17:04:07.000 上班
fred 2004-12-15 00:00:00.000 1899-12-30 17:04:08.000 下班
fred 2004-12-15 00:00:00.000 1899-12-30 17:04:11.593 下班
fred 2004-12-15 00:00:00.000 1899-12-30 17:04:13.077 下班
fred 2004-12-15 00:00:00.000 1899-12-30 08:09:50.187 上班
fred 2004-12-15 00:00:00.000 1899-12-30 08:09:51.610 上班

Answer 1

假定正常工作日每天都有员工出勤打卡：select
     d.*
from     
     (select 
          a.login_usr,
          b.login_date,
          case 
              when (c.login_timeA is null and c.login_timeB is null) then '旷工'
              when (c.login_timeA>cast('1899-12-30 08:00:00') and c.login_timeA<cast('1899-12-30 15:00:00')) then '迟到,早退'
              when (c.login_timeA>cast('1899-12-30 08:00:00')) then '迟到'
              when (c.login_timeA<cast('1899-12-30 15:00:00')) then '早退'
              else null
          end as status
      from
          (select distinct login_usr  from time_book) a
      cross join
          (select 
                 distinct login_date 
           from 
                 time_book 
           where 
                 datepart(dw,login_date) not in(1,7) 
                 and 
                 substring(convert(varchar(10),login_date,120),6,5) not in('05-01','05-02','05-03','10-01','10-02','10-03')) b
      left join    
          (select 
                 login_usr,
                 login_date,
                 min(case login_flag 
                       when '上班' then 
                           (case 
                               when (month(login_date) between 5 and 9) then login_time 
                               else dateadd(mm,-30,login_time)
                            end)
                       else null 
                 end) as login_timeA,
                 max(case login_flag 
                       when '下班' then login_time 
                       else null 
                 end) as login_timeB
           from
                 time_book
           group by
                 login_usr,
                 login_date) c
      on 
          a.login_date = c.login_date) d
where
     d.status is not null

Answer 2

TO: 
    libin_ftsafe(子陌红尘) 
    
表名为loginlog
表名为loginlog
表名为loginlog
表名为loginlog 你的语法有错误
服务器: 消息 1035，级别 15，状态 10，行 9
'cast' 附近有语法错误，需要 'AS'。
服务器: 消息 170，级别 15，状态 1，行 15
第 15 行: 'a' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 24
第 24 行: 'b' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 45
第 45 行: 'c' 附近有语法错误。

Answer 3

--生成测试数据create table loginlog(
Login_usr   varchar(10),
login_date  datetime,
login_time  datetime,
login_flag  varchar(10)
)
insert into loginlog values('acoo',cast('2004-12-08 00:00:00.000' as datetime),cast('1899-12-30 17:06:38.187' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-08 00:00:00.000' as datetime),cast('1899-12-30 17:06:40.547' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-08 00:00:00.000' as datetime),cast('1899-12-30 17:06:41.467' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-08 00:00:00.000' as datetime),cast('1899-12-30 17:06:42.297' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-08 00:00:00.000' as datetime),cast('1899-12-30 17:06:43.280' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-09 00:00:00.000' as datetime),cast('1899-12-30 08:31:32.890' as datetime),'上班')
insert into loginlog values('acoo',cast('2004-12-09 00:00:00.000' as datetime),cast('1899-12-30 17:09:49.327' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-09 00:00:00.000' as datetime),cast('1899-12-30 17:09:50.390' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-09 00:00:00.000' as datetime),cast('1899-12-30 17:09:51.517' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-09 00:00:00.000' as datetime),cast('1899-12-30 17:09:52.547' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-09 00:00:00.000' as datetime),cast('1899-12-30 17:09:53.750' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 08:25:57.203' as datetime),'上班')
insert into loginlog values('acoo',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 08:25:58.407' as datetime),'上班')
insert into loginlog values('acoo',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 17:07:35.860' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 17:07:36.920' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 17:07:37.843' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 17:07:38.750' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 17:10:12.187' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 17:10:13.217' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 17:05:37.437' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 17:05:39.327' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 17:05:40.420' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 17:05:41.547' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 17:05:42.517' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 17:05:43.407' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 17:05:44.420' as datetime),'下班')
insert into loginlog values('acoo',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 08:27:21.687' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-08 00:00:00.000' as datetime),cast('1899-12-30 17:03:08.703' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-08 00:00:00.000' as datetime),cast('1899-12-30 17:03:09.797' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-08 00:00:00.000' as datetime),cast('1899-12-30 17:03:22.313' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-09 00:00:00.000' as datetime),cast('1899-12-30 08:09:36.767' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-09 00:00:00.000' as datetime),cast('1899-12-30 08:09:42.267' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-09 00:00:00.000' as datetime),cast('1899-12-30 17:02:52.187' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 08:06:28.610' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 08:06:29.983' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 17:06:40.170' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-10 00:00:00.000' as datetime),cast('1899-12-30 17:06:41.360' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-11 00:00:00.000' as datetime),cast('1899-12-30 08:00:04.907' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-11 00:00:00.000' as datetime),cast('1899-12-30 08:00:06.017' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-11 00:00:00.000' as datetime),cast('1899-12-30 17:02:05.797' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 08:09:44.467' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 08:09:45.610' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 17:03:54.717' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-13 00:00:00.000' as datetime),cast('1899-12-30 17:03:55.640' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-14 00:00:00.000' as datetime),cast('1899-12-30 17:04:23.313' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-14 00:00:00.000' as datetime),cast('1899-12-30 08:00:39.610' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-15 00:00:00.000' as datetime),cast('1899-12-30 17:04:07.000' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-15 00:00:00.000' as datetime),cast('1899-12-30 17:04:08.000' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-15 00:00:00.000' as datetime),cast('1899-12-30 17:04:11.593' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-15 00:00:00.000' as datetime),cast('1899-12-30 17:04:13.077' as datetime),'下班')
insert into loginlog values('fred',cast('2004-12-15 00:00:00.000' as datetime),cast('1899-12-30 08:09:50.187' as datetime),'上班')
insert into loginlog values('fred',cast('2004-12-15 00:00:00.000' as datetime),cast('1899-12-30 08:09:51.610' as datetime),'上班')

Answer 4

select
     d.*
from     
     (select 
          a.login_usr,
          c.login_date,
          case 
              when (c.login_timeA is null and c.login_timeB is null) then '旷工'
              when (c.login_timeA>cast('1899-12-30 08:00:00' as datetime) and c.login_timeA<cast('1899-12-30 15:00:00'  as datetime)) then '迟到,早退'
              when (c.login_timeA>cast('1899-12-30 08:00:00' as datetime)) then '迟到'
              when (c.login_timeA<cast('1899-12-30 15:00:00' as datetime)) then '早退'
              else null
          end as status
      from
          (select distinct login_usr  from loginlog) a
      cross join
          (select 
                 distinct login_date 
           from 
                 loginlog 
           where 
                 datepart(dw,login_date) not in(1,7) 
                 and 
                 substring(convert(varchar(10),login_date,120),6,5) not in('05-01','05-02','05-03','10-01','10-02','10-03')) b
      left join    
          (select 
                 login_usr,
                 login_date,
                 min(case login_flag 
                       when '上班' then 
                           (case 
                               when (month(login_date) between 5 and 9) then login_time 
                               else dateadd(mm,-30,login_time)
                            end)
                       else null 
                 end) as login_timeA,
                 max(case login_flag 
                       when '下班' then login_time 
                       else null 
                 end) as login_timeB
           from
                 loginlog
           group by
                 login_usr,
                 login_date) c
      on 
          b.login_date = c.login_date and a.login_usr = c.login_usr) d
where
     d.status is not null

Answer 5

修改一下：select
     d.*
from     
     (select 
          a.login_usr,
          b.login_date,
          case 
              when (c.login_timeA is null and c.login_timeB is null) then '旷工'
              when (c.login_timeA>cast('1899-12-30 08:00:00' as datetime) and c.login_timeA<cast('1899-12-30 15:00:00'  as datetime)) then '迟到,早退'
              when (c.login_timeA>cast('1899-12-30 08:00:00' as datetime)) then '迟到'
              when (c.login_timeA<cast('1899-12-30 15:00:00' as datetime)) then '早退'
              else null
          end as status
      from
          (select distinct login_usr  from loginlog) a
      cross join
          (select 
                 distinct login_date 
           from 
                 loginlog 
           where 
                 datepart(dw,login_date) not in(1,7) 
                 and 
                 substring(convert(varchar(10),login_date,120),6,5) not in('05-01','05-02','05-03','10-01','10-02','10-03')) b
      left join    
          (select 
                 login_usr,
                 login_date,
                 min(case login_flag 
                       when '上班' then 
                           (case 
                               when (month(login_date) between 5 and 9) then login_time 
                               else dateadd(mm,-30,login_time)
                            end)
                       else null 
                 end) as login_timeA,
                 max(case login_flag 
                       when '下班' then login_time 
                       else null 
                 end) as login_timeB
           from
                 loginlog
           group by
                 login_usr,
                 login_date) c
      on 
          b.login_date = c.login_date and a.login_usr = c.login_usr) d
where
     d.status is not null
--输出结果
login_usr login_date                status
-------------------------------------------
acoo  2004-12-09 00:00:00.000 早退
acoo  2004-12-10 00:00:00.000 早退
acoo  2004-12-13 00:00:00.000 早退
acoo  2004-12-14 00:00:00.000 旷工
acoo  2004-12-15 00:00:00.000 旷工
fred  2004-12-09 00:00:00.000 早退
fred  2004-12-10 00:00:00.000 早退
fred  2004-12-13 00:00:00.000 早退
fred  2004-12-14 00:00:00.000 早退
fred  2004-12-15 00:00:00.000 早退

Answer 6

TO libin_ftsafe(子陌红尘):  1. 如果某一天应该上班，而某个员工上班与下班都没有打卡，应该记旷工，你的程序中的实现吗？
  2. 可不可以只查某一个员工情况，也就是要求7. 
     (要求7.输入参数为Login_usr,输出员工的早退,迟到，旷工的记录。)

Answer 7

1、已经实现，楼主看查询的返回结果，如果上班和下班都没有打卡的考勤记录，会标志为'旷工'
2、如果只查询一个员工的记录，可以在最后的d.status is not null 后边加上 login_usr = '...'

Answer 8

TO libin_ftsafe(子陌红尘):结果如像不对，你把login_timeA,login_timeB也打出来看一下，再帮我看看，结果已经很接近了!!

Answer 9

case 
              when (c.login_timeA is null and c.login_timeB is null) then '旷工'
              when (c.login_timeA>cast('1899-12-30 08:00:00' as datetime) and c.login_timeA<cast('1899-12-30 15:00:00'  as datetime)) then '迟到,早退'
              when (c.login_timeA>cast('1899-12-30 08:00:00' as datetime)) then '迟到'
              when (c.login_timeA<cast('1899-12-30 15:00:00' as datetime)) then '早退'
              else null
          end as status,
          c.login_timeA 上班,
          c.login_timeB 下班

Answer 10

楼主所言极是，错了一个地方，在dateadd中应该使用参数mi，我用了mm
已经将日期打印出，修正之后的查询代码如下：select
     d.*
from     
     (select 
          a.login_usr,
          b.login_date,
          c.login_timeA,
          c.login_timeB,
          case 
              when (c.login_timeA is null and c.login_timeB is null) then '旷工'
              when (c.login_timeA>cast('1899-12-30 08:00:00' as datetime) and c.login_timeA<cast('1899-12-30 15:00:00'  as datetime)) then '迟到,早退'
              when (c.login_timeA>cast('1899-12-30 08:00:00' as datetime)) then '迟到'
              when (c.login_timeA<cast('1899-12-30 15:00:00' as datetime)) then '早退'
              else null
          end as status
      from
          (select distinct login_usr  from loginlog) a
      cross join
          (select 
                 distinct login_date 
           from 
                 loginlog 
           where 
                 datepart(dw,login_date) not in(1,7) 
                 and 
                 substring(convert(varchar(10),login_date,120),6,5) not in('05-01','05-02','05-03','10-01','10-02','10-03')) b
      left join    
          (select 
                 login_usr,
                 login_date,
                 min(case login_flag 
                       when '上班' then 
                           (case 
                               when (month(login_date) between 5 and 9) then login_time 
                               else dateadd(mi,-30,login_time)
                            end)
                       else null 
                 end) as login_timeA,
                 max(case login_flag 
                       when '下班' then login_time 
                       else null 
                 end) as login_timeB
           from
                 loginlog
           group by
                 login_usr,
                 login_date) c
      on 
          b.login_date = c.login_date and a.login_usr = c.login_usr) d
where
     d.status is not null

Answer 11

libin_ftsafe(子陌红尘)还在吗？你把login_timeA,login_timeB也打出来看一下，结果如像不对

Answer 12

请问：
　　有cross join与cross join，先执行哪一个？

Answer 13

谢谢  libin_ftsafe(子陌红尘)