一个难对应的考勤明细表的存储过程（400分的题，另开页加分300；完成后再加分，这个表对我来说太重要了，请来帮忙）

BarryW 2005-08-11 01:08:48
一个数据库的每天考勤表(TF_KQ)的內容如下:
YG_NO varchar(20）->为工号； BAN_NO varchar(4)->班别； TRS_DD datetime ->为打卡日期；
下为这表的例子以一个工号A002 打卡为2004-10-08天 的数据为准;
YG_NO BAN_NO TRS_DD
------------------------------------------------------------------
A002 002 2004-10-08 07:23:00.000
A002 002 2004-10-08 07:22:00.000

A002 002 2004-10-08 11:31:00.000
A002 002 2004-10-08 12:04:00.000
A002 002 2004-10-08 12:29:00.000

A002 002 2004-10-08 12:45:00.000
A002 002 2004-10-08 12:51:00.000
A002 002 2004-10-08 13:24:00.000

A002 002 2004-10-08 17:33:00.000
A002 002 2004-10-08 17:45:00.000

A002 002 2004-10-08 19:22:00.000
A002 002 2004-10-08 19:25:00.000

A002 002 2004-10-08 20:22:00.000
A002 002 2004-10-08 02:25:00.000
A003 ... ......................
A003 ... ......................
...
又一个数据库每天对每一员工有一实登基数的表(TF_TZ)的內容如下:
YG_NO varchar(20）->为工号；SZ_NO varchar(4)->班别；TRS_DD datetime ->为打卡日期；
QTY numeric(28,8) 基数； CH_ID varchar(1)->审核否
YG_NO SZ_NO TRS_DD QTY CH_ID
------------------------------------------------------------------
... ... .............. .... ..
A002 002 2005-08-07 00:00:00.000 4.00000000 N
A002 002 2005-08-08 00:00:00.000 2.00000000 Y
A002 002 2005-08-09 00:00:00.000 3.00000000 N
A002 002 2005-08-10 00:00:00.000 7.00000000 N
... ... .............. .... ..

两个表通过 (YG_NO,SZ_NO,TRS_DD)来关联

对考勤表(TF_KQ)条件是:
1,上午上班刷卡有效时间为：早上7：00刷卡,刷卡前后30分钟有效,如果同时这时间内有刷两次以上以最后一次为准;
2、中午下班刷卡时间为12：00，刷卡前后30分钟有效,如果同时这时间内有刷两次以上以最后一次为准；
3、下午上班刷卡时间为13：30，刷卡前后30分钟有效,如果同时这时间内有刷两次以上以最后一次为准；
4、下午下班刷卡时间为18：00，刷卡前后30分钟有效,如果同时这时间内有刷两次以上以最后一次为准；
5、晚上加班入刷卡时间为19:01,刷卡前后30分钟有效,如果同时这时间内有刷两次以上以最后一次为准；
6、晚上加班出刷卡时间为第二天的04：00钟，刷卡从19：31至第二天04：30分钟有效,如果同时这时间内有刷两次以上以最后一次为准；
对考勤表(TF_TZ)条件是:
1、JBS(加班时数)=SJ_C(加班出)-SJ_Y(加班入);
2、工时=9;
3、KJ(旷职)=9-{(SC_C(用餐出)-SB_Y(上班入))+(SB_C(下班出)-SC_Y(用餐入))};->如果KJ(旷职)<0时就为空；
4、QTY(基数)=表(TF_TZ)的对应工号与班别对应当天的QTY；

目的：以TRS_DD为打卡日期变量范围和YG_NO为工号变量范围写一个存储过程明细表如下：

YG_NO SZ_NO TRS_DD SB_Y SC_C SC_Y SB_C SJ_Y SJ_C JBS KJ GS QTY
A002 002 05-10-08 07:22 12:29 13:24 17:45 19:25 02:26 07:01 0 9 2
..................................................................................



以上表的字段意义:
SB_Y(上班入)
SC_C(用餐出)
SC_Y(用餐入)
SB_C(下班出)
SJ_Y(加班入)
SJ_C(加班出)
JBS(加班时数)
KJ(旷职)
GS(工时)
QTY(基数)


请各位来帮忙，来看看，谢谢了！
...全文
863 45 打赏 收藏 转发到动态 举报
写回复
用AI写文章
45 条回复
切换为时间正序
请发表友善的回复…
发表回复
add8849 2005-09-22
  • 打赏
  • 举报
 回复
这不天心的人事考勤系统吗？？？难道楼主就是珠海天心的

请问能不能认识一下

QQ：78523054
zlp321002 2005-08-16
  • 打赏
  • 举报
 回复
--就libin_ftsafe(子陌红尘) 一个人在表演
imbutton 2005-08-16
  • 打赏
  • 举报
 回复
看不懂，up
RenRevid 2005-08-16
  • 打赏
  • 举报
 回复
有点感觉
BarryW 2005-08-15
  • 打赏
  • 举报
 回复
JBS(加班时数),KJ(旷职),GS(工时)

對上面三個的算式不能算出小數出來:

如:JBS=23:36(SJ_C-加班出)-18:58(SJ_Y-加班入) ---> 上面的算式是等于4;但实际上加了4个半小时,应是4.5才对;

谢谢!

prcgolf 2005-08-15
  • 打赏
  • 举报
 回复
up
子陌红尘 2005-08-15
  • 打赏
  • 举报
 回复
替换一行代码即可：
----------------------------------------------------------------------------
CREATE PROCEDURE SP_GetDetail
@SDATE DATETIME,
@EDATE DATETIME,
@SYG_NO VARCHAR(20),
@EYG_NO VARCHAR(20)
AS
BEGIN
SELECT
b.YG_NO, --！！替换这一行代码！！
b.SZ_NO,

--------------------------------------------------------------

把：

b.YG_NO,

替换为：

Name = (select Name from MF_YG where YG_NO=b.YG_NO),
BarryW 2005-08-15
  • 打赏
  • 举报
 回复
谢谢了；

我现在还有一个要加的附加问题：我忘了把一个工号姓名加上去了，它存在另外一个表里：
MF_YG

YG_NO, NAME,,,
A002 庄文芳,,,,
............

我想把这个姓名也加上去，应如何做？

完成这个就结贴，谢谢您了
子陌红尘 2005-08-15
  • 打赏
  • 举报
 回复
把原查询代码中的：
-------------------------------------------------------------------------
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),

-------------------------------------------------------------------------
替换成：
-------------------------------------------------------------------------
KJ = CASE
WHEN (9 - ISNULL((DATEDIFF(MI,SB_Y,SC_C)+0.0)/60,0) - ISNULL((DATEDIFF(MI,SC_Y,SB_C)+0.0)/60,0))>0
THEN (9 - ISNULL((DATEDIFF(MI,SB_Y,SC_C)+0.0)/60,0) - ISNULL((DATEDIFF(MI,SC_Y,SB_C)+0.0)/60,0))
ELSE 0
END,
GS = ISNULL((DATEDIFF(MI,SB_Y,SC_C)+0.0)/60,0) + ISNULL((DATEDIFF(MI,SC_Y,SB_C)+0.0)/60,0),
-------------------------------------------------------------------------

因为DATEDIFF函数返回的结果为INT类型，而在SQL Server中，INT类型数据相除只保留整数，所以需要将除号两边的数据至少有一个转换成非整型，+0.0 是一种隐含的转换。

注意：计算出的结果会自动保留六位小数，如果只需要保留一位小数，用CAST函数转换一下。
BarryW 2005-08-12
  • 打赏
  • 举报
 回复
SELECT
b.YG_NO,
b.SZ_NO,
Tf_kq.ban_no,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
......
b.SZ_NO-->这里的值可不可以取TF_KQ里的BAN_NO呢,在这里两个虽然是一样,但是真正要的是TF_KQ里的BAN_NO;
子陌红尘 2005-08-12
  • 打赏
  • 举报
 回复
--生成测试数据
create table TF_KQ(YG_NO varchar(10),BAN_NO varchar(10),TRS_DD datetime)
insert into TF_KQ select 'A002','002','2004-10-08 07:23:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 07:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 11:31:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:04:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:29:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:51:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 13:24:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 17:33:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 17:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 19:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 19:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 20:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 02:25:00.000'

create table TF_TZ(YG_NO varchar(20),SZ_NO varchar(4),TRS_DD datetime,QTY numeric(28,8),CH_ID varchar(1))
insert into TF_TZ select 'A002','002','2004-10-08 00:00:00.000',8.00000000,'Y'
GO


CREATE PROCEDURE SP_GetDetail
@SDATE DATETIME, --日期范围起始日期
@EDATE DATETIME, --日期范围截止日期
@SYG_NO VARCHAR(20),--工号范围起始工号
@EYG_NO VARCHAR(20) --工号范围截止工号
AS
BEGIN
SELECT
b.YG_NO,
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY
FROM
(SELECT
a.YG_NO,
a.SZ_NO,
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY
FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CH_ID
FROM
TF_TZ
UNION
SELECT
DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL
FROM
TF_KQ t
WHERE
NOT EXISTS(SELECT 1 FROM TF_TZ WHERE YG_NO=t.YG_NO AND SZ_NO=t.BAN_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a
WHERE
(a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01'))
AND
(a.YG_NO BETWEEN @SYG_NO AND @EYG_NO)
AND
EXISTS(SELECT 1 FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b

END
GO

--调用存储过程
EXEC SP_GetDetail '2004-01-01','2005-01-01','A002','A008'
GO
BarryW 2005-08-12
  • 打赏
  • 举报
 回复
不好意思,为难libin_ftsafe(子陌红尘)了;

YG_NO SZ_NO TRS_DD SB_Y SC_C SC_Y SB_C SJ_Y SJ_C JBS KJ GS QTY
-------- ----- ---------------------------------- ----------- ---------------------------
A002 01 2005-07-28 00:00:00.000 NULL 12:22 13:58 18:19 18:40 22:04 3 5 4 NULL
A002 01 2005-07-28 00:00:00.000 NULL 12:22 13:58 18:19 18:40 22:04 3 5 4 3.00000000


上面的存储过程是可以通过了,但是显示两条记录出来,只一条出来就行了;QTY在TF_TZ里对应有数据就抓过来,如没有QTY就为NULL

谢谢了
chn_sycjw 2005-08-12
  • 打赏
  • 举报
 回复
libin_ftsafe(子陌红尘):
再次感谢，其它话就不说了
子陌红尘 2005-08-12
  • 打赏
  • 举报
 回复
libin_ftsafe(子陌红尘)
上次你帮我解决的问题我还没给你分呢？怎么给分啊？
---------------------------------------------------------
问题解决就行，给不给分都无所谓了。
chn_sycjw 2005-08-12
  • 打赏
  • 举报
 回复
libin_ftsafe(子陌红尘)
上次你帮我解决的问题我还没给你分呢？怎么给分啊？
chn_sycjw 2005-08-12
  • 打赏
  • 举报
 回复
顶死你们，呵呵
感谢 libin_ftsafe(子陌红尘)
子陌红尘 2005-08-12
  • 打赏
  • 举报
 回复
呵呵，快被你打败了，你在主贴里给出的TF_TZ表表结构一定跟实际环境不一样。
算了，不究根问底了，把以下的代码：

(SELECT * FROM TF_TZ
UNION
SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ) a

修改成如下形式：

(SELECT YG_NO,SZ_NO,TRS_DD,QTY,CH_ID FROM TF_TZ
UNION
SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ) a
子陌红尘 2005-08-12
  • 打赏
  • 举报
 回复
CREATE PROCEDURE SP_GetDetail
@SDATE DATETIME, --日期范围起始日期
@EDATE DATETIME, --日期范围截止日期
@SYG_NO VARCHAR(20),--工号范围起始工号
@EYG_NO VARCHAR(20) --工号范围截止工号
AS
BEGIN
SELECT
b.YG_NO,
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY
FROM
(SELECT
a.YG_NO,
a.SZ_NO,
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY
FROM
(SELECT * FROM TF_TZ
UNION
SELECT DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL FROM TF_KQ) a
WHERE
(a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01'))
AND
(a.YG_NO BETWEEN @SYG_NO AND @EYG_NO)
AND
EXISTS(SELECT 1 FROM TF_KQ WHERE YG_NO = a.YG_NO AND BAN_NO = a.SZ_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b

END
GO
prcgolf 2005-08-12
  • 打赏
  • 举报
 回复
up
子陌红尘 2005-08-12
  • 打赏
  • 举报
 回复
误解楼主的意思，往相反的方向走了一步：
---------------------------------------------------------------------------------------
DROP PROCEDURE SP_GetDetail
DROP TABLE TF_KQ,TF_TZ
GO

--生成测试数据
create table TF_KQ(YG_NO varchar(10),BAN_NO varchar(10),TRS_DD datetime)
insert into TF_KQ select 'A002','002','2004-10-08 07:23:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 07:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 11:31:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:04:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:29:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 12:51:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 13:24:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 17:33:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 17:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 19:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 19:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-08 20:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 02:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 07:23:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 07:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 11:31:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:04:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:29:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 12:51:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 13:24:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 17:33:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 17:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 19:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 19:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-09 20:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 02:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 07:23:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 07:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 11:31:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:04:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:29:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 12:51:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 13:24:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 17:33:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 17:45:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 19:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 19:25:00.000'
insert into TF_KQ select 'A002','002','2004-10-10 20:22:00.000'
insert into TF_KQ select 'A002','002','2004-10-11 02:25:00.000'

create table TF_TZ(YG_NO varchar(20),SZ_NO varchar(4),TRS_DD datetime,QTY numeric(28,8),CH_ID varchar(1))
insert into TF_TZ select 'A002','00D','2004-10-08 00:00:00.000',8.00000000,'Y'
insert into TF_TZ select 'A002','00E','2004-10-09 00:00:00.000',8.00000000,'Y'
GO

--创建存储过程
CREATE PROCEDURE SP_GetDetail
@SDATE DATETIME, --日期范围起始日期
@EDATE DATETIME, --日期范围截止日期
@SYG_NO VARCHAR(20),--工号范围起始工号
@EYG_NO VARCHAR(20) --工号范围截止工号
AS
BEGIN
SELECT
b.YG_NO,
b.SZ_NO,
b.TRS_DD,
SB_Y = CONVERT(CHAR(5),b.SB_Y,108),
SC_C = CONVERT(CHAR(5),b.SC_C,108),
SC_Y = CONVERT(CHAR(5),b.SC_Y,108),
SB_C = CONVERT(CHAR(5),b.SB_C,108),
SJ_Y = CONVERT(CHAR(5),b.SJ_Y,108),
SJ_C = CONVERT(CHAR(5),b.SJ_C,108),
JBS = ISNULL(DATEDIFF(MI,SJ_Y,SJ_C)/60,0),
KJ = CASE
WHEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))>0
THEN (9 - ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) - ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0))
ELSE 0
END,
GS = ISNULL(DATEDIFF(MI,SB_Y,SC_C)/60,0) + ISNULL(DATEDIFF(MI,SC_Y,SB_C)/60,0),
b.QTY
FROM
(SELECT
a.YG_NO,
SZ_NO = (SELECT MAX(BAN_NO) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710),
a.TRS_DD,
SB_Y = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 450 ),
SC_C = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 690 AND 750 ),
SC_Y = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 780 AND 840 ),
SB_C = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1050 AND 1110),
SJ_Y = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1111 AND 1171),
SJ_C = (SELECT max(TRS_DD) FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 1172 AND 1710),
a.QTY
FROM
(SELECT
YG_NO,SZ_NO,TRS_DD,QTY,CH_ID
FROM
TF_TZ
UNION
SELECT
DISTINCT YG_NO,BAN_NO,CAST(CONVERT(CHAR(10),TRS_DD,120) AS DATETIME),NULL,NULL
FROM
TF_KQ t
WHERE
NOT EXISTS(SELECT 1 FROM TF_TZ WHERE YG_NO=t.YG_NO AND DATEDIFF(MI,TRS_DD,t.TRS_DD) BETWEEN 390 AND 1710)) a
WHERE
(a.TRS_DD BETWEEN ISNULL(@SDATE,'1900-01-01') AND ISNULL(@EDATE,'2010-01-01'))
AND
(a.YG_NO BETWEEN @SYG_NO AND @EYG_NO)
AND
EXISTS(SELECT 1 FROM TF_KQ WHERE YG_NO = a.YG_NO AND DATEDIFF(MI,a.TRS_DD,TRS_DD) BETWEEN 390 AND 1710)) b

