Find Date between two Dates
-
I created two tables 1) Attendance 2) Leave 1) Attendance table structure EmpID int Att_Date Datetime 2) Leave table Structure EmpID int LeaveID int LeaveFrom Datetime LeaveTo Datetime While taking the Attendance i want to check whether this employee taken any leave on that day(Attendance day). How can i write a sql for this. Eg: Attendance Date: 30/03/2012 Leave taken this Employee is 02/03/2012 - 05/03/2012 28/03/2012 - 31/03/2012 How can i write a sql to check this without any cursor.
-
I created two tables 1) Attendance 2) Leave 1) Attendance table structure EmpID int Att_Date Datetime 2) Leave table Structure EmpID int LeaveID int LeaveFrom Datetime LeaveTo Datetime While taking the Attendance i want to check whether this employee taken any leave on that day(Attendance day). How can i write a sql for this. Eg: Attendance Date: 30/03/2012 Leave taken this Employee is 02/03/2012 - 05/03/2012 28/03/2012 - 31/03/2012 How can i write a sql to check this without any cursor.
I'm not 100% sure what you're after, I'll assume you want to find any records in the "leave" table for the attendance table.
SELECT a1.EmpID, COUNT(l1.LeaveID) as LeaveRecords FROM
Attendance a1 LEFT JOIN
Leave l1 ON l1.EmpID = a1.EmpID AND a1.Att_Date BETWEEN l1.LeaveFrom AND l1.LeaveTo
GROUP BY a1.EmpIDDoes that suit your needs? The above would return a list of emp attendances and the no of leave records for that attendance date.
-
I'm not 100% sure what you're after, I'll assume you want to find any records in the "leave" table for the attendance table.
SELECT a1.EmpID, COUNT(l1.LeaveID) as LeaveRecords FROM
Attendance a1 LEFT JOIN
Leave l1 ON l1.EmpID = a1.EmpID AND a1.Att_Date BETWEEN l1.LeaveFrom AND l1.LeaveTo
GROUP BY a1.EmpIDDoes that suit your needs? The above would return a list of emp attendances and the no of leave records for that attendance date.