34,837
社区成员
发帖
与我相关
我的任务
分享遇到一个难问题,请各位兄弟帮忙.在线等代......
我想得到一个月员工的考勤报表
要求是根据班次表和打卡原始记录来显示员工在规定的上下班时间是否迟到，迟到多长时间，早退多长时间， 加班多长时间（单位最少是0.5小时），旷工多长时间
遇到星期六，跟日算加班。如果休息日上班算加班，不来不算旷工。
员工号 班次 打卡日期 时间1 时间2 迟到时间 早退时间 加班时间 旷工
A1459 MS 2004.04.01 0844 1717 14 20 0
A1459 MS 2004.04.02 0819 1910 0 0 1.5h
A1459 MS 2004.04.03 0821 1740
.
.
A2176 LS 2004.04.01 1932 0722 2 10 0
A2176 LS 2004.04.02 1925 0832 0 0 1h
A2176 LS 2004.04.03 1
班次表
shift_mst
班次 上班时间 下班时间 是否跨日上班
SHIFT_TYPE WORK_START WORK_END OVER_NIGHT
---------- ---------- -------- ----- -------
MS 0830 1730 N
LS 1930 0730 Y
休息日
班次 星期
SHIFT_TYPE DAY_OFF
MS 1
MS 7
LS 1
打卡原始记录
record
员工号 班次 打卡日期 时间
emp_no shift_type date time
A1459 MS 2004.04.01 0844
A1459 MS 2004.04.01 1750
A1459 MS 2004.04.02 0819
A1459 MS 2004.04.02 1910
A1459 MS 2004.04.03 0821
A1459 MS 2004.04.03 1740
A1459 MS 2004.04.04 0822
A1459 MS 2004.04.04 1810
A2176 LS 2004.04.01 1932
A2176 LS 2004.04.02 0722
A2176 LS 2004.04.02 1925
A2176 LS 2004.04.03 0732
A2176 LS 2004.04.04 1945
A2176 LS 2004.04.05 0936
要求是根据班次表和打卡原始记录来显示员工在规定的上下班时间是否迟到，迟到多长时间，早退多长时间， 加班多长时间（单位最少是0.5小时），旷工多长时间
遇到星期六，跟日算加班。如果休息日上班算加班，不来不算旷工。
员工号 班次 打卡日期 时间1 时间2 迟到时间 早退时间 加班时间 旷工
A1459 MS 2004.04.01 0844 1717 14 20 0
A1459 MS 2004.04.02 0819 1910 0 0 1.5h
A1459 MS 2004.04.03 0821 1740
.
.
A2176 LS 2004.04.01 1932 0722 2 10 0
A2176 LS 2004.04.02 1925 0832 0 0 1h
A2176 LS 2004.04.03 1
班次表
shift_mst
班次 上班时间 下班时间 是否跨日上班
SHIFT_TYPE WORK_START WORK_END OVER_NIGHT
---------- ---------- -------- ----- -------
MS 0830 1730 N
LS 1930 0730 Y
休息日
班次 星期
SHIFT_TYPE DAY_OFF
MS 1
MS 7
LS 1
打卡原始记录
record
员工号 班次 打卡日期 时间
emp_no shift_type date time
A1459 MS 2004.04.01 0844
A1459 MS 2004.04.01 1750
A1459 MS 2004.04.02 0819
A1459 MS 2004.04.02 1910
A1459 MS 2004.04.03 0821
A1459 MS 2004.04.03 1740
A1459 MS 2004.04.04 0822
A1459 MS 2004.04.04 1810
A2176 LS 2004.04.01 1932
A2176 LS 2004.04.02 0722
A2176 LS 2004.04.02 1925
A2176 LS 2004.04.03 0732
A2176 LS 2004.04.04 1945
A2176 LS 2004.04.05 0936
...全文
请发表友善的回复…
发表回复
qianduo 2005-10-19
- 打赏
- 举报
请大家帮我补充,得到
员工号 班次 打卡日期 时间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
员工号 班次 打卡日期 时间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
qianduo 2005-10-19
- 打赏
- 举报
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' union
select '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' union
select '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
BEGIN
declare @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_emp
select * 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
LGame 2005-10-18
- 打赏
- 举报
高人啊
qianduo 2005-10-18
- 打赏
- 举报
班次要是再增加几个呢？
qianduo 2005-10-18
- 打赏
- 举报
算异常
real_name 2005-10-18
- 打赏
- 举报
到了有效时间没有打卡的怎么算呢？
qianduo 2005-10-17
- 打赏
- 举报
多谢scmail81(freedom) 大侠. 还有一个条件,就是遇到休息日,上班为加班,不上班显示为休息
休息日
班次 星期
SHIFT_TYPE DAY_OFF
MS 1
MS 7
LS 1
休息日
班次 星期
SHIFT_TYPE DAY_OFF
MS 1
MS 7
LS 1
qianduo 2005-10-17
- 打赏
- 举报
还有打卡的有效时间没有考虑进去
qianduo 2005-10-15
- 打赏
- 举报
不好意思，大概格式如下
员工号 班次 打卡日期 上班时间 下班时间 迟到时间 早退时间 加班 旷工
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 。如果在这短时间打多次就取最小的时间为下班打卡时间。
如果下班打卡时间-正常下班时间为加班时间。但加班时间要大于一个小时才算为加班时间。
员工号 班次 打卡日期 上班时间 下班时间 迟到时间 早退时间 加班 旷工
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 。如果在这短时间打多次就取最小的时间为下班打卡时间。
如果下班打卡时间-正常下班时间为加班时间。但加班时间要大于一个小时才算为加班时间。
天地客人 2005-10-15
- 打赏
- 举报
你要的考勤记录是什么样的格式啊?请说明?
$扫地僧$ 2005-10-15
- 打赏
- 举报
结果为:
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
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
$扫地僧$ 2005-10-15
- 打赏
- 举报
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' union
select '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' union
select '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]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_LS]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_LS]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_LX]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_LX]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_PM]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_PM]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE 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_date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE 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_date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE 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_date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE 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_date
GO
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
(
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' union
select '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' union
select '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]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_LS]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_LS]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_LX]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_LX]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_PM]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_PM]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE 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_date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE 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_date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE 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_date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE 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_date
GO
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
