A simple attendance system

Do not consider overtime, leave, go out and so on.

Original record table:
1: Work Calendar Table Calendar
2: Schedule Worktime
3: Personnel Information table employee
4: Exception Category table (late, leave early, no credit card ...) Abnormity
5: Attendance record form timecard


Processing Results table: (can be stored in the form of a table or view)
1: Clock Abnormal 10001 20031102 I am late for work
2: Attendance Schedule 1:10,001 20031101 07:55/12:01/12:02/13:55/18:00/18:01 list all punch details
3: Attendance Schedule 2:10,001 20031101 07:55 12:01 13:55 18:00



/* Working calendar:
This assumes that there are only three working calendars, and the default is as follows
1: Saturday and Sundays do not go to work
2: Not going to work on Sundays
4: Work every day
*/
CREATE TABLE Calendar (
Date smalldatetime primary key clustered,
Flag1 bit,
Flag2 bit,
Flag3 bit
)
EXEC sp_addextendedproperty n ' ms_description ', n ' Date ', n ' user ', n ' dbo ', n ' table ', n ' Calendar ', n ' column ', n ' Date '
EXEC sp_addextendedproperty n ' ms_description ', n ' whether work 1 ', n ' user ', n ' dbo ', n ' table ', n ' Calendar ', n ' column ', n ' Flag1 '
EXEC sp_addextendedproperty n ' ms_description ', n ' whether work 2 ', n ' user ', n ' dbo ', n ' table ', n ' Calendar ', n ' column ', n ' flag2 '
EXEC sp_addextendedproperty n ' ms_description ', n ' whether work 3 ', n ' user ', n ' dbo ', n ' table ', n ' Calendar ', n ' column ', n ' flag3 '
* * Individual input data
Insert Calendar Select ' 20031101 ', 0,1,1
UNION ALL SELECT ' 20031102 ', 0,0,1
UNION ALL SELECT ' 20031103 ', 1,1,1
UNION ALL SELECT ' 20031104 ', 1,1,1
*/

/********* the following stored procedures for batch input data **************/
Create Procedure Addworkcalendar @BDate smalldatetime, @EDate smalldatetime
As
Declare @CDate smalldatetime
Declare @Flag1 Bit
Declare @Flag2 Bit
Declare @Flag3 Bit

Set @CDate = @BDate
If Exists (Select * from calendar Where cld_rq Between @BDate and @EDate)
Begin
Raiserror (' There is already a range of information, please check the start date and end Date!!! ', 16,-1)
Return
End
While @CDate <= @EDate
Begin
Set @Flag1 = (case when DatePart (weekday, @CDate) in (1,7) then 0 Else 1-end)
Set @Flag2 = (DatePart (weekday, @CDate) =1 then 0 Else 1 end)
Set @Flag3 = 1
Insert Calendar Values (@CDate, @Flag, @Flag1, @Flag2)
Set @CDate = DateAdd (day,1, @CDate)
End

/******** Usage:
Exec Addworkcalendar ' 20031101 ', ' 20031130 '
*********/

/*************
Of course, the more flexible approach is to build the table as follows:
Date Way Flag
20011101 1 0
20011101 2 1
20011101 3 1
...
********************/



/*
2: Schedule (for simplicity, do not consider overtime scheduling, and only consider a maximum of four times a day card)
*/
CREATE TABLE Worktime (
Worktimeid int primary key clustered,
Minb char (5),
mins char (5),
Mine char (5),
Moutb char (5),
Mouts char (5),
Moute char (5),
Ainb char (5),
Ains char (5),
Aine char (5),
Aoutb char (5),
Aouts char (5),
Aoute Char (5)
)
Go
EXEC sp_addextendedproperty n ' ms_description ', n ' class numbering ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' Worktimeid '
EXEC sp_addextendedproperty n ' ms_description ', n ' morning work start ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' minb '
EXEC sp_addextendedproperty n ' ms_description ', n ' Morning work standard ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' mins '
EXEC sp_addextendedproperty n ' ms_description ', n ' Morning work stop ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', N ' Mine '
EXEC sp_addextendedproperty n ' ms_description ', n ' Morning off start ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' Moutb '
EXEC sp_addextendedproperty n ' ms_description ', n ' Morning off standard ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' mouts '
EXEC sp_addextendedproperty n ' ms_description ', n ' Morning work stop ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' Moute '

