sql - How to determine two different aggregate dates on employee attendance data? -

sql - How to determine two different aggregate dates on employee attendance data? -

i need help implement employee attendance sheet. presently having employee attendance i.e

query:

select c.first_name + c.middle_name + c.last_name employeename, b.device_person_id,a.dept_id,date1, case when b.device_person_id not null 'p' else 'a' end status emp_setting left outer bring together (select device_person_id, max(logdatetime) date1 tempdevicelogs grouping device_person_id) b on a.personal_id = b.device_person_id left outer bring together persons_profile c on c.pesonal_id=a.personal_id

result:

employeename device_person_id dept_id date1 status mehaboob null 4 null uday null 26 null shankrayya null 10 null basavaraj null 24 null bhimappa 5 10 2014-05-23 14:14:00.000 p

i.e. employeename bhimappa nowadays on 2014-05-23.

now want list of employees nowadays on 2014-05-23.

please help?

assuming logdatetime indicates presence of employee, , given current derived table returns max() of field (presumably lastly time employee detected) need bring together tempdevicelogs table filtering. filtering @ same time inner join, viz:

... inner bring together (select device_person_id tempdevicelogs logdatetime >= '2014-05-23' , logdatetime < '2014-05-24') x on x.device_person_id = a.personal_id

edit given want select date well, i'm assuming want parameterize / utilize range. , making yet assumption sqlserver version beingness >= 2008, cast datetime date , grouping it:

select c.first_name + c.middle_name + c.last_name employeename, b.device_person_id,a.dept_id, date1 datetimelastseen, x.logdate datepresent, case when b.device_person_id not null 'p' else 'a' end status emp_setting left outer bring together (select device_person_id, max(logdatetime) date1 tempdevicelogs grouping device_person_id) b on a.personal_id = b.device_person_id left outer bring together persons_profile c on c.pesonal_id=a.personal_id inner bring together (select device_person_id, cast(logdatetime date) logdate tempdevicelogs grouping device_person_id, cast(logdatetime date)) x on x.device_person_id = a.personal_id logdate between '2014-05-01' , '2014-05-23';

this done distinct. if have before version of sqlserver, utilize hack this obtain date part of datetime (in select + group)

sql sql-server