Appointment Finder Query...TSQL Expert Required!

We are designing a booking system and have a list of appointments in a single table with the start and end time of the appointment.

Is there any way to write a query to return the available time gaps, I'm thinking here that we would need to generate a temporary table in the query and return the data from this table that doesn't join with data in the appointment table but I'm guessing and was wondering whether there's an easier way..

The table definition is

CREATE TABLE [dbo].[SALON_Appointment] (
[AppointmentID] [int] IDENTITY (1, 1) NOT NULL ,
[Subject] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
[StylistID] [int] NOT NULL ,
[Recurrency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstStartTime] [datetime] NOT NULL ,
[FirstEndTime] [datetime] NOT NULL ,
[UserID] [int] NOT NULL
) ON [PRIMARY]
GO

Thanks in advance

Hi

You don't say what version of SQL Server you are using and there are a number of pieces of information/constraints missing.

These question include:

What is the length of the working day - does midnight to 07:00 counts as free time? Does the system need to allow for breaks such as lunch time? You could book them as dummy appointments (probably need an appointment type field to allow them to be stripped out under certain circumstances). What is the minimum length for an appointment - is 09:30 to 09:35 a valid gap? What days do you need this for: All Days; Working Days; Days for which appointments exist.|||

I assume that you wana find the gap based on stylistID.

Code Snippet

SELECT A1.StylistID,

DATEDIFF(mi,A1.EndTime, A2.StartTime) AS Gap

FROM Appointment A1 JOIN Appointment A2 ON (A1.StylistID=A2.StylistID)

WHERE (A1.EndTime=

(SELECT MAX(A3.EndTime)

FROM Appointment A3

WHERE (A3.StylistID = A1.StylistID)

AND (A3.EndTime <= A2.StartTime)))

Hope this helps,

Paraclete

|||Hi,

Thanks for the reply.
I think I've worked a solution out which is similar to your method. I create a temporary and fill it with psuedo appointments, I then do a select which uses a sub query with NOT EXISTS to select items from the pseudo table that don't exist in the real appointment table and hence returns a list of available slots.

It looks like a little less code than your method but I've not tested it fully so I'm not sure whether its 100% yet.

Apply a long list of SQL Statements

I just received a long text file that contains all statements required to
create all necessary tables as well as inserting all the data into this
table
Using the Enterprise Manager how can I apply all these statements?
Thank you,
SamuelSamuel
> Using the Enterprise Manager how can I apply all these statements?
Why ? open a query analyzer and run this statement (modify it for your
needs)
EXEC master..xp_cmdshell
'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:O1$a3wzfGHA.2032@.TK2MSFTNGP02.phx.gbl...
>I just received a long text file that contains all statements required to
>create all necessary tables as well as inserting all the data into this
>table
> Using the Enterprise Manager how can I apply all these statements?
> Thank you,
> Samuel
>|||copy the info into query analyzer and execute it there.
"Samuel Shulman" wrote:
> I just received a long text file that contains all statements required to
> create all necessary tables as well as inserting all the data into this
> table
> Using the Enterprise Manager how can I apply all these statements?
> Thank you,
> Samuel
>
>|||On Wed, 24 May 2006 17:22:51 +0300, Uri Dimant wrote:
>Samuel
>> Using the Enterprise Manager how can I apply all these statements?
>Why ? open a query analyzer and run this statement (modify it for your
>needs)
>EXEC master..xp_cmdshell
>'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
Hi Uri,
Ir you're going to use osql.exe, why use Query Analyzer at all? Just
open a DOS prompt (or command window, or whatever it is called now).
For better ability to view the output, I'd recommend either adding an
output file to the osql command (using the -o parameter), or switching
to Quary Analyzer, copying in the text of the script, then hitting the
Execute button.
--
Hugo Kornelis, SQL Server MVP|||Hugo
> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
> open a DOS prompt (or command window, or whatever it is called now).
Don't you see people who was wrapping this command into a stored procedure
and run in within a job or whatever?
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:a0h972p5rpockl0n8v38sabjlbkg57ejls@.4ax.com...
> On Wed, 24 May 2006 17:22:51 +0300, Uri Dimant wrote:
>>Samuel
>> Using the Enterprise Manager how can I apply all these statements?
>>Why ? open a query analyzer and run this statement (modify it for your
>>needs)
>>EXEC master..xp_cmdshell
>>'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
> Hi Uri,
> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
> open a DOS prompt (or command window, or whatever it is called now).
> For better ability to view the output, I'd recommend either adding an
> output file to the osql command (using the -o parameter), or switching
> to Quary Analyzer, copying in the text of the script, then hitting the
> Execute button.
> --
> Hugo Kornelis, SQL Server MVP|||On Thu, 25 May 2006 09:43:06 +0300, Uri Dimant wrote:
>Hugo
>> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
>> open a DOS prompt (or command window, or whatever it is called now).
>Don't you see people who was wrapping this command into a stored procedure
>and run in within a job or whatever?
Hi Uri,
Since Samuel asked "how to apply these statements (...) using Enterprise
Manager", I figured he wanted to do it manually, not to schedule it.
Also, scripts that contain CREATE TABLE statements should normally run
just once.
--
Hugo Kornelis, SQL Server MVP

