遇到一个难问题,请各位兄弟帮忙.在线等代......

Question

不好意思，大概格式如下
员工号        班次        打卡日期       上班时间     下班时间   迟到时间  早退时间   加班   旷工
A1459    MS 2004.04.01 08:44      17:17       14分钟     20分钟      0
A1459    MS 2004.04.02 08:19      19:10       0          0       1.5h
A1459    MS 2004.04.03 08:21      17:40
.
.
A2176    LS 2004.04.01 19:32      07:22       2         10        0
A2176    LS 2004.04.02 19:25      08:32       0          0        1h
A2176    LS 2004.04.03                                                   1其实完整的班次表里还有上班打卡的有效时间段,下班打卡的有效时间段,防止重复多次打卡的现象发生.
shift_mst
班次       上班时间    下班时间    是否跨日上班   上班打卡的有效时间   上班打卡的有效时间
SHIFT_TYPE WORK_START WORK_END  OVER_NIGHT   START_PUNCH   STOP_PUNCH  OT_START  OT_END  OT_OVE_NIGHT
---------- ---------- --------                 -----                 ------- 
MS         08:30       17:30           N       06:30      10:30        14:00      19:00    N
LS         19:30       07:30           Y       17:30      21:30        05:30      09:30    N
如MS 班次 正常上班时间为08：30  打卡的有效时间为 START_PUNCH 06：30--- STOP_PUNCH 10:30 如在这段时间打了2次，就取最大的一次为打卡上班的时间
如打卡的时间大于上班的时间就为迟到并得到迟到多少分钟。下班正常下班时间为17：30 下班打卡的有效时间为 OT_START 14:00 --- OT_END 19：00 。如果在这短时间打多次就取最小的时间为下班打卡时间。
如果下班打卡时间-正常下班时间为加班时间。但加班时间要大于一个小时才算为加班时间。
 

Answer 1

create table record
(
   emp_no  varchar(10),
   shift_type  varchar(2),
   T_date varchar(15),
   T_time varchar(15)
)insert record
select 'A1459','MS','2004.04.01','0844' union
select 'A1459','MS','2004.04.01','1750' union
select 'A1459','MS','2004.04.02','0819' union
select 'A1459','MS','2004.04.02','1910' union
select 'A1459','MS','2004.04.03','0821' union
select 'A1459','MS','2004.04.03','1740' union
select 'A1459','MS','2004.04.04','0822' union
select 'A1459','MS','2004.04.04','1810' unionselect 'A2176','LS','2004.04.01','1932' union
select 'A2176','LS','2004.04.02','0722' union
select 'A2176','LS','2004.04.02','1925' union
select 'A2176','LS','2004.04.03','0732' unionselect 'A2176','LS','2004.04.04','1945' union
select 'A2176','LS','2004.04.05','0936'create table shift_mst
(
   SHIFT_TYPE  varchar(10),
   WORK_START  varchar(10),
   WORK_END varchar(15),
   OVER_NIGHT varchar(15),
   START_PUNCH varchar(15),
   STOP_PUNCH varchar(15),
   OT_START varchar(15),
   OT_END varchar(15),
   OT_OVE_NIGHT varchar(2)
)
insert shift_mst
select 'MS','08:30','17:30','N','06:30','10:30','14:00','19:00','N' union
select 'LS','19:30','07:30','Y','17:30','21:30','05:00','09:30','N' 
--视图
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_AM]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_AM]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_LS]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_LS]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_LX]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_LX]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_PM]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_PM]
GOSET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GOCREATE VIEW dbo.V_AM      --白班的上班时间
AS
SELECT emp_no, shift_type, CAST(T_date AS datetime) AS T_date, 
      MIN(CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime)) 
      AS T_time, 'AM' AS Flag
FROM dbo.record R
WHERE (shift_type = 'MS')
GROUP BY emp_no, shift_type, T_dateGO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GOSET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GOCREATE VIEW dbo.V_LS   --夜班上班时间
AS
SELECT emp_no, shift_type, CAST(T_date AS datetime) AS T_date, 
      MAX(CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime)) 
      AS T_time, 'S' AS Flag
FROM dbo.record R
WHERE (shift_type = 'LS') AND (DATEDIFF(hh, CAST(T_date + ' ' + LEFT(T_time, 2) 
      + ':' + RIGHT(T_time, 2) + ':00' AS datetime), DATEADD(d, 1, 
      CAST(T_date AS datetime))) < 12)
GROUP BY emp_no, shift_type, T_dateGO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GOSET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GOCREATE VIEW dbo.V_LX        --夜班下班时间
AS
SELECT emp_no, shift_type, CAST(T_date AS datetime) AS T_date, 
      MIN(CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime)) 
      AS T_time, 'E' AS Flag
