Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
Hi,
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,
Balaji.D
Posted 10-Jan-13 0:20am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Use this ........... Smile | :)
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
GROUP BY ECode
OUTPUT
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
Regards,
Vijay
  Permalink  
v2
Comments
Member 3301978 at 10-Jan-13 6:53am
   
Thanks for your quick reply.I need In and Out Time Separetely
Member 3301978 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Take this Smile | :)
 
SELECT ECode , MIN(logdate) [LOGIN], MAX(logdate) [LOGOUT], CASE WHEN MIN(logdate)<> MAX(logdate) THEN 'P' ELSE 'I' END AS STATUS  FROM Stu_Log
GROUP BY ECode
 
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
 
Regards,
Vijay
  Permalink  
Comments
Member 3301978 at 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.
Member 3301978 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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 .... Smile | :)
 
Regards ,
Vijay
  Permalink  
Comments
Member 3301978 at 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 at 13-Jan-13 23:27pm
   
can u send me the table design for both table ....
Member 3301978 at 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.
Member 3301978 at 22-Jan-13 1:33am
   
Dear Mr.Vijay, Any updates , can you pls help me out on this.
P.S Vijay at 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.
Member 3301978 at 24-Jan-13 4:27am
   
Dear Mr.Vijay,
Thanks for your support , Can I have your contact no. Bcoz I need to explain it in detail. Bcoz the logfile I cannot able to do any changes in that as its receiving datas from the Swiping device. So I need based on those I told you earlier. Thanks for your understanding and support.
P.S Vijay at 6-Feb-13 4:05am
   
My Mob No is 9388365646
gprawin at 2-Apr-14 6:43am
   
Hi vijay me also same problem... please help me...
 
http://www.codeproject.com/Questions/753217/how-to-create-Pivot-table
gprawin at 8-Apr-14 2:52am
   
vijay can u send u r mail id please........

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

  Print Answers RSS
0 OriginalGriff 328
1 Gihan Liyanage 292
2 Sergey Alexandrovich Kryukov 275
3 ClimerChinna 222
4 Abhinav S 187
0 Sergey Alexandrovich Kryukov 8,213
1 OriginalGriff 7,089
2 CPallini 2,598
3 Richard MacCutchan 1,925
4 Abhinav S 1,778


Advertise | Privacy | Mobile
Web02 | 2.8.140826.1 | Last Updated 11 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100