Hello gurus, am new to sql queries, but need to query some results from a view.
i have this columns in vw_SchoolLogs view;
EmployeeName datatype = varchar, AttendanceDate datatype = DateTime, Status datatype = Varchar(Present or absent).
The objective is to select employeeName that are present for a particular date, and if any employeeName is absent for that date, the query should also include result of the last date the employeeeName was present.
What you want to do is get the maximum date for each employee where the status was present - so your first criteria is selecting all records with the present status. Second criteria is records with an attendance date less than or equal to your search date (2011-06-01 in your example)
What you could then do is use the MAX aggregate function to get the maximum date for each employee. For those present on the given date it will be that date, for all others it will be the maximum date value less than your given date where the status was present.