Query Is Required?
-
Beolw is the tables Structure and dummy data used in Access database Employee ======== EmpId(Pk int) EName(Text) DeptId(Fk int) 1 Rashid 1 2 Kashif 2 3 Black 1 4 White 2 5 Brown 2 Department ========== DeptId(Pk int) DName(Text) 1 Labor 2 Secret 3 Production 4 Purchase Attendance ========== AttId(Pk Int) EmpId(Fk int) In/Out_Time(Datetime) Direction 1 2 2/5/2006 6:37:02 PM 1 // 1 for IN, 0 For OUT 2 2 2/5/2006 8:37:02 PM 0 3 2 2/5/2006 8:50:00 PM 1 4 1 2/5/2006 8:51:00 PM 1 5 1 2/5/2006 9:37:02 PM 0 6 2 2/5/2006 9:40:02 PM 0 7 3 2/5/2006 6:37:04 PM 1 8 4 2/5/2006 6:37:06 PM 1 9 5 2/5/2006 6:37:08 PM 1 10 5 2/5/2006 8:40:02 PM 0 11 4 2/5/2006 8:40:40 PM 0 12 3 2/5/2006 8:40:50 PM 0 13 2 3/5/2006 6:37:02 PM 1 14 2 3/5/2006 8:37:02 PM 0 15 2 3/5/2006 8:50:00 PM 1 16 1 3/5/2006 8:51:00 PM 1 17 1 3/5/2006 9:37:02 PM 0 18 2 3/5/2006 9:40:02 PM 0 19 3 3/5/2006 6:37:04 PM 1 20 4 3/5/2006 6:37:06 PM 1 21 5 3/5/2006 6:37:08 PM 1 22 5 3/5/2006 8:40:02 PM 0 23 4 3/5/2006 8:40:40 PM 0 24 3 3/5/2006 8:40:50 PM 0 And so on the attendance data for whole month. What i need is an optimized efficient query that takes a Start_date(DateTime) as an input parameter and returns me the Attendance data of all Employees for one week. Means starting from startdate and ending at startdate+6 days in the following format EName DName IstDay 2ndDay 3rdDay 4thDay 5thDay 6thDay 7thDay Where as IstDay to 7thDay Columns contains the value for total Working hrs at that day as IstDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}- {Value of In/Out_Time Column for Min(AttId) When Direction=1} Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time) for any EMpId] 2ndDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}- {Value of In/Out_Time Column for Min(AttId) When Direction=1} Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time+1 Day) for any EMpId] 3rdDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}- {Value of In/Out_Time Column for Min(AttId) When Direction=1} Where DatePart(d,In/Out_Time)=DatePart(d,@Start_Time+2 Days) for any EMpId] 4thDay = [{Value of In/Out_Time Column for Max(AttId) When Direction=0}- {Value of In/Out_Time Column for Min(AttId) When Direction=1} Where DatePart(d,In/Out_Time)=Dat