Writing a query to find the following result

Employees Table:

EmpID EmpName

---

1 John Torres

2 Irina Williams

Payroll Week Table:

WeekID EmpID WeekStart WeekEnd

---

1 1 11-20-2011 11-26-2011

2 2 11-27-2011 12-03-2011

3 1 11-27-2011 12-03-2011

Employee Visits Table:

ID EmpID VisitDate StartTime EndTime Earningcode

---

1 1 11-20-2011 10:00 12:00 Sat-Sun1

2 1 11-21-2011 13:30 16:00 Mon-Fri1

3 1 11-22-2011 14:00 15:00 Mon-Fri1

4 1 11-24-2011 10:00 14:00 Mon-Fri1

5 1 11-25-2011 13:30 16:00 Mon-Fri1

6 1 11-26-2011 14:00 15:00 Sat-Sun1

7 2 11-27-2011 09:00 11:00 Sat-Sun1

8 2 11-28-2011 07:00 12:00 Mon-Fri1

9 2 11-29-2011 09:00 11:00 Mon-Fri1

10 2 12-03-2011 07:00 12:00 Sat-Sun1

Expected Results

RecordType EmpID EmpName WeekStart WeekEnd Earning code Hours

---

H 1 John Torres 11-20-2011 11-26-2011

D Sat-Sun1 3.00

D Mon-Fri1 10.00

H 2 Irina Williams 11-27-2011 12-03-2011

D Sat-Sun1 7.00

D Mon-Fri1 7.00