Check if date exists

[JP SIngh]

Hi All

We have two tables EmpProfile and holidaysrequest tables.

EmpProfile has fields
EmpNo
Firstname
Lastname etc

HolidayRequests has fields
EmpNo
FromDate
ToDate


holiday requests table stores the dates employees have booked as holidays.

I want to write a query which will be used by ASP page to display everyone
who have not booked 29th - 31st Dec as days off.

I cannot figure out how to write this query , can anyone help.

I am using Access 2000.

 

[Steven Burn]

Do While Not rst.eof
If Not rst("Date")=TheDate and Not rst("Date")=TheOtherDate Then
'// response.write the other records (or whichever your wanting)
End If
rst.MoveNext
Loop

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[Aaron [SQL Server MVP]]

ugh, return the whole recordset and do the comparison in ASP? Better to do
the work in the database...

 

[Bob Barrows [MVP]]

Hi All

We have two tables EmpProfile and holidaysrequest tables.

EmpProfile has fields
EmpNo
Firstname
Lastname etc

HolidayRequests has fields
EmpNo
FromDate
ToDate


holiday requests table stores the dates employees have booked as
holidays.

I want to write a query which will be used by ASP page to display
everyone who have not booked 29th - 31st Dec as days off.

The exact time period? Or any portion of that time period? For example, do
you want to see employees who have booked Dec. 24th through Dec. 30th? Or
Dec. 29th - Jan. 2? Or Dec. 28th - Dec. 28th?

I cannot figure out how to write this query , can anyone help.

I am using Access 2000.

You will need to use a subquery for this. This will take care of the exact
time period problem:

SELECT e.EmpNo, e.FirstName
FROM EmpProfile AS e LEFT JOIN
(Select EmpNo, FromDate,ToDate FROM HolidayRequests
WHERE FromDate<=#2004-12-29# AND ToDate>=#2004-12-31#) AS q
ON e.EmpNo = q.EmpNo
WHERE q.EmpNo Is Null

This will handle the inclusive time period problem:

SELECT e.EmpNo, e.FirstName
FROM EmpProfile AS e LEFT JOIN
(Select EmpNo, FromDate,ToDate FROM HolidayRequests
WHERE FromDate<=#12/31/2004# AND ToDate>=#12/29/2004#) AS q
ON e.EmpNo = q.EmpNo
WHERE q.EmpNo Is Null

HTH,
Bob Barrows

 

[Aaron [SQL Server MVP]]

Who have not booked ALL THREE days, or who have not booked any of 29th,
30th, 31st?

For the former, assuming they are booked consecutively (e.g. all the days
are booked in a single row):

SELECT e.EmpNo, e.FirstName, e.LastName
FROM EmpProfile e
WHERE e.EmpNo NOT IN
(SELECT EmpNo FROM HolidayRequests
WHERE FromDate <= #2004-12-29#
AND ToDate >= #2004-12-31#)

For the latter:

SELECT e.EmpNo, e.FirstName, e.LastName
FROM EmpProfile e
WHERE e.EmpNo NOT IN
(SELECT EmpNo FROM HolidayRequests
WHERE FromDate BETWEEN #2004-12-29# AND #2004-12-31#
OR ToDate BETWEEN #2004-12-29# AND #2004-12-31#)

You could do this with a left outer join but I'm not sure how well Access
behaves.

If these queries don't provide the results you need, you need to better
define the requirement, and give us sample data and desired results.

 

[Bob Barrows [MVP]]

You will need to use a subquery for this. This will take care of the
exact time period problem:

SELECT e.EmpNo, e.FirstName
FROM EmpProfile AS e LEFT JOIN
(Select EmpNo, FromDate,ToDate FROM HolidayRequests
WHERE FromDate<=#2004-12-29# AND ToDate>=#2004-12-31#) AS q
ON e.EmpNo = q.EmpNo
WHERE q.EmpNo Is Null

correction:
WHERE FromDate=#2004-12-29# AND ToDate=#2004-12-31#) AS q

Bob Barrows

 

[Steven Burn]

I didn't think it was a good idea/possible to use "Not" clauses in the
database string?

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

 

[Steven Burn]

Ignore my last reply (just noticed your reply to the OP's post ;o))

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!