Return Available appointments

I am upgrading an application that another developer wrote. Basically, it i
s
an application used to display appointments / request appointments. The new
request is to display available appointments. That is where I need help in
figuring out how to return available time slots.
Here is what the table looks like(Again, I did not write it or develop it so
far)
CREATE TABLE [dbo].[Meetings] (
[appt_ID] [int] IDENTITY (1, 1) NOT NULL ,
[appt_StartDT] [datetime] NOT NULL ,
[appt_RequesterName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[appt_RequestEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[appt_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[appt_Room] [int] NOT NULL ,
[appt_EndDT] [datetime] NOT NULL ,
[appt_State] [int] NOT NULL ,
[appt_attendees] [int] NULL
) ON [PRIMARY]
Some sample data with column names next to the values
apptID - 1177
appt_StartDt - 11/21/2003 8:00:00 AM
appt_requesterName - User Name
appt_requesterEmail- Email@.company.com
appt_Name - Acme Electric
appt_Room - 11
appt_EndDT - 11/21/2003 12:00:00 PM
appt_State - 1
appt_attendees - 8
Currently, I display all the appointments on the web page reading the
database. With the new change, I would like to be able to see the available
appointments
so users could enter in a date range or appt_room to see what is available
and then make appointments. My problem is the query to return the avaiable
appointments.
Please let me know if you have any questions.
TIA.Hi,
Welcome to use MSDN Managed Newsgroup!
Would you please give me a expected data row for "avaiable appointments"?
How to define this? For example, given a date range, list all the meetings
in this data range?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
Thanks for the response. Yes, you are right, for a given range of days,
list all available appointments.
Here is more information.
The user would have the choice to enter day/ range of days to check for
available appointments. So if the user would want to make an appointment
2
ws from now, then the user would select that w and the result would be
all available appointments in that w. Appointments can be a full hour, 1
5
mins or a complete day.
For example:
Available appointments for 11/30/2005 are
1. Room1 9:30 AM to 10:30 PM
2. Room 1 1:45 PM to 2:15 PM
3. Room 2 9:00 AM to 10:00 AM
If the user selected a range(11/30/2005 - 12/1/2005) , then
Available appointments for 11/30/2005 are:
1. Room 1 time
2. Room 3 time
3. Room 4 time
Avaliable appointments for 12/1/2005 are:
1. Room 1 time
Hope this is helpful. Please let me know if you need more information or
have any questions.
TIA.
"Michael Cheng [MSFT]" wrote:

> Hi,
> Welcome to use MSDN Managed Newsgroup!
> Would you please give me a expected data row for "avaiable appointments"?
> How to define this? For example, given a date range, list all the meetings
> in this data range?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi,
Thanks for your clarification.
What's your concern? Below is a sample about how to select all available
for two ws later (2005-11-28 ~ 2005-12-4)
SELECT [appt_Room] , [appt_StartDT], [appt_EndDT]
FROM Meetings
WHERE @.P1 <= [appt_StartDT] AND @.P2 +1 > [appt_EndDT]
--@.P1, @.P2 indicate the inputing data ranges
I guess the key point might be you will have to make a "sub table" for each
date. If I have misunderstood your concern, please feel free to point it
out.
With T-SQL statements only, I am afraid it is not possible to list by date
like below
Available appointments for 11/30/2005 are
..
Avaliable appointments for 12/1/2005 are:
..
To accomplish this, you should impliment this with .NET in your asp.net
pages or winform.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
The SQL below will return the appt_room, start and end dates from the
meetings table whether or not if the appointment is available.
I am expecting the result to be only of the available appointments and
then have a break down by day.
Could you please clarify on the sub table for each date thing that you
mentioned.
Hope this is been clear.
Thanks.
"Michael Cheng [MSFT]" wrote:

> Hi,
> Thanks for your clarification.
> What's your concern? Below is a sample about how to select all available
> for two ws later (2005-11-28 ~ 2005-12-4)
> SELECT [appt_Room] , [appt_StartDT], [appt_EndDT]
> FROM Meetings
> WHERE @.P1 <= [appt_StartDT] AND @.P2 +1 > [appt_EndDT]
> --@.P1, @.P2 indicate the inputing data ranges
> I guess the key point might be you will have to make a "sub table" for eac
h
> date. If I have misunderstood your concern, please feel free to point it
> out.
> With T-SQL statements only, I am afraid it is not possible to list by date
> like below
> Available appointments for 11/30/2005 are
> ...
> Avaliable appointments for 12/1/2005 are:
> ...
> To accomplish this, you should impliment this with .NET in your asp.net
> pages or winform.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi,
I am sorry that I get .
Could you provide the the sample data and the expected results? I am not
sure for what situation the the appointment will be not available.
If you want to list the appointments day by day, you will have to implement
this with business logic instead of in T-SQL only.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.