sqlserver考勤表的显示,该如何处理_Sql Server

sqlserver考勤表的显示
我要做一个考勤记录表,显示的样式如下

姓名 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |11|12|13|14|15|16|17|18|19|20|

张三 |到 |迟到0.5h|到 |放假|早退1h|

我数据库中记录的字段如下

姓名 | 到 | 迟到 | 早退 | 旷工 | 放假
张三 |0 | 0.5 | 0 | 0 | 0
李四 | 1 | 0 | 0 | 0 | 0
王五 | 1 | 0 | 0 | 0 | 0

到字段是bit类型
迟到早退旷工放假是 decimal(18,2)类型的

这是创建数据库的SQL

SQL code

if exists (select * from sysobjects where id = OBJECT_ID('[ManualSign]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [ManualSign]CREATE TABLE [ManualSign] ([ID] [int]  IDENTITY (1, 1)  NOT NULL,[UserId] [varchar]  (50) NOT NULL,[Dao] [bit]  NULL DEFAULT (0),[ChiDao] [decimal]  (18,2) NULL,[ZaoTui] [decimal]  (18,2) NULL,[KuangGong] [decimal]  (18,2) NULL,[QingJia] [decimal]  (18,2) NULL DEFAULT (0),[FangJia] [decimal]  (18,2) NULL DEFAULT (0),[LrUserId] [varchar]  (50) NOT NULL,[LrDate] [smalldatetime]  NOT NULL DEFAULT (getdate()),[AddDate] [smalldatetime]  NOT NULL DEFAULT (getdate()),[Remarks] [varchar]  (200) NULL)ALTER TABLE [ManualSign] WITH NOCHECK ADD  CONSTRAINT [PK_ManualSign] PRIMARY KEY  NONCLUSTERED ( [ID] )SET IDENTITY_INSERT [ManualSign] ONINSERT [ManualSign] ([ID],[UserId],[Dao],[ChiDao],[ZaoTui],[KuangGong],[QingJia],[FangJia],[LrUserId],[LrDate],[AddDate]) VALUES ( 3,N'admin',1,0.00,0.00,0.00,0.00,0.00,N'admin',N'2011-12-21 0:00:00',N'2011-12-21 14:55:00')INSERT [ManualSign] ([ID],[UserId],[Dao],[ChiDao],[ZaoTui],[KuangGong],[QingJia],[FangJia],[LrUserId],[LrDate],[AddDate]) VALUES ( 4,N'admin',1,0.00,0.00,0.00,0.00,0.00,N'admin',N'2011-12-23 0:00:00',N'2011-12-23 0:36:00')SET IDENTITY_INSERT [ManualSign] OFF

------解决方案--------------------
表的字段设计有冗余，迟到、早退、请假等为什么不单独用一个字段表示，如下所示：

SQL code

CREATE TABLE [ManualSign] ([ID] [int]  IDENTITY (1, 1)  NOT NULL,[UserId] [varchar]  (50) NOT NULL,[Type] [varchar]    (10) NOT NULL,[Attendance] [varchar]    (20) NOT NULL,[LrUserId] [varchar]  (50) NOT NULL,[LrDate] [smalldatetime]  NOT NULL DEFAULT (getdate()),[AddDate] [smalldatetime]  NOT NULL DEFAULT (getdate()),[Remarks] [varchar]  (200) NULL)INSERT [ManualSign] ([ID],[UserId],[Type],[Attendance],[LrUserId],[LrDate],[AddDate]) VALUES ( 3,N'admin','ChiDao',0.5,N'admin',N'2011-12-21 0:00:00',N'2011-12-21 14:55:00')INSERT [ManualSign] ([ID],[UserId],[Type],[Attendance],[LrUserId],[LrDate],[AddDate]) VALUES ( 3,N'admin','KuangGong',2.0,N'admin',N'2011-12-21 0:00:00',N'2011-12-21 14:55:00')
------解决方案--------------------
探讨
我是考虑到可能会出现多种情况的:
既迟到又早退,或者请了半天假,迟到1小时 这些情况都有可能出现.这样一个字段就无法表示了呀

------解决方案--------------------
到是什么意思
------解决方案--------------------
你这个算是考勤记录表？
考勤的话，一般是只记录签到和签退时间，当然考勤编号或者考勤人是必须的。

确认考勤规则，多班次的考虑排版，然后根据打卡签到记录生成相应的报表。最后在添加不正常签到数据处理。然后你现在需要的，判断迟到，早退这些，就很随意了，想要什么报表就有什么。

你上面这直接是报表了。