I got a problem in my SELECT Statement:
SELECT DISTINCT IF(ISNULL(a.LOGIN), 'rdc', '') AS LOGIN_CLASS, IF(ISNULL(a.LOGOUT), 'rdc', '') AS LOGOUT_CLASS, a.EMP_NO, a.LOGIN, a.LOGOUT, CONCAT(LNAME, ',' , FNAME, ' ', MI, '.') AS FULLNAME FROM attendance.employee_attendance AS a JOIN hris.employment em ON (a.EMP_NO = em.EMP_NO AND em.STATUS = 'Reg Operatives') JOIN hris.personal AS p ON p.EMP_ID = em.EMP_ID WHERE a.LOGIN BETWEEN '2012-01-16' AND '2012-02-01' OR a.LOGOUT BETWEEN '2012-01-16' AND '2012-02-01' OR ISNULL(a.LOGIN) OR ISNULL(a.LOGOUT) ORDER BY FULLNAME, a.LOGIN
I attach the 2 image, first the sample attendance which I get the data base on my select query, and the sample get attendance which is the output.
I notice that in the output from my query, the attendance with 2012-02-01 that has no logout did not displayed and also the NO LOGIN and LOGOUT .
when i tried to select from 2012-01-16 t0 2012-02-02, the date 2012-02-01 was displayed., but I need to range it in 2012-01-16 to 2012-02-01 only.