To filter data Table1 based on Table2, help Please! 1

[RaffiqEddy]

Hello Expert!

I want to filter out Public Holiday from Employee’s attendance table.

Take note: Public Holiday differ from state to state

I’ve created 2 tables for this, they are as following:
1. PublicHoliday
2. EmpAttendance

Table “PublicHoliday” contain information as following:

No	State	FromDate	ToDate		Day	Description
1		1	01/01/2006	02/01/2006	2	New Year
2		1	07/01/2006	07/01/2006	1	Pub. Hol. 2
3		1	10/01/2006	10/01/2006	1	Pub. Hol. 3
4		1	21/01/2006	21/01/2006	1	Pub. Hol. 4
5		2	01/01/2006	01/01/2006	1	New Year
6		2	06/01/2006	06/01/2006	1	Pub. Hol. 5
7		2	11/01/2006	11/01/2006	1	Pub. Hol. 6

Table “EmpAttendance” contain information as following:

No	EmpNo	State	Date		LogIn	LogOut
1		A		1	01/01/2006	
2		A		1	02/01/2006	
3		A		1	03/01/2006	8:35	17:05
4		A		1	04/01/2006	9:00	17:30
5		A		1	05/01/2006
6		A		1	06/01/2006
7		B		2	01/01/2006	
8		B		2	02/01/2006	8:50	17:12
9		B		2	03/01/2006	8:55	17:35

Using a query, I want to produce result as following (excluding Public Holiday):

No	EmpNo	State	Date		LogIn	LogOut
3		A		1	03/01/2006	8:35	17:05
4		A		1	04/01/2006	9:00	17:30
5		A		1	05/01/2006
6		A		1	06/01/2006
8		B		2	02/01/2006	8:50	17:12
9		B		2	03/01/2006	8:55	17:35

TIA for your help

Regards.

 

[TwoOdd]

Have you thought about having only one date field in the PublicHoliday table? It would make the query much easier. Yes, this would create 2 records for the New Year holiday instead of one, but as stated above, it would make the query extremely easy.

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[Pampers]

I think so too. Like:

01/01 - new year
02/01 - new year
etc...

Pampers
You never too young to learn

 

[PHV]

You may try something like this:
SELECT E.No, E.EmpNo, E.State, E.Date, E.LogIn, E.LogOut
FROM EmpAttendance AS E
WHERE Not EXISTS (SELECT * FROM PublicHoliday AS P
WHERE P.State = E.State AND (E.Date Between P.FromDate And P.ToDate))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RaffiqEddy]

Thanks PHV,

It Works!

I Hope to contribute more STAR to you for the excellent guide..

TwoOdd & Pampers – Thanks for the idea, it's good but I have to skip your suggestion.

Take care,

Regards.