问题描述
大家好,
我有三张桌子:
桌子
1.检查:
i)EMPID
ii)CHECKTIME
2.EMP:
i)EMPID
3.TIMECARD:
i)TimeOUT
注意:CHECKINOUT(EMPID )= EMP(EMPID)
CHECKINOUT所有打卡时间都来自CHECKTIME栏目。
所以我想从chechinout表中选择所有EMPID(存在于EMP表中)一天(例如:2013年2月2日)。
之后我想选择最后一个打击给定日期的每位员工的列CHECKTIME打卡时间。
然后我想将最后一次打卡时间插入表TIMECARD列名超时。
如果有任何疑问请注释...评论。
Hi all,
I have three table :
TABLE
1. CHECKINOUT:
i)EMPID
ii)CHECKTIME
2.EMP:
i)EMPID
3.TIMECARD:
i)TimeOUT
Note : CHECKINOUT(EMPID)=EMP(EMPID)
CHECKINOUT where all the punch time will come and sit in Column CHECKTIME.
So i want to select the all EMPID(Which is present in EMP Table)from chechinout table For one day(eg: 2/2/2013).
After this i Want to select the Last punch time from Column CHECKTIME of every employee who punched for the given date.
Then i want to insert the last punch time to table TIMECARD Column name Timeout.
If any doubt plz ...comment.
推荐答案
declare @CHECKINOUT table
(
EMPID int,
CHECKTIME datetime
)
declare @EMP table
(
EMPID int
)
--select * from @EMP;
declare @TIMECARD table
(
EMPID int,
outTime datetime
)
insert into @EMP values(1)
insert into @EMP values(2)
insert into @EMP values(3)
insert into @CHECKINOUT values (1,'10-07-2013 10:00 am')
insert into @CHECKINOUT values (1,'10-07-2013 05:00 pm')
insert into @CHECKINOUT values (2,'10-07-2013 10:00 am')
insert into @CHECKINOUT values (2,'10-07-2013 05:30 pm')
insert into @CHECKINOUT values (1,'11-07-2013 10:00 am')
insert into @CHECKINOUT values (1,'11-07-2013 03:00 pm')
insert into @CHECKINOUT values (3,'11-07-2013 09:00 am')
insert into @CHECKINOUT values (3,'11-07-2013 05:30 pm')
select c.* from @CHECKINOUT c inner join @EMP e on e.EMPID=c.EMPID
Where convert(date,CHECKTIME,112) = '10-07-2013'
insert into @TIMECARD
select c.EMPID,max(c.CHECKTIME )
from @CHECKINOUT c inner join @EMP e on e.EMPID=c.EMPID
Where convert(date,CHECKTIME,112) = '10-07-2013'
group by c.EMPID
select * from @TIMECARD
这篇关于用于从所选记录中选择最大数据的SQL命令。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!