日期员工出勤 [英] Datewise Employee attendance

问题描述

需要帮助来实施员工出勤表。目前正在招聘员工，即



查询

  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  那么 ' < span class =code-string> P'  ELSE  '  A'  END   AS 状态
 
  FROM  Emp_setting 
  LEFT  < span class =code-keyword> 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 

结果

 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出席于2014-05-23。



现在我要列表2014-05-23在场的员工。



请帮助



谢谢。

解决方案

  SELECT  c.First_name + c.Middle_name + c.last_name < span class =code-keyword> AS  employees ename，
 b.Device_Person_id，a.Dept_Id，Date1  as  DateTimeLastSeen，
 x.logDate  As  DatePresent，
  CASE   WHEN  b.Device_Person_id  IS   NOT   NULL  那么 '  P'  ELSE  < span class =code-string>'  A'  END   AS  status 
  FROM  Emp_setting 
  LEFT   OUTER   JOIN （ SELECT  Device_Person_id，MAX （logDateTime） AS  D. ate1 
  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年5月23日; 

hi,

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

query

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

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.

解决方案

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

这篇关于日期员工出勤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！