Click here to Skip to main content
15,789,776 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am having one doubt regarding the attendance , In my Table I have following Details like
ECode LogDate
C0001 2012-12-12 07:56:05.000
C0001 2012-12-12 17:47:02.000
E5890 2012-12-12 07:54:59.000
E5890 2012-12-12 08:23:23.000
E5890 2012-12-12 08:23:42.000
E5890 2012-12-12 08:30:10.000
E5890 2012-12-12 08:30:41.000
E5890 2012-12-12 08:31:28.000
E5890 2012-12-12 08:32:13.000
E5890 2012-12-12 08:35:52.000
E5890 2012-12-12 08:42:59.000
E5890 2012-12-12 14:08:26.000
E5890 2012-12-12 19:14:35.000
E5890 2012-12-12 19:19:53.000
E5890 2012-12-12 19:22:32.000
E6000 2012-12-12 17:32:32.000

In this I need the output like

Code Date IN Out Status
C0001 2012-12-12 07:56 17:47 P
E5890 2012-12-12 07:54 19:22 P
E6000 2012-12-12 17:32 I

Kindly Suggest How to do this,Thanks for your support.

Thanks & Regards,

Use this ........... :)
SELECT ECode , MIN(logdate) logdate, CASE WHEN substring(convert(varchar(20), MIN(logdate), 100), 18, 5) = 'AM' THEN 'P' ELSE 'I' END AS STATUS  FROM Stu_Log

ECode      logdate                 STATUS
---------- ----------------------- ------
C0001      2012-12-12 07:56:05.000 P
E5890      2012-12-12 07:54:59.000 P
E6000      2012-12-12 17:32:32.000 I

Share this answer
Balaji 30111978 10-Jan-13 6:53am    
Thanks for your quick reply.I need In and Out Time Separetely
Balaji 30111978 10-Jan-13 7:02am    
Its Ok , But I need "P" When there is In and out and Invalid when there is 1 in or 1 Out , I need In and Out time also , If there is more than Two Punches it should consider the Min value as In and the Max value as Out. Thanks for your UnderStanding.
Take this :)

SELECT ECode , MIN(logdate) [LOGIN], MAX(logdate) [LOGOUT], CASE WHEN MIN(logdate)<> MAX(logdate) THEN 'P' ELSE 'I' END AS STATUS  FROM Stu_Log

ECode      LOGIN                   LOGOUT                  STATUS
---------- ----------------------- ----------------------- ------
C0001      2012-12-12 07:56:05.000 2012-12-12 17:47:02.000 P
E5890      2012-12-12 07:54:59.000 2012-12-12 08:30:10.000 P
E6000      2012-12-12 17:32:32.000 2012-12-12 17:32:32.000 I

Share this answer
Balaji 30111978 10-Jan-13 8:16am    
Thanks Mr.Vijay for your swift reply , If its in 3rd shift he will comes in day1 and goes out in day2 , how it can be mentioned and also i need to take consecutive 'P' for n days excludes holidays and Sundays. It will be very helpful for me, Thanks for your understanding and expecting your reply.Sorry for asking too many questions.
Balaji 30111978 10-Jan-13 23:48pm    
For 1 day the status is coming correctly if i run the query for more than 1 day the status is showing as I. Kindly suggest
As per me you have to change your table design .... it should be like this ...

Create Table EmpLogin
(Id int ,
LoginDate date,
LoinTime time ,
Logout time )

At the time of log in one new row will be insert and Logout will be NULL, and for log out same row will be update with the out time .... :)

Regards ,
Share this answer
Balaji 30111978 11-Jan-13 6:07am    
Stu_Log is the table where the in and out time is getting downloaded from the swiping machine , how can i insert it to the table emplogin and also i dont want the same time to repeat in login or logout Ex (E6000 2012-12-12 17:32:32.000 2012-12-12 17:32:32.000 I) in this both in and out are same time.when there is 1 punch.Kindly advice. Other how can i automate the process to automatically insert values from stu_log to emplogin also can u pls suggest to generate the report for the entire month with 'P' and 'A' status. for the entire month and also need the list of ecode who are having status 'A' for Consecutive 'n' days which excludes Holdiays and Sundays (I will Create a table for Holdiays).If you suggest , it will be very helpful for me. Thanks in Advance for your valuable support.Pls help me out.
P.S Vijay 13-Jan-13 23:27pm    
can u send me the table design for both table ....
Balaji 30111978 18-Jan-13 0:20am    
Thanks for your support , I can give u the Table Design of the Original table that is StuLog fields are
LogID bigInt,LogDate Datetime,CardNr CardNr(24),Name nvarchar(200). U can design the other tables as per your , and give me the procedure and scripts.
Balaji 30111978 22-Jan-13 1:33am    
Dear Mr.Vijay, Any updates , can you pls help me out on this.
P.S Vijay 22-Jan-13 23:49pm    
ohhhh.... I am so sorry.... I was so busy last 3-4 days, So I didn't log in code project.

As per you requirement, I create a table for u ... plz have a look ..

Table Name : GMLogData
FieldsName Datatype
TransID bigInt Not Null
CardNo nvarchar(24)Not Null
LogIn_ID Int NotNull
LogOut_ID Int Null
Remarks ntext Null
Business workflow
System shall find out in GMLogData(New Table) using CardNo wether LogOut_ID is null or not
If Logout_ID is null then System will be update Logout_Id.
Other wise insert a new row and update TransID ,Login_ID (LogOut_Id will be null).

User Workflow
When Employee comes in, then the TransID and LogIn_ID will be insert and when Employee leave from the office at that time, LogOut_ID will be update for the same row.
Remarks : In the case of Manual (If employee forgot his access card). So you can put a manual entry and write the remark.

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