Click here to Skip to main content
15,299,443 members
Please Sign up or sign in to vote.
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

1 solution

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';
   

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