EXEC sp_addextendedproperty n ' ms_description ', n ' afternoon work start ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' ainb '
EXEC sp_addextendedproperty n ' ms_description ', n ' afternoon work standard ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' ains '
EXEC sp_addextendedproperty n ' ms_description ', n ' afternoon work Stop ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' Aine '
EXEC sp_addextendedproperty n ' ms_description ', n ' afternoon off start ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' Aoutb '
EXEC sp_addextendedproperty n ' ms_description ', n ' afternoon off standard ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' aouts '
EXEC sp_addextendedproperty n ' ms_description ', n ' afternoon work Stop ', n ' user ', n ' dbo ', n ' table ', n ' worktime ', n ' column ', n ' aoute '
/*
Note: At the beginning of work-the standard room to punch in the normal clock, in the working standard-clock-in clock belongs to the late
At the beginning of work-the standard room to clock out is early, in the end of the work standard-clock-out clock belongs to normal clocking
The rest of the time clocking in is invalid.
*/

--3: Personnel Information table (only lists the fields related to attendance)
CREATE TABLE Employee (
Workno Char (6) Primary key,
[Name] varchar (10),
Calendarflag int Check (Calendarflag in (1,2,3))--1,2,3 represents the logo 1 in the working Calendar, Flag 2, Flag 3, respectively
Worktimeid INT--Shifts in the scheduling system
)
Go
EXEC sp_addextendedproperty n ' ms_description ', n ' work number ', n ' user ', n ' dbo ', n ' table ', n ' employee ', n ' column ', n ' workno '
EXEC sp_addextendedproperty n ' ms_description ', n ' name ', n ' user ', n ' dbo ', n ' table ', n ' employee ', n ' column ', n ' name '
EXEC sp_addextendedproperty n ' ms_description ', n ' working calendar way ', n ' user ', n ' dbo ', n ' table ', n ' employee ', n ' column ', N ' Calendarflag '
EXEC sp_addextendedproperty n ' ms_description ', n ' class system ', n ' user ', n ' dbo ', n ' table ', n ' employee ', n ' column ', n ' Worktimeid '

/*
Calendarflag 1,2,3 represents the logo 1 in the working Calendar, Flag 2, Flag 3, respectively.
Worktimeid the shift schedule in the corresponding scheduling system
*/


--4: Exception Category table Abnormity
CREATE TABLE Abnormity (
Abnormityno int primary key clustered,
[Description] varchar (10)
)
Go
EXEC sp_addextendedproperty n ' ms_description ', n ' exception category ', n ' user ', n ' dbo ', n ' table ', n ' abnormity ', n ' column ', N ' Abnormityno '
EXEC sp_addextendedproperty n ' ms_description ', n ' exception description ', n ' user ', n ' dbo ', n ' table ', n ' abnormity ', n ' column ', N ' description '

/**************
Exceptions include: Late (and can be subdivided into late time), leave early, no credit card ...
Of course, there may be a deduction or the like, which is not considered here.
*****************/





5: Attendance record form timecard
CREATE TABLE Timecard (
Workno Char (6),
[Date] datetime,
constraint [Pk_timecard] primary key clustered
(
Workno,
[Date]
) on [PRIMARY]
)
Go
EXEC sp_addextendedproperty n ' ms_description ', n ' work number ', n ' user ', n ' dbo ', n ' table ', n ' timecard ', n ' column ', n ' workno '
EXEC sp_addextendedproperty n ' ms_description ', n ' Punch time ', n ' user ', n ' dbo ', n ' table ', n ' timecard ', n ' column ', n ' Date '

/*******
In fact, I actually according to the card number (corresponding work number), date, time, Kazhong number four fields to save.
*******/




/**************** the following processing to obtain attendance schedule 1:**********/
--1. To create a merged function
Create function F_kqlist (@workno char (6), @date char (8))
Returns varchar (400)
As
Begin
DECLARE @str varchar (1000)
Set @str = '
Select @str = @str + '/' + CONVERT (char (8), [date],108) from timecard
where Workno = @workno and DateDiff (Day,[date], @date) = 0
Set @str =right (@str, Len (@str)-1)
Return (@str)
End

/* Usage:
SELECT DISTINCT workno,date,dbo.f_kqlist (Workno,[date])
From timecard
where workno = ' 102337 ' and CONVERT (char (8), [date],112) = ' 20030814 '
*/

Let's leave the rest to you.  In fact, it is not difficult to see whose efficiency is higher. :D
(My approach: exception handling: Using stored procedures, with cursors, lazy to optimize the
Attendance Schedule 2: Use a function, the original with a cursor, not good, and then instead of function