Attendance Related Query

Question

0.00/5 (No votes)

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

Balaji 30111978

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

P.S Vijay · Accepted Answer · 2013-01-10T00:49:00

Solution 1

Use this ........... :)

SQL

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

CSS

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

Posted 10-Jan-13 0:49am

P.S Vijay

Updated 10-Jan-13 0:50am

v2

Comments

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.

P.S Vijay · Accepted Answer · 2013-01-10T01:34:00

Solution 2

Take this :)

SQL

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

CSS

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

Posted 10-Jan-13 1:34am

P.S Vijay

Comments

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

P.S Vijay · Accepted Answer · 2013-01-10T23:05:00

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 .... :)

Regards ,
Vijay

Posted 10-Jan-13 23:05pm

P.S Vijay

Comments

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.

Balaji 30111978 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 6-Feb-13 4:05am

My Mob No is 9388365646

akhil.krish 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

akhil.krish 8-Apr-14 2:52am

vijay can u send u r mail id please........