Sql statement Help

SQL Apprentice Question
Hi I'm trying to create a isBooked statement

What I have is a db with:


Date, StartTime,EndTime


I currently run two SQL statements:
1) "SELECT Count(PupilID) FROM TblLessons WHERE " _
& "Date =@GetDate and Time between @GetTime and @GetTimeEnd"
2) Dim StrSqlEndTime As String = "SELECT Count(PupilID) FROM TblLessons
WHERE " _
& "Date= @GetDate and TimeEnd between @GetTime and @GetTimeEnd "


I may be going it the wrong direction so please help out.
If I have already booked a appointment at say 10:00 to 12.30
Using the above statements misses a appointment if I try to added a new for
10.15 to 11.15, but catches 09:15 to 10:15 I understand why but don't know
how to correct.
I guess I'm going about this all wrong so any help would be great.


Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

What you did post was wrong; there is no TIME data type in SQL Server.


CREATE TABLE AppointmentSchedule
(pupil_id INTEGER NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (pupil_id, start_time),
..);


We never put "tbl-" as a prefix on a data element name in SQL. What
you posted is not a SQL statement -- it is in some host language for
dynamci execution. Use a stored procedure in the database instead.



>> I guess I'm going about this all wrong so any help would be great. <<


What are you trying to do. You never told us. Do you need to delete
appointsments? Do you need to add appointments? Can they overlap?