Apply a long list of SQL Statements

I just received a long text file that contains all statements required to
create all necessary tables as well as inserting all the data into this
table
Using the Enterprise Manager how can I apply all these statements?
Thank you,
SamuelSamuel
> Using the Enterprise Manager how can I apply all these statements?
Why ? open a query analyzer and run this statement (modify it for your
needs)
EXEC master..xp_cmdshell
'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:O1$a3wzfGHA.2032@.TK2MSFTNGP02.phx.gbl...
>I just received a long text file that contains all statements required to
>create all necessary tables as well as inserting all the data into this
>table
> Using the Enterprise Manager how can I apply all these statements?
> Thank you,
> Samuel
>|||copy the info into query analyzer and execute it there.
"Samuel Shulman" wrote:

> I just received a long text file that contains all statements required to
> create all necessary tables as well as inserting all the data into this
> table
> Using the Enterprise Manager how can I apply all these statements?
> Thank you,
> Samuel
>
>|||On Wed, 24 May 2006 17:22:51 +0300, Uri Dimant wrote:

>Samuel
>Why ? open a query analyzer and run this statement (modify it for your
>needs)
>EXEC master..xp_cmdshell
>'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
Hi Uri,
Ir you're going to use osql.exe, why use Query Analyzer at all? Just
open a DOS prompt (or command window, or whatever it is called now).
For better ability to view the output, I'd recommend either adding an
output file to the osql command (using the -o parameter), or switching
to Quary Analyzer, copying in the text of the script, then hitting the
Execute button.
Hugo Kornelis, SQL Server MVP|||Hugo
> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
> open a DOS prompt (or command window, or whatever it is called now).
Don't you see people who was wrapping this command into a stored procedure
and run in within a job or whatever?
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:a0h972p5rpockl0n8v38sabjlbkg57ejls@.
4ax.com...
> On Wed, 24 May 2006 17:22:51 +0300, Uri Dimant wrote:
>
> Hi Uri,
> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
> open a DOS prompt (or command window, or whatever it is called now).
> For better ability to view the output, I'd recommend either adding an
> output file to the osql command (using the -o parameter), or switching
> to Quary Analyzer, copying in the text of the script, then hitting the
> Execute button.
> --
> Hugo Kornelis, SQL Server MVP|||On Thu, 25 May 2006 09:43:06 +0300, Uri Dimant wrote:

>Hugo
>Don't you see people who was wrapping this command into a stored procedure
>and run in within a job or whatever?
Hi Uri,
Since Samuel asked "how to apply these statements (...) using Enterprise
Manager", I figured he wanted to do it manually, not to schedule it.
Also, scripts that contain CREATE TABLE statements should normally run
just once.
Hugo Kornelis, SQL Server MVP

Application Role and SQL Express (2005)

Hello,

Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)?

Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005

Thanks

John

Hi,

could you please show of these link in google which point out that pooling should be disabled ? You can enable this for sure in SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Here's one

http://solidqualitylearning.com/Blogs/dejan/archive/2006/11/10/3487.aspx

I did say, "still required", but perhaps I should have made it clear that most google searches get to SQL Server 2000.

I am having problems with pooling on...

If pooling is off, I execute sp_setapprole after every time I do an Open.

I assume with pooling on, I should execute it just once, the first time I open for the same connection string.

Thanks

John

|||