FROM dbo.record R
WHERE (shift_type = 'LS') AND (DATEDIFF(hh, CAST(T_date + ' ' + LEFT(T_time, 2) 
      + ':' + RIGHT(T_time, 2) + ':00' AS datetime), DATEADD(d, 1, 
      CAST(T_date AS datetime))) > 12)
GROUP BY emp_no, shift_type, T_dateGO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GOSET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GOCREATE VIEW dbo.V_PM  --白班的下班时间
AS
SELECT emp_no, shift_type, CAST(T_date AS datetime) AS T_date, 
      MAX(CAST(T_date + ' ' + LEFT(T_time, 2) + ':' + RIGHT(T_time, 2) + ':00' AS datetime)) 
      AS T_time, 'PM' AS Flag
FROM dbo.record R
WHERE (shift_type = 'MS')
GROUP BY emp_no, shift_type, T_dateGO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
---------执行语句
select distinct TMS.emp_no as 员工号,TMS.shift_type as 班次, TMS.T_date as 打卡日期,
(select convert(char(15),T_time,108)  from V_AM where V_AM.T_date=TMS.T_date ) as 上班时间,
(select convert(char(15),T_time,108)  from V_PM where V_PM.T_date=TMS.T_date ) as 下班时间,
case when datediff(n, sh.WORK_START,(select convert(char(15),T_time,108)  from V_AM where V_AM.T_date=TMS.T_date ))>0 then datediff(n, sh.WORK_START,(select convert(char(15),T_time,108)  from V_AM where V_AM.T_date=TMS.T_date )) else 0 end  as 迟到时间,
case when datediff(n, sh.WORK_END,(select convert(char(15),T_time,108)  from V_PM where V_PM.T_date=TMS.T_date ))<0 then abs(datediff(n, sh.WORK_END,(select convert(char(15),T_time,108)  from V_PM where V_PM.T_date=TMS.T_date ))) else 0 end as 早退时间,
case when datediff(n, sh.WORK_END,(select convert(char(15),T_time,108)  from V_PM where V_PM.T_date=TMS.T_date ))>0 then round(datediff(n, sh.WORK_END,(select convert(char(15),T_time,108)  from V_PM where V_PM.T_date=TMS.T_date ))/60.0,2) else 0 end as 加班时间,
case when (select convert(char(15),T_time,108)  from V_AM where V_AM.T_date=TMS.T_date )<>' ' and (select convert(char(15),T_time,108)  from V_PM where V_PM.T_date=TMS.T_date ) <> '' then '0' else '1' end as 旷工
from shift_mst SH,
                   (select * from V_AM 
                    union all
                    select * from V_PM) TMS
where SH.SHIFT_TYPE=TMS.shift_type
union 
select distinct TMS.emp_no,TMS.shift_type, TMS.T_date,
(select convert(char(15),T_time,108)  from V_LS where V_LS.T_date=TMS.T_date ) as 上班时间,
(select convert(char(15),T_time,108)  from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date)) as 下班时间,
case when datediff(n, sh.WORK_START,(select convert(char(15),T_time,108)  from V_LS where V_LS.T_date=TMS.T_date ))>0 then datediff(n, sh.WORK_START,(select convert(char(15),T_time,108)  from V_LS where V_LS.T_date=TMS.T_date )) else 0 end  as 迟到时间,
case when datediff(n, sh.WORK_END,(select convert(char(15),T_time,108)  from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date)))<0 then abs(datediff(n, sh.WORK_END,(select convert(char(15),T_time,108)  from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date)))) else 0 end as 早退时间,
case when datediff(n, sh.WORK_END,(select convert(char(15),T_time,108)  from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date)))>0 then round(datediff(n, sh.WORK_END,(select convert(char(15),T_time,108)  from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date)))/60.0,2) else 0 end as 加班时间,
case when (select convert(char(15),T_time,108)  from V_LS where V_LS.T_date=TMS.T_date )<>' ' and (select convert(char(15),T_time,108)  from V_LX where V_LX.T_date=dateadd(dd,1,TMS.T_date) ) <> '' then '0' else '1' end as 旷工
from shift_mst SH,
                   (select * from V_LS 
                    union all
                    select * from V_LX) TMS
where SH.SHIFT_TYPE=TMS.shift_type

Answer 2

结果为:
A1459 MS 2004-04-01 00:00:00.000 08:44:00        17:50:00        14 0 .330000 0
A1459 MS 2004-04-02 00:00:00.000 08:19:00        19:10:00        0 0 1.670000 0
A1459 MS 2004-04-03 00:00:00.000 08:21:00        17:40:00        0 0 .170000 0
A1459 MS 2004-04-04 00:00:00.000 08:22:00        18:10:00        0 0 .670000 0
A2176 LS 2004-04-01 00:00:00.000 19:32:00        07:22:00        2 8 .000000 0
A2176 LS 2004-04-02 00:00:00.000 19:25:00        07:32:00        0 0 .030000 0
A2176 LS 2004-04-03 00:00:00.000 NULL NULL 0 0 .000000 1
A2176 LS 2004-04-04 00:00:00.000 19:45:00        09:36:00        15 0 2.100000 0
A2176 LS 2004-04-05 00:00:00.000 NULL NULL 0 0 .000000 1

