Don't consider overtime, leave, and go out.
Original record table: 1: Work calendar Calendar2: Route Worktime3: Personnel Information Table Employee4: Abnormal Category Table (late, early retreat, brushless card ...) Abnormity5: Attendance record table Timecard
Procedure: (can be stored in the form of a table or view) 1: Tag abnormally 10001 20031102 morning to work late 2: Attendance details 1: 10001 20031101 07: 55/12: 01/12: 02/13: 55/18: 00/18: 01 list all the cards in detail 3: Attendance details 2: 10001 20031101 07:55 12:01 13:55 18:00
/ * Work calendar: The following assumptions are only three working calendars. The default situation is as follows 1: Saturday and Sunday don't go to work 2: Sunday is not going to work 4: Every day, you will go to work * / create Table Calendar (Date SmallDatetime Primary Key Clustered, Flag1 Bit, Flag2 Bit, flag3 bit) EXEC SP_ADDEXTENDEDPROPERTY N'MS_DEXTENDEDPROPERTY N'MS_DESBRIPTION ', N' Date ', N'User', N'dbo ', N'table', N'calendar ', N'Column', N'date'Exec sp_addextendedProperty n ' MS_DESBRIPTION ', N' Whether to go to work 1 ', N'TEER', N'Calendar ', N'Column', N'Flag1'Exec sp_addextendedProperty N'MS_DEXTENDEDPROPERTY N'MS_DESBRIPTION ', N' Whether to go to work 2 ', N'User', N'TBo ', N'column', N'Flag2'Exec sp_addextendedproperty N'MS_DEXTENDEDPROPERTY N'MS_DESCRIPTION ', N' Whether to go to work 3 ', N'User' N'dbo ', N'table', N'calendar ', N'Column', N'Flag3 '/ * Individual Record Information Insert Calendar Select' 20031101 ', 0, 1, 1 UNION All Select' 20031102 ', 0, 0,1union All Select '20031103', 1, 1, 1 UNION All SELECT '20031104', 1, 1, 1 * /
/ ********* The following stored procedures are used for mass entry information ************** / CREATE Procedure addWorkcalendar @Bdate SmallDatetime, @ edate 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 ('Exported information, 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 = ( Case when datepart (weekday, @ cdate) = 1 Then 0 else 1 End) set @ flag3 = 1 insert calendar value (@ cdate, @ flag, @ flag1, @ flag2) set @cdate = dateadd (day, 1, @ cdate ) End / ******** usage: Exec AddWorkcalendar '20031101', '20031130' ******** /
/ ********************************************************************************************************************************************* 10 ********* /
/ * 2: Remote form (for simplicity, do not consider overtime, only one day you need four credit cards) * / 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), AOUTE CHAR (5), AOUTE CHAR (5)) GOEXEC SP_ADDEXTENDEDPROPERTY N'MS_DEXTENDEDPROPERTY N'MS_DESBRIPTION ', N' Class No. ', N'User', N'Worktime ', N'WorkTime', N 'Column', N'Worktimeid'exec sp_addextendedproperty N'MS_DEXTENDPROPERTY N'MS_DESBRIPTION ', N' AM INTW ', 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'column ', n'Worktime', n'column ', n'mins'Exec sp_addextendedProperty N'MS_Description' , N 'morning starter', N'User ', n'dbo', n'column ', n'mine'exec sp_addextendedproperty N'MS_DEXTENDEDPROPERTY N'MS_DESCRIPTION', N 'AM Get off , N'User ', N'DBO', N'TABLE ', N'Worktime', N'Column ', N'moutb'Exec sp_addextendedProperty N'MS_DEXTENDPROPERTY N'MS_DESCRIPTION', N 'AM Getting Stated Standard', N'User ', N 'dbo', n'table ', n'worktime', n'column ', n'mouts'exec sp_addextendedprop ERTY N'MS_DESBRIPTION ', N' AM off-get off work stop ', N'User', N'dbo ', N'Table', N'Worktime ', N'Column', N'moute '
EXEC SP_ADDEXTENDEDPROPERTY N'MS_DESBRIPTION ', N' afternoon start ', N'User', n'dbo ', n'tde', n'worktime ', n'column', n'Ainb'Exec sp_addextendedProperty N'MS_DESCRIPTION ', N 'afternoon work standard', N'User ', n'dbo', n'column ', n'worktime', n'column ', N'Ains'Exec sp_addextendedproperty N'MS_DESCRIPTION', N 'afternoon starter', N'User ', n'Worktime', n'column ', n'aine'exec sp_addextendedproperty N'MS_DEXTENDEDPROPERTY N'MS_DEXTENDEDPROPERTY N'MS_DEXTENDEDPROPERTY N'MS_DESBRIPTION', N 'ANP GACE', N'USER ', N' DBO ', N'table', N'Worktime ', N'Aunt'Exec sp_addextendedProperty N'MS_DESCRIPTION', N 'ANP GASA', N'USER ', N'DBO', N'TABLE ' N'Worktime ', N'column', N'Aouts'Exec sp_addextendedProperty N'MS_DESCRIPTION ', N'Worktime', N'table ', N'Worktime', N'table ', N'Worktime', N'TEABLE ', N'WORKTIME' 'Column', N'Aoute '/ * Note: At the beginning of the work - to work in the standard room is normal punch, at work standard - go to work, the card is late to get started with the next start - work standard room is early, at work standard - from work Stopping cards belong to the normal card, and the card is invalid. * /
--3: Personnel Data Sheet (Listing and Attendance Related Fields) Create Table Employee (Workno Char (6) Primary Key, [Name] VARCHAR (10), Calendarflag INT Check (Calendarflag In (1, 2, 3) ), - 1, 2, 3 indicate the logo 1 in the work calendar 1, Sign 2, Sign 3 Worktimeid Int - class in the class "GoExec sp_addextendedProperty N'MS_DESCRIPTION ', N' INTE ', N' User ', N'DBO', N'table ', N'Employee', N'Column ', N'Workno'Exec sp_addextendedproperty N'MS_DEXTENDEDPROPERTY N'MS_DESCRIPTION', N 'Name', N'User ', N'dbo', N 'Table', N'Employee ', N'Column', N'Name'Exec sp_addextendedProperty N'MS_DESCRIPTION ', N' Work Calendar Way ', N'User', N'dbo ', N'table', N'Employee ', N'calendarflag'exec sp_addextendedproperty N'MS_DEXTENDPROPERTY N'MS_DESCRIPTION', N 'Class', N'User', N'dbo ', N'TABLE', N'Employee ', N'Column', N 'Worktimeid' / * Calendarflag 1, 2, 3 represents the logo 1 in the work calendar, the flag 2, the sign 3worktimeid corresponds to the class of the class "* /
--4: Exception category table abnormitycreate table abnormity (abnormityno int primary key clustered, [description] varchar (10)) goexec sp_addextendedproperty N'MS_Description ', N' anomaly category ', N'user', N'dbo ', N' Table ', N'Abnormity', N'Column ', N'AbnormityNo'Exec sp_addextendedproperty N'MS_DEXTENDPROPERTY N'MS_DEXTENDEDPROPERTY N'MS_DESBRIPTION', N 'Executive Description', N'User ', N'dbo', N'table ', N'Abnormity', N'column ', n'description'
/ ************* Abnormally includes: late (can also be subsequently divided into time period), early retreat, brushless card ... of course, may have a buckle mode, this Do not consider it. ***************** /
5: attendance record table timecardcreate table timecard (workno char (6), [date] datetime, constraint [pk_timecard] primary key clustered (workno, [date]) ON [PRIMARY]) goexec sp_addextendedproperty N'MS_Description ', N' job number ', N'User', N'TIMECard ', N'Column', N'Workno'Exec sp_addextendedproperty N'MS_DEXTENDEDPROPERTY N'MS_DESCRIPTION ', N' Card Time ', N'User', N 'dbo', n'table ', n'timecard', n'column ', n'date' / ******* In fact, I actually follow the card number (corresponding working number), date, time, card clock The four fields are included. ******* /
/ **************** Detachment is processed to get the test fine table 1: ********** / - 1. Create a merged function crete function f_kqlist (@ Workno char (6), @ Date Char (8)) Returns Varchar (400) asbegindeclare @Str VARCHAR (1000) SET @Str = '' SELECT @ Str = @ Str '/' Convert (Char (8), [Date ], 108) from timecard where workno = @Workno and datediff (day, [date], @date) = 0set @ Str = Right (@ Str, Len (@STR) -1) Return (@str) End
/ * Usage: Select, DOSTINCT WORKNO, DATE, DBO.F_KQLIST (Workno, [DATE]) from timecard where workno = '102337' and communic (char (8), [date], 112) = '20030814' * /
Leave it to everyone. In fact, it is not difficult to see who is more efficient. : D (My Practice: Abnormal Processing: Use the storage process, do it with the cursor, too lazy to optimize the Test Chen 2: Use the function, the original use of the cursor, not good, then change the function)
