Click here to Skip to main content
15,917,940 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 11-Jul-13 19:56pm
v2

1 solution

Try this code.

SQL
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 
 
Share this answer
 
v2
Comments
Nawab Ahmad 12-Jul-13 2:40am    
Thanks Arun..
It is working fine..but one problem is there that checktime contain both date and time
'1/19/2012 18:59' so want to take only max time of that day.
I tried with only date but no data will display.
ArunRajendra 12-Jul-13 2:49am    
I have modified the code. Try the update query? If it works dont forget to select answered :)
Nawab Ahmad 12-Jul-13 2:58am    
getting this error:

Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
ArunRajendra 12-Jul-13 3:04am    
I guess you are using access. I dont have access to verify the query.
Nawab Ahmad 12-Jul-13 3:38am    
it is not working because u don't specify the timecard column name that is Timeout.
I tried but showing many unwanted errors.
plz help.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900