Answer 3

多谢scmail81(freedom) 大侠. 还有一个条件,就是遇到休息日,上班为加班,不上班显示为休息休息日
班次              星期
SHIFT_TYPE DAY_OFF
MS          1
MS         7
LS          1

Answer 4

create table record
(
   emp_no  varchar(10),
   shift_type  varchar(2),
   T_date varchar(15),
   T_time varchar(15)
)insert record
select 'A1459','MS','2004.04.01','0844' union
select 'A1459','MS','2004.04.01','1750' union
select 'A1459','MS','2004.04.02','0819' union
select 'A1459','MS','2004.04.02','1910' union
select 'A1459','MS','2004.04.03','0821' union
select 'A1459','MS','2004.04.03','1740' union
select 'A1459','MS','2004.04.04','0822' union
select 'A1459','MS','2004.04.04','1810' unionselect 'A2176','LS','2004.04.01','1932' union
select 'A2176','LS','2004.04.02','0722' union
select 'A2176','LS','2004.04.02','1925' union
select 'A2176','LS','2004.04.03','0732' unionselect 'A2176','LS','2004.04.04','1945' union
select 'A2176','LS','2004.04.05','0936'create table emp_mst
(
  emp_no varchar(10),
  shift_type varchar(2)
)
insert emp_mst
select 'A1459', 'MS' union
select 'A2176', 'LS'
create table shift_mst
(
   SHIFT_TYPE  varchar(10),
   WORK_START  varchar(10),
   WORK_END varchar(15),
   OVER_NIGHT varchar(15),
   START_PUNCH varchar(15),
   STOP_PUNCH varchar(15),
   OT_START varchar(15),
   OT_END varchar(15),
   OT_OVE_NIGHT varchar(2)
)
insert shift_mst
select 'MS','08:30','17:30','N','06:30','10:30','14:00','19:00','N' union
select 'LS','19:30','07:30','Y','17:30','21:30','05:00','09:30','N' go
CREATE PROCEDURE SP_GetDetail
  @SDATE DATETIME,    --日期范围起始日期
  @EDATE DATETIME    --日期范围截止日期AS
BEGINdeclare @emp_id varchar(10), @wk_st varchar(10), @wk_ed varchar(10), @ov_night varchar(15), @ot_ov_night varchar(2)
declare @ls_shift varchar(2), @st_punch varchar(15), @end_punch varchar(15), @st_ot varchar(15), @end_ot varchar(15)create table #att_detail(
            emp_no varchar(10),
            T_date varchar(15)
)     Declare cur_emp cursor for
     select emp_mst.emp_no, emp_mst.shift_type, shift_mst.work_start, 
            shift_mst.work_end, shift_mst.over_night, shift_mst.start_punch, shift_mst.stop_punch, shift_mst.ot_start,
            shift_mst.ot_end, shift_mst.ot_ove_night
     from emp_mst, shift_mst where emp_mst.shift_type = shift_mst.shift_type      
     open cur_emp 
     fetch from cur_emp into @emp_id, @ls_shift, @wk_st, 
                                 @wk_ed, @ov_night, @st_punch, @end_punch, @st_ot,
                                 @end_ot, @ot_ov_night
                                
     while @@fetch_status = 0
     begin 
          
           insert #att_detail (emp_no, t_date)
           select  emp_no, T_date  from record where emp_no = @emp_id and (CAST(T_date AS datetime)  between @Sdate and @edate) 
        fetch next from cur_emp into @emp_id, @ls_shift, @wk_st, 
                                 @wk_ed, @ov_night, @st_punch, @end_punch, @st_ot,
                                 @end_ot, @ot_ov_night
     end 
     
     close  cur_emp
     deallocate cur_empselect * from #att_detail
drop table #att_detail
End
go
EXEC SP_GetDetail '2004-01-01','2005-01-01'
DROP TABLE RECORD, emp_mst, shift_mst
drop PROCEDURE SP_GetDetail

Answer 5

请大家帮我补充,得到
员工号        班次        打卡日期       时间1     时间2   迟到时间  早退时间   加班时间    旷工
A1459   MS2004.04.010844      1717   14         20          0
A1459   MS2004.04.020819      1910   0          0           1.5h
A1459   MS2004.04.030821      1740
.
.
A2176   LS2004.04.011932      0722   2         10           0
A2176   LS2004.04.021925      0832   0          0           1h
A2176   LS2004.04.03                                                  1