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.

Retrieving a datetime with a time of midnight (from a typical datetime)

Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.

A frequent method used is to (1) convert it to varchar using CONVERT with the 101 flavor and then (2) re-convert it back to datetime. Here are some examples:

Code Snippet

select convert(datetime, convert(varchar, getdate(), 101))
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

select dateadd(day, datediff (day, 0, getdate()), 0)
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

select cast(floor(cast(getdate() as float)) as datetime)
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

|||

Another way:

Code Snippet

select dateadd(d, datediff(d,0,[PostedDate]),0)

|||I used the dateadd method both of you suggested and it worked perfectly. Thank you very much.

Retrieve REDO Information

I need a way to retrieve INSERT, DELETE and UPDATE information from SQL
Server, which needs to include basically a redo statement such as the actual
INSERT and DELETE statement and an UPDATE statement with the new (set
values) and original values. I know SQL Server has log files and there are
third party applications that an retrieve this information, however some of
them have problems getting the correct or even getting any UPDATE
information, plus I do not need a UI or any of their features, just the
information.
I need to get the INSERT, DELETE and UPDATE information, new and old values
using C++ code. These UI applications are of no use. All I need is the
information.
Oracle has Logminer where you can query the log information based on
operation type and timestamp as well as other useful parameters. DB2 can
even send this INERT, DELETE and UPDATE information to a message queue.
I need a way to get this information from SQL Server without using database
triggers but using C++ code. It would be nice to be able to query for this
information, similar to Oracle's implementation. Can anyone point me in the
right direction? Thanks in advance for any help you can provide.
Charles ParkerThe only commands you have to work with are DBCC LOG and fn_dblog. However,
these doesn't return
information in any type of clear text, and there is not information on how t
o decode the information
they return (or even if they contains what you need).
You can talk to MS and ask them for information on how to do this, which wou
ld put you on the same
level as the companies that wrote these applications, but there is no public
ly available API or
command for getting "meaningful" information from the transaction log.
Consider putting a request at http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Parker" <charles.parker@.whamtect.com> wrote in message
news:Othcy1qcGHA.3888@.TK2MSFTNGP02.phx.gbl...
>I need a way to retrieve INSERT, DELETE and UPDATE information from SQL Ser
ver, which needs to
>include basically a redo statement such as the actual INSERT and DELETE sta
tement and an UPDATE
>statement with the new (set values) and original values. I know SQL Server
has log files and there
>are third party applications that an retrieve this information, however som
e of them have problems
>getting the correct or even getting any UPDATE information, plus I do not n
eed a UI or any of their
>features, just the information.
>
> I need to get the INSERT, DELETE and UPDATE information, new and old value
s using C++ code. These
> UI applications are of no use. All I need is the information.
>
> Oracle has Logminer where you can query the log information based on opera
tion type and timestamp
> as well as other useful parameters. DB2 can even send this INERT, DELETE a
nd UPDATE information to
> a message queue.
>
> I need a way to get this information from SQL Server without using databas
e triggers but using C++
> code. It would be nice to be able to query for this information, similar t
o Oracle's
> implementation. Can anyone point me in the right direction? Thanks in adva
nce for any help you can
> provide.
>
> Charles Parker
>|||Tibor,
Thanks for the quick reply. I will try the feedback link you suggested below
but I do not understand why Microsoft let Oracle and DB2 get ahead of them
in terms of this feature. Could it be in SQL Server 2005?
Charles...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HAfv8qcGHA.536@.TK2MSFTNGP02.phx.gbl...
> The only commands you have to work with are DBCC LOG and fn_dblog.
> However, these doesn't return information in any type of clear text, and
> there is not information on how to decode the information they return (or
> even if they contains what you need).
> You can talk to MS and ask them for information on how to do this, which
> would put you on the same level as the companies that wrote these
> applications, but there is no publicly available API or command for
> getting "meaningful" information from the transaction log.
> Consider putting a request at
> http://lab.msdn.microsoft.com/productfeedback/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Charles Parker" <charles.parker@.whamtect.com> wrote in message
> news:Othcy1qcGHA.3888@.TK2MSFTNGP02.phx.gbl...
>|||> Could it be in SQL Server 2005?
Unfortunately, no. I guess that there haven't been enough customer request t
o warrant spending time
on doing this compared to other feature request MS has on the product. But o
f course, only people
sitting in the product planning meetings can say for sure... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Parker" <charles.parker@.whamtect.com> wrote in message
news:%23rNuBhscGHA.4312@.TK2MSFTNGP05.phx.gbl...
> Tibor,
> Thanks for the quick reply. I will try the feedback link you suggested bel
ow but I do not
> understand why Microsoft let Oracle and DB2 get ahead of them in terms of
this feature. Could it
> be in SQL Server 2005?
> Charles...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23HAfv8qcGHA.536@.TK2MSFTNGP02.phx.gbl...
>

