Datewise Employee attendance

Question

1.00/5 (1 vote)

See more:

hi,

Need help to implement employee attendance sheet.Presently am having employee attendance i.e

query

SQL

SELECT c.First_name + c.Middle_name + c.last_name AS employeename,b.Device_Person_id,a.Dept_Id,Date1, 
CASE WHEN b.Device_Person_id IS NOT NULL THEN 'P' ELSE 'A' END AS status

FROM Emp_setting a 
LEFT OUTER JOIN (SELECT Device_Person_id, MAX(logDateTime) AS Date1 FROM tempDeviceLogs GROUP BY Device_Person_id) b
ON a.personal_id = b.Device_Person_id 
LEFT OUTER JOIN persons_profile c ON c.pesonal_id=a.personal_id

Result

SQL

employeename      Device_person_id  dept_id   date1             status


MEHABOOB	         NULL	      4	        NULL	            A
UDAY	                 NULL	      26	NULL	            A
SHANKRAYYA 	         NULL	      10	NULL	            A
BASAVARAJ	         NULL	      24	NULL	            A
BHIMAPPA	          5    	      10       2014-05-23 14:14:00.000 P

Employeename BHIMAPPA is present on 2014-05-23 .

NOW I want the list of employees who is present on 2014-05-23.

please help

Thanks.

Posted 17-Jun-14 20:36pm

Danuuu

Add a Solution

1 solution

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)

Danuuu · Accepted Answer · 2014-06-18T00:24:00

SQL

SELECT c.First_name + c.Middle_name + c.last_name AS employeename,
      b.Device_Person_id,a.Dept_Id, Date1 as DateTimeLastSeen,
      x.logDate As DatePresent,
      CASE WHEN b.Device_Person_id IS NOT NULL THEN 'P' ELSE 'A' END AS status
FROM Emp_setting a
  LEFT OUTER JOIN (SELECT Device_Person_id, MAX(logDateTime) AS Date1
                   FROM tempDeviceLogs
                   GROUP BY Device_Person_id) b
     ON a.personal_id = b.Device_Person_id
  LEFT OUTER JOIN persons_profile c
     ON c.pesonal_id=a.personal_id
  INNER JOIN (SELECT Device_Person_id, CAST(logDateTime AS DATE) as logDate
              FROM tempDeviceLogs
              GROUP BY Device_Person_id, CAST(logDateTime AS DATE)) x
  ON x.Device_Person_id = a.personal_id
WHERE
     logDate ='2014-05-23';