/*
功能：按某一段时间，自动生成员工的打卡记录
*/
--人事表
CREATE TABLE #Employee(EmployeeID int,ChineseName nvarchar(20))
--考勤表
CREATE TABLE #OnOffDuty(EmployeeID int,Check1 datetime,Check2 datetime,CheckDate datetime)
--虚拟人事信息
INSERT INTO #Employee
SELECT 1,'张小名' UNION ALL
SELECT 2,'黄大' UNION ALL
SELECT 3,'李兵' UNION ALL
SELECT 4,'刘达' UNION ALL
SELECT 5,'张雷' UNION ALL
SELECT 6,'郭小妹'
/*
自动生成2007年1月份的考勤打卡时间
方法：
使用游标按员工再按日期生成打卡记录
*/
DECLARE @BeginDate datetime
,@EndDate datetime
,@CheckDate datetime
,@EmployeeID int
,@EXECUTE_SQL nvarchar(4000)
,@For int
SELECT @BeginDate='2007-01-01',@EndDate='2007-01-31' ,@EXECUTE_SQL='',@For=0
DECLARE CursorEmployee CURSOR FOR
SELECT EmployeeID FROM #Employee
OPEN CursorEmployee
FETCH NEXT FROM CursorEmployee INTO @EmployeeID
WHILE @@FETCH_STATUS=0
BEGIN
SET @CheckDate=@BeginDate
WHILE (@CheckDate<=@EndDate)
BEGIN
--根据实际调整RAND()的值范围
    SELECT @EXECUTE_SQL=@EXECUTE_SQL+'INSERT INTO #OnOffDuty SELECT '
+CAST(@EmployeeID AS nvarchar(20))+','''
+ CONVERT(nchar(10),@CheckDate,121)+' 08:0'+SUBSTRING(CAST(RAND() AS nvarchar(20)),3,1)+''','''
+ CONVERT(nchar(10),@CheckDate,121)+' 17:0'+SUBSTRING(CAST(RAND() AS nvarchar(20)),3,1)+''','''
+ CONVERT(nchar(10),@CheckDate,121)+''''+CHAR(10)
,@For=@For+1
IF (@For%40)=0  --每40行保存一次，根据实际的@EXECUTE_SQL长度来调试多少行。
     BEGIN
EXECUTE (@EXECUTE_SQL)
SET @EXECUTE_SQL=''
END
SET @CheckDate=DATEADD(day,1,@CheckDate)
END
FETCH NEXT FROM CursorEmployee INTO @EmployeeID
END
CLOSE CursorEmployee
DEALLOCATE CursorEmployee
IF(@EXECUTE_SQL<>'') EXECUTE (@EXECUTE_SQL)  --判断@EXECUTE_SQL是否已经执行完成
--查询结果：
SELECT B.ChineseName,A.Check1,A.Check2,A.CheckDate FROM #OnOffDuty A
INNER JOIN  #Employee B ON B.EmployeeID=A.EmployeeID
ORDER BY A.EmployeeID,A.CheckDate
DROP TABLE #Employee,#OnOffDuty