菜鸟求助!这个存储过程要怎么写
有考勤表表是这样的
工号 日期 打卡时间 有效
16759 2013-4-8 0:00:00 1900-1-1 9:00:00 0
9999 2013-4-8 0:00:00 1900-1-1 17:30:00 0
4577 2013-4-9 0:00:00 1900-1-1 9:00:00 0
4568 2013-4-9 0:00:00 1900-1-1 17:30:00 0
9966 2013-4-10 0:00:00 1900-1-1 9:00:00 0
8875 2013-4-10 0:00:00 1900-1-1 17:30:00 0
7736 2013-4-11 0:00:00 1900-1-1 9:00:00 0
8865 2013-4-11 0:00:00 1900-1-1 17:39:00 0
7774 2013-4-12 0:00:00 1900-1-1 8:29:00 0
4456 2013-4-12 0:00:00 1900-1-1 17:48:00 0
1125 2013-4-15 0:00:00 1900-1-1 8:33:00 0
现在要求每人每天在12点到12点30分只可以存在一张打卡记录,2张以上的把有效标识写为1.
这个只能用存储过程写,谢谢各位了
自己写了个但是各种错误
UPDATE K07
SET Flag =1
WHERE select * ,ROW_NUMBER() over(order by k0701) as rows from k07 WHERE
K0701 BETWEEN '1900-1-1 12:00:00' AND '1900-1-1 12:30:00'
and k0700>= @sdatebegin
and k0700<=@sdateend
AND rows !='1'
[解决办法]
create table tb(工号 int,日期 datetime ,打卡时间 datetime,有效 int)
insert into tb
select 16759,'2013-4-8 0:00:00','1900-1-1 9:00:00',0
union all select 9999,'2013-4-8 0:00:00','1900-1-1 17:30:00',0
union all select 4577,'2013-4-9 0:00:00','1900-1-1 9:00:00',0
union all select 4568,'2013-4-9 0:00:00','1900-1-1 17:30:00',0
union all select 9966,'2013-4-10 0:00:00','1900-1-1 9:00:00',0
union all select 8875,'2013-4-10 0:00:00','1900-1-1 17:30:00',0
union all select 7736,'2013-4-11 0:00:00','1900-1-1 9:00:00',0
union all select 8865,'2013-4-11 0:00:00','1900-1-1 17:39:00',0
union all select 7774,'2013-4-12 0:00:00','1900-1-1 8:29:00',0
union all select 4456,'2013-4-12 0:00:00','1900-1-1 17:48:00',0
union all select 1125,'2013-4-15 0:00:00','1900-1-1 8:33:00',0
create proc proc_card
as
set nocount on
update a
set 有效=1
from tb a
inner join
(select *
from (select *,rn=ROW_NUMBER() over(partition by 工号,日期 order by 打卡时间) from tb)t
where CONVERT(varchar(5),打卡时间,108) between '12:00' and '12:30' and rn>=2
)b on a.工号=b.工号 and a.打卡时间=b.打卡时间 and a.日期=b.日期
go
--执行
exec proc_card
--創建數據
CREATE TABLE #temp ([工号] int,[日期] datetime,[打卡时间] datetime,[有效] BIT)
INSERT #temp
SELECT 16759,'2013-04-08 00:00:00','1900-01-01 09:00:00',0 UNION ALL
SELECT 9999,'2013-04-08 00:00:00','1900-01-01 17:30:00',0 UNION ALL
SELECT 4577,'2013-04-09 00:00:00','1900-01-01 09:00:00',0 UNION ALL
SELECT 4568,'2013-04-09 00:00:00','1900-01-01 17:30:00',0 UNION ALL
SELECT 9966,'2013-04-10 00:00:00','1900-01-01 09:00:00',0 UNION ALL
SELECT 8875,'2013-04-10 00:00:00','1900-01-01 17:30:00',0 UNION ALL
SELECT 7736,'2013-04-11 00:00:00','1900-01-01 09:00:00',0 UNION ALL
SELECT 8865,'2013-04-11 00:00:00','1900-01-01 17:39:00',0 UNION ALL
SELECT 7774,'2013-04-12 00:00:00','1900-01-01 08:29:00',0 UNION ALL
SELECT 4456,'2013-04-12 00:00:00','1900-01-01 17:48:00',0 UNION ALL
SELECT 1125,'2013-04-15 00:00:00','1900-01-01 08:33:00',0
go
--創建存儲過程
CREATE PROCEDURE test
(@sdatebegin datetime,@sdateend datetime)
AS
BEGIN
;WITH a1 AS
(
SELECT [工号],[日期],[打卡时间],[有效],ROW_NUMBER() OVER (PARTITION BY [工号],[日期] ORDER BY [打卡时间]) re
FROM #temp
WHERE [日期] BETWEEN @sdatebegin AND @sdateend AND
[打卡时间] BETWEEN '1900-01-01 12:00:00' AND '1900-01-01 12:30:00'
)
UPDATE a1 SET [有效] WHERE re>1
END
GO
--調用存儲過程
EXEC test '2013-04-08','2013-04-15'