END
GO


--执行存储过程
EXEC SP_GetDetail '2004-01-01','2005-01-01','A002','A008'
GO

/*
YG_NO SZ_NO TRS_DD SB_Y SC_C SC_Y SB_C SJ_Y SJ_C JBS KJ GS QTY
----- ----- ---------- ----- ----- ----- ----- ----- ----- ---- --- --- ----------
A002 002 2004-10-09 07:23 12:29 13:24 17:45 19:25 02:25 7 0 9 8.00000000
A002 002 2004-10-08 07:23 12:29 13:24 17:45 19:25 02:25 7 0 9 8.00000000
A002 002 2004-10-10 07:23 12:29 13:24 17:45 19:25 02:25 7 0 9 NULL
*/
加载更多回复（25）
考勤管理系统asp版
考勤管理系统asp版
企业人事工资考勤管理系统(c#源码)
企业人事工资考勤管理系统(c#源码)
html5实现的时间轴设计工作记录样式效果源码
这是一款基于html5实现的时间轴设计工作记录样式效果源码，是一款时间考勤表样式插件源码。用户可根据自身需求调整显示的文字、颜色及时间等的样式和数值。使之更具个性化。建议使用火狐或谷歌浏览器查看该源码。...
考勤打卡记录数据库表结构_考勤系统数据表结构
第1页共7页考勤系统数据表结构考勤员信息设置T_KQEMP_INFOMAN_IDVARCHAR2(20)N工号POST_INFOVARCHAR2(20)Y岗位描述POWER_USERVARCHAR2(20)Y创建人POWER_DATEDATEY创建时间POWER_USER_DEPTVARCHAR2(20)Y创建人部门...
Mysql“员工管理系统”存储过程
实验十二 存储过程实践 一、实验目的 1、掌握存储过程的功能与作用 2、掌握存储过程的创建与管理的方法 二、实验内容 用于企业管理的员工管理数据库，数据库名为YGGL，包含员工信息表Employees、部门信息表...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章