The following statement generates output of attendance records (from Honeywell based access control system) and Lotus Notes based workflow application pulled the information to publish the records.
SELECT * FROM
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date,
MIN(a.TIMESTAMP) AS First_in,
MAX(a.TIMESTAMP) AS Last_Out
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '29-apr-2014'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber = '1'
Recently, we have implemented Biometrics equipments at various locations and IN/OUT of an employee is getting stored in a separate Table namely "Timewatch_attendance", which has the following four fields:
EmpID : EmpID is unique in both the case
Now, I am facing challenges in joining these two to generate a single output file, which I can finally display through Lotus Notes application.
The query should check and display First_IN & FirstIn (MIN) and Last_Out and LastOut (Max) in both the table/view and generate the output accordingly.
Can you help me please.