http://msdn2.microsoft.com/en-us/8xx3tyca(VS.80).aspx & http://databases.aspfaq.com/database/how-do-i-enable-or-disable-connection-pooling.html

FYI.

|||

If you want to use pooling with application roles, then you should use sp_unsetapprole to unset the approle before returning the connection to the pool. If you cannot enforce this, then you should disable pooling when using application roles. This holds for both SQL Server and SQL Server Express.

Thanks
Laurentiu

Application Role and SQL Express (2005)

Hello,

Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)?

Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005

Thanks

John

Hi,

could you please show of these link in google which point out that pooling should be disabled ? You can enable this for sure in SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

Here's one

http://solidqualitylearning.com/Blogs/dejan/archive/2006/11/10/3487.aspx

I did say, "still required", but perhaps I should have made it clear that most google searches get to SQL Server 2000.

I am having problems with pooling on...

If pooling is off, I execute sp_setapprole after every time I do an Open.

I assume with pooling on, I should execute it just once, the first time I open for the same connection string.

Thanks

John

|||

http://msdn2.microsoft.com/en-us/8xx3tyca(VS.80).aspx & http://databases.aspfaq.com/database/how-do-i-enable-or-disable-connection-pooling.html

FYI.

|||

If you want to use pooling with application roles, then you should use sp_unsetapprole to unset the approle before returning the connection to the pool. If you cannot enforce this, then you should disable pooling when using application roles. This holds for both SQL Server and SQL Server Express.

Thanks
Laurentiu

Application / User defined categories?

I have about 10 required (never to change) categories in my tbAccountCategories table, but after that, the user can add as many
as he/she wishes. I use an identity field to identify each record. The problem is I want to reserve the first 20 rows for the application and thereafter is for the user. Should I just create 20 rows with the category description field = 'Reserved' ?

Also, how do I reindex the identity fields that it cleans up gaps and starts back at 0?

Mike BI personally would not rely on Identy fields or any primary key maintaining an uninterupted sequential order. The maintenance is a pain in the a$$.

Have you thought about adding a flag field to designate which records are permanent? You could create your 20 reserved records with the flag set, and then only allow records without the flag set to be deleted.|||Originally posted by blindman
I personally would not rely on Identy fields or any primary key maintaining an uninterupted sequential order. The maintenance is a pain in the a$$.

Have you thought about adding a flag field to designate which records are permanent? You could create your 20 reserved records with the flag set, and then only allow records without the flag set to be deleted.

Good advice!

Mike B|||Originally posted by MikeB_2k4
I have about 10 required (never to change) categories in my tbAccountCategories table


Yeah right...never speak in absoultes..(unless it's on the rocks with a twist)

...but after that, the user can add as many
as he/she wishes. I use an identity field to identify each record.


AAAAAAAAAAAAAAAAAAAAAHHHHHHHHHHHHH

You've got an account code, no?

The problem is I want to reserve the first 20 rows for the application and thereafter is for the user. Should I just create 20 rows with the category description field = 'Reserved' ?

OK...I'm officially lost...do you just need a type field?

Also, how do I reindex the identity fields that it cleans up gaps and starts back at 0?

You don't...(the gap part), you can reset the seed though with DBCC REINDEX(EDIT: What a scrub...CHECKIDENT)..but are you sure you want to do that? What happens to all the existing rows? You'll run in to a dup key problem...

Cut and paste this to check it out...

But I think you need to rethingk what you're doing

USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY,Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
GO

SELECT * FROM myTable99
GO

DELETE FROM myTable99 WHERE Col1 = 2
GO

DBCC CHECKIDENT (myTable99,RESEED,0)
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Originally posted by Brett Kaiser
Yeah right...never speak in absoultes..(unless it's on the rocks with a twist)

ummmm, absolute!

You've got an account code, no?

Yes and they are unique :)

OK...I'm officially lost...do you just need a type field?

Famouse words of Ed McMahon on Johnny Carson show
"UhhhHuhhh, you are correct sir!"

You don't...(the gap part), you can reset the seed though with [s]DBCC REINDEX(EDIT: What a scrub...CHECKIDENT)..but are you sure you want to do that? What happens to all the existing rows? You'll run in to a dup key problem...

So it is best to just leave it the way it is?

Cut and paste this to check it out...

But I think you need to rethingk what you're doing

Which part, the reindex or the "adding categories"?

Thanks for the reply Brett

Mike