Retrieve REDO Information

I need a way to retrieve INSERT, DELETE and UPDATE information from SQL
Server, which needs to include basically a redo statement such as the actual
INSERT and DELETE statement and an UPDATE statement with the new (set
values) and original values. I know SQL Server has log files and there are
third party applications that an retrieve this information, however some of
them have problems getting the correct or even getting any UPDATE
information, plus I do not need a UI or any of their features, just the
information.
I need to get the INSERT, DELETE and UPDATE information, new and old values
using C++ code. These UI applications are of no use. All I need is the
information.
Oracle has Logminer where you can query the log information based on
operation type and timestamp as well as other useful parameters. DB2 can
even send this information to a message queue.
I need a way to get this information from SQL Server without using database
triggers but using C++ code. It would be nice to be able to query for this
information, similar to Oracle's implementation. Can anyone point me in the
right direction? Thanks in advance for any help you can provide.
Charles ParkerSee my reply in .programming.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Parker" <charles.parker@.whamtect.com> wrote in message
news:e0f%23N2qcGHA.3936@.TK2MSFTNGP05.phx.gbl...
>I need a way to retrieve INSERT, DELETE and UPDATE information from SQL Ser
ver, which needs to
>include basically a redo statement such as the actual INSERT and DELETE sta
tement and an UPDATE
>statement with the new (set values) and original values. I know SQL Server
has log files and there
>are third party applications that an retrieve this information, however som
e of them have problems
>getting the correct or even getting any UPDATE information, plus I do not n
eed a UI or any of their
>features, just the information.
>
> I need to get the INSERT, DELETE and UPDATE information, new and old value
s using C++ code. These
> UI applications are of no use. All I need is the information.
>
> Oracle has Logminer where you can query the log information based on opera
tion type and timestamp
> as well as other useful parameters. DB2 can even send this information to
a message queue.
>
> I need a way to get this information from SQL Server without using databas
e triggers but using C++
> code. It would be nice to be able to query for this information, similar t
o Oracle's
> implementation. Can anyone point me in the right direction? Thanks in adva
nce for any help you can
> provide.
>
> Charles Parker
>

Retrieve REDO Information

I need a way to retrieve INSERT, DELETE and UPDATE information from SQL
Server, which needs to include basically a redo statement such as the actual
INSERT and DELETE statement and an UPDATE statement with the new (set
values) and original values. I know SQL Server has log files and there are
third party applications that an retrieve this information, however some of
them have problems getting the correct or even getting any UPDATE
information, plus I do not need a UI or any of their features, just the
information.
I need to get the INSERT, DELETE and UPDATE information, new and old values
using C++ code. These UI applications are of no use. All I need is the
information.
Oracle has Logminer where you can query the log information based on
operation type and timestamp as well as other useful parameters. DB2 can
even send this information to a message queue.
I need a way to get this information from SQL Server without using database
triggers but using C++ code. It would be nice to be able to query for this
information, similar to Oracle's implementation. Can anyone point me in the
right direction? Thanks in advance for any help you can provide.
Charles ParkerSee my reply in .programming.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Charles Parker" <charles.parker@.whamtect.com> wrote in message
news:e0f%23N2qcGHA.3936@.TK2MSFTNGP05.phx.gbl...
>I need a way to retrieve INSERT, DELETE and UPDATE information from SQL Server, which needs to
>include basically a redo statement such as the actual INSERT and DELETE statement and an UPDATE
>statement with the new (set values) and original values. I know SQL Server has log files and there
>are third party applications that an retrieve this information, however some of them have problems
>getting the correct or even getting any UPDATE information, plus I do not need a UI or any of their
>features, just the information.
>
> I need to get the INSERT, DELETE and UPDATE information, new and old values using C++ code. These
> UI applications are of no use. All I need is the information.
>
> Oracle has Logminer where you can query the log information based on operation type and timestamp
> as well as other useful parameters. DB2 can even send this information to a message queue.
>
> I need a way to get this information from SQL Server without using database triggers but using C++
> code. It would be nice to be able to query for this information, similar to Oracle's
> implementation. Can anyone point me in the right direction? Thanks in advance for any help you can
> provide.
>
> Charles Parker
>

retrieve from db then write to text file

basically i am trying to create a program wherein after saving a new transaction to the sql database, the fields saved will be retrieved and then written to a text file.

i read a thread here which is similar to what i am trying to do but it was in xml format..

hope someone anwers me...i really need help!

thanks!

I'm not sure what the difference is between the thread you mentioned and what you want to do. I would expect that the challenging thing is to retrieve the fields that were just saved. Once you have the information, changing it to the appropriate format is potentially tedious, but not difficult.

Do you have a reference to the thread?