Click here to Skip to main content
15,904,023 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';
 
Share this answer
 

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