Query Is Required? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Query Is Required?

Author

Topic

majidbhutta
Starting Member

13 Posts

Posted - 2006-03-24 : 11:36:44

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)=DatePart(d,@Start_Time+3 days) for any EMpId]

And so on upto 7th day.

Now i m in need of an efficient query that returns me the required above result set.
NOTE
====

I am using Access 2000 database so i need a query or anything else that may give me the required output in Access database

Thnx in Advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources