22,209
社区成员
发帖
与我相关
我的任务
分享
create table kaoqing(
kaoqingid int identity(1,1),
username varchar(9) not null,
userip varchar(16) not null,
ktime datetime default(getdate())
)
create table banci(
banciid int,
name varchar(12) not null,
stime time(7) not null,---上班时间08:00.下班时间17:00
xtime time(7) not null,---一天2次卡,中午休息不打卡.
)
create table kao(
kaoid int identity(1,1),
username varchar(12),
userip varchar(16),
ktime datetime,
kaostate varchar(15)---考勤状态,分为迟到,正常,早退.
)
create table kaoqing(
kaoqingid int identity(1,1),
username varchar(9) not null,--网页自动获取并插入数据
userip varchar(16) not null,--网页自动获取并插入数据
ktime datetime default(getdate()),
kaostate varchar(15)---考勤状态,分为迟到,正常,早退.由数据库判断
)
create table banci(
banciid int,
name varchar(12) not null,
stime time(7) not null,---上班时间08:00.下班时间17:00
xtime time(7) not null,---一天2次卡,中午休息不打卡.
)
SELECT
CASE
WHEN CONVERT(varchar(100), ktime, 24)<=CONVERT(varchar(10),stime,120)and CONVERT(varchar(100), ktime, 24) between '07:00:00' And CONVERT(varchar(10),stime,120) THEN '正常上班'
WHEN CONVERT(varchar(100), ktime, 24)<CONVERT(varchar(10),xtime,120)and CONVERT(varchar(100), ktime, 24) between CONVERT(varchar(10),stime,120) And CONVERT(varchar(10),xtime,120) THEN '迟到'
WHEN CONVERT(varchar(100), ktime, 24)>CONVERT(varchar(10),xtime,120) THEN '正常下班'
ELSE null END kaoqing,kaoqingid,stime,ktime,xtime,username FROM kaoqing,banci where kaoqing.username=banci.name