CODEWITHSUNDEEP
c#asp.netsql-servergridview
Fhd.ashraf
Fhd.ashraf

Reputation: 537

Left Join to produce One Row for Each Day

I have two Tables one. One Employee_List( that has all the list of employee) and Attendance( That keeps a track of the attendance) I am Using a Grid to Display the Employee Information and a RadioButtonList that marks him Absent or Present.

SELECT Hcc_Emp_Detail.Hcc_Emp_Id, Emp_Attendance_Main.Emp_Attendance_Action, Emp_Attendance_Main.Emp_Attendance_Date
FROM  Emp_Attendance_Main INNER JOIN
               Hcc_Emp_Detail ON Emp_Attendance_Main.Emp_Attendance_Emp_Id = Hcc_Emp_Detail.Hcc_Emp_Id

The issue I am facing is If I mark the attendance for an employee on 26th ..and on 27th the grid is not empty. THat particular grid should be empty everyday.enter image description here and the join statement should show attendance of the already marked and keep the others empty. And there should be a fresh grid everyday. Thank you.

enter image description here

This is the Empty grid of all the emp on 26th before taking attendance. enter image description here

This is how it looks after taking attendance. enter image description here

Now when i login on the 27th to take the attendance the grid should turn empty with the list of all the employees.

Upvotes: 0

Views: 69

Answers (1)

attila
attila

Reputation: 2219

If I understand what you are trying to do, just left join to emp attendance main with a criteria of date - 

SELECT hed.Hcc_Emp_Id, eam.Emp_Attendance_Action, eam.Emp_Attendance_Date
FROM  Hcc_Emp_Detail hed
LEFT JOIN Emp_Attendance_Main eam
    ON hed.Hcc_Emp_Id = eam.Emp_Attendance_Emp_Id 
      AND eam.Emp_Attendance_date=cast(getdate() as date)

Upvotes: 1

Related Questions