Scheduling Database - Design Help!

I need to develop a scheduling app and am having trouble with the
database
design. I can easily design a table hold appointments with start and
finish
times, but I always have an issue when it comes time to searching for
free
time.

The search examples:
Find the first available appointment in September
Find the first afternoon appointment
etc...

Should appointments be linked similar to a linked list? Should I create
a
row for each 5 or 10 or 15 minute slice of the day for every day and
then
just search for null in a 'used' field? This could grow way to fast.

If you need a more specific example to understand I can provide that,
but I
wanted to keep this as short as possible.

If anyone has experience designing a scheduling DB then please post
your
expoeriences.

ThanksOn 18 Sep 2005 22:20:03 -0700, Trevor.D.Matthews@.gmail.com wrote:

>I need to develop a scheduling app and am having trouble with the
>database
>design. I can easily design a table hold appointments with start and
>finish
>times, but I always have an issue when it comes time to searching for
>free
>time.
>The search examples:
>Find the first available appointment in September
>Find the first afternoon appointment
>etc...
>Should appointments be linked similar to a linked list? Should I create
>a
>row for each 5 or 10 or 15 minute slice of the day for every day and
>then
>just search for null in a 'used' field? This could grow way to fast.
>If you need a more specific example to understand I can provide that,
>but I
>wanted to keep this as short as possible.
>If anyone has experience designing a scheduling DB then please post
>your
>expoeriences.
>Thanks

Hi Trevor,

Using pre-allocated slots would bloat your database (though Daniel's
idea would limit this somewhat), and at the same time, it would limit
the freedom of the user to make appointments the way he wants to, to the
granularity of your pre-allocated slots.

Here's an alternative:
CREATE TABLE Schedule
(PersonID int NOT NULL REFERENCES (Persons),
StartTime smalldatetime NOT NULL,
EndTime smalldatetime NOT NULL, -- but see below!
-- other columns,
PRIMARY KEY (PersonID, StartTime),
UNIQUE (PersonID, EndTime),
CHECK (EndTime > StartTime),
)
You'd also need to ensure that there are no overlapping intervals, but
that can't be done in a CHECK constraint - you'll ened a trigger to
verify that business rule.

With this design, you can go two ways:

a) Store only the appointments. If there are no intervals that start
before time Y and end after time X, then the time interval from X to Y
is available for appointments.
This approach makes the processes for adding, changing and removing
appointments easy, but makes searching for available time somewhat
harder, as you have to search for absence of rows.

b) Change the nullability of EndTime to allow NULLs. NULL will represent
"eternity". Define a code to represent available time. Give each person
one special starting row: StartTime is the earliest datetime your
application will allow; EndTime is NULL; row marked as "available time".
When making the first appointment, the end time in this first row is
changed to the start time of the appointment, a row is added for the
appointment and an extra row is inserted to makr the time from the end
of the appointment to eternity (NULL) as available.
This approach makes the processes for adding, changing and removing
appointments harder (think about the combinations: an appointment in the
middle of available time has to be treated differently from an
appointment that immediately follow the previous appointment, that's
followed by another appointment, or even both. When appointments get
removed, changed, or shortened, the time that is now available again has
to be collapsed with adjacent avaialble time. Etc etc), but makes
searching for available time easier, as each time will always be part of
exactly one time slot in the Schedule table.
Note: To be complete and fail-safe, you'll also have to implement checks
(in trigger code) to ensure that there are no overlaps and no gaps
between a person's rows in the schedule).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much to everyone for the input. I'll try out a couple of the
ideas and post back my results in case anyone wants to know how it
played out.

Thanks again,
Trevor

Trevor.D.Matthews@.gmail.com wrote:
> I need to develop a scheduling app and am having trouble with the
> database
> design. I can easily design a table hold appointments with start and
> finish
> times, but I always have an issue when it comes time to searching for
> free
> time.
> The search examples:
> Find the first available appointment in September
> Find the first afternoon appointment
> etc...
> Should appointments be linked similar to a linked list? Should I create
> a
> row for each 5 or 10 or 15 minute slice of the day for every day and
> then
> just search for null in a 'used' field? This could grow way to fast.
> If you need a more specific example to understand I can provide that,
> but I
> wanted to keep this as short as possible.
> If anyone has experience designing a scheduling DB then please post
> your
> expoeriences.
> Thanks

Scheduler UTC problem

Hi all,

I'm having trouble to implement an "scheduler" in a stored procedure with UTC date.

In one table (ScheduleCfg) I have one field that indicates which days of the week my banner must be exhibited, separated by commas (e.g. 0,5,6)
In my table "Schedule", I have a datetime field that indicates which is the next time that the banner must be shown.
(To explain: I have a process which runs every hour to republicate the banners and recalculate the next exhibition hour).

My SP gets the "Last Display" datetime and sums one hour... If the day still the same, then the new value of "Next Display" is this value.
But if it changes the day, then I'll have to verify if the new day's dayofweek number matches with the tables...

Until this everything works fine... But all my problem is: UTC TIME!

My application is globalizated, so many countries can use it. All the dates stored in my database must be UTC, so here's my problem:

When I sum one hour, I'm using the UTC date. So the "turn of the day" occurs in a different moment. For example, in Brazil the TimeZone is -3 hours, so when the day changes it will be 9 o'clock in Brazil. (because it's 0h UTC, so the Brazil's local time will be 9h PM of the day before...)

Anyone can help me?
I've been thinking about it for weeks and I couldn't find a good solution...

Thanks in advance.

This should work. I've used the difference between the GetUTCDate and GetDate functions to get the difference between the local timezone and UTC. Here is my suggested approach:

Code Snippet

--This variable contains the last saved date

Declare @.LastDisplay as Datetime

--Retrieve the value from the 'Schedule' table

--Retrieve the number of hours difference between the local timezone and UTC

Declare @.HourDiff as Int

Select @.HourDiff = DateDiff(hour, GetUTCDate(), GetDate())

--Calculate the next schedule date taking into consideration the timezone difference and adding an hour

Declare @.NextDisplay as Datetime

Set @.NextDisplay = DateAdd(hour, 1 + @.HourDiff, @.LastDisplay)

Select @.NextDisplay

--Check the difference between the last date (in UTC) and the new date (local time)

If DateDiff(day, @.LastDisplay, @.NextDisplay) = 1

Begin

--The day has changed

End

Else

Begin

-- The day has not changed

End

The DateDiff at the end, compares the last date the schedule ran which is saved in UTC with the new calculated date which is in local time. If the difference is not 0 then this is a new date and you can perform the logic that you want.

I hope this answers your question.

Best regards,

Sami Samir

|||Hmmm. Good idea..
I'll try to pass a parameter with the time difference, since my SQL Server does not know which country is calling..

Thank you!!!!

Scheduled DTS import and Delete Query

Hi there,
I am new to SQL Server, we are running SQL Server 7
I am having some trouble with creating a delete query.
Basically, i have developed a Access database that exports data to a .csv
file, to be imported to SQL server via a scheduled DTS package.
Most of the .csv files will only have new data to ammend to the SQL tables
which is fine doing the above.
But some of the .csv files have the entire data dump of the Access table and
needs to imported over the current data in the SQL table.
What i was thinking was to make a Query/View to delete the data in the
relevant table and then call the DTS package.
I have no idea how to do this - View? Stored Procedure?
But ideally it would delete the data and then call the DTS package, all in
one function.....
Also, would it then be possible to schedule/automate this service?
Thanks for any help
Regards
GuyOK
Reimporting all the data is fine if you have
A. No constraints reliant on the data
B. Your quantity of data is not too large
Personally I would do things like this
1. Have a working table in a staging area to hold each CSV.
2. Import each CSV into their respective staging table
3. Use TSQL statements to compare the working table data with that is in
your destination tables. By comparing Key values you can determine whether
the Data in the real tables requires
1. Adding
2. Updating
3. Deleteing.
As for scheduling then yes you can do this using SQL Server Agent and a Job.
Be careful to read this though
http://support.microsoft.com/?kbid=269074
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Guy Hocking" <guy.hocking@.bradflack.com> wrote in message
news:O6KLUlnsDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I am new to SQL Server, we are running SQL Server 7
> I am having some trouble with creating a delete query.
> Basically, i have developed a Access database that exports data to a .csv
> file, to be imported to SQL server via a scheduled DTS package.
> Most of the .csv files will only have new data to ammend to the SQL tables
> which is fine doing the above.
> But some of the .csv files have the entire data dump of the Access table
and
> needs to imported over the current data in the SQL table.
> What i was thinking was to make a Query/View to delete the data in the
> relevant table and then call the DTS package.
> I have no idea how to do this - View? Stored Procedure?
> But ideally it would delete the data and then call the DTS package, all in
> one function.....
> Also, would it then be possible to schedule/automate this service?
> Thanks for any help
> Regards
> Guy
>|||In article <O6KLUlnsDHA.2244@.TK2MSFTNGP09.phx.gbl>, Guy Hocking
<guy.hocking@.bradflack.com> writes
>Hi there,
>I am new to SQL Server, we are running SQL Server 7
>I am having some trouble with creating a delete query.
>Basically, i have developed a Access database that exports data to a .csv
>file, to be imported to SQL server via a scheduled DTS package.
>Most of the .csv files will only have new data to ammend to the SQL tables
>which is fine doing the above.
>But some of the .csv files have the entire data dump of the Access table and
>needs to imported over the current data in the SQL table.
>What i was thinking was to make a Query/View to delete the data in the
>relevant table and then call the DTS package.
>I have no idea how to do this - View? Stored Procedure?
>But ideally it would delete the data and then call the DTS package, all in
>one function.....
>Also, would it then be possible to schedule/automate this service?
>Thanks for any help
>Regards
>Guy
>
You can use the Execute SQL Task to run a Delete statement on your
table. Just link this with workflow to run before your the export
(DataPump) task. You will need to open the package manually to add this,
if still using the Wizard only.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org|||Thanks for that Allan,
I dont think the amount of data isnt too much (how much is too much?), and i
would rather to all the guinea-pig work in Access. The data is currently
exported into another table in Access with the same naming convention and
design as its SQL equivalent, then the data is exporeted to CSV, once done
the data is tagged in access so it is not exported again.
However, some of the tables will be very different as the data will change
frequently, it seems alot easier to re-import as its only 30-40k of records,
nothing monsterous and it doesnt take an age as iv tested it.
This difficulty is i have no idea where to write the query? view, stored
procedure, i really dont know........i cant seem to create a "delete"
function view, it only seems to do the SELECT function.
I am very new to SQL, so i apoligise for my ignorance
Guy
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:Or4GN9nsDHA.536@.tk2msftngp13.phx.gbl...
> OK
> Reimporting all the data is fine if you have
> A. No constraints reliant on the data
> B. Your quantity of data is not too large
> Personally I would do things like this
> 1. Have a working table in a staging area to hold each CSV.
> 2. Import each CSV into their respective staging table
> 3. Use TSQL statements to compare the working table data with that is in
> your destination tables. By comparing Key values you can determine
whether
> the Data in the real tables requires
> 1. Adding
> 2. Updating
> 3. Deleteing.
> As for scheduling then yes you can do this using SQL Server Agent and a
Job.
> Be careful to read this though
> http://support.microsoft.com/?kbid=269074
>
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Guy Hocking" <guy.hocking@.bradflack.com> wrote in message
> news:O6KLUlnsDHA.2244@.TK2MSFTNGP09.phx.gbl...
> > Hi there,
> >
> > I am new to SQL Server, we are running SQL Server 7
> > I am having some trouble with creating a delete query.
> >
> > Basically, i have developed a Access database that exports data to a
.csv
> > file, to be imported to SQL server via a scheduled DTS package.
> > Most of the .csv files will only have new data to ammend to the SQL
tables
> > which is fine doing the above.
> > But some of the .csv files have the entire data dump of the Access table
> and
> > needs to imported over the current data in the SQL table.
> >
> > What i was thinking was to make a Query/View to delete the data in the
> > relevant table and then call the DTS package.
> > I have no idea how to do this - View? Stored Procedure?
> > But ideally it would delete the data and then call the DTS package, all
in
> > one function.....
> >
> > Also, would it then be possible to schedule/automate this service?
> >
> > Thanks for any help
> >
> > Regards
> >
> > Guy
> >
> >
>|||ok allsorted, thanks ever so much 4 your help
Kind regards
guy
"Darren Green" <darren.green@.reply-to-newsgroup-only.uk.com> wrote in
message news:HF+FI9EP4ew$Ewl9@.sqldts.com...
> In article <O6KLUlnsDHA.2244@.TK2MSFTNGP09.phx.gbl>, Guy Hocking
> <guy.hocking@.bradflack.com> writes
> >Hi there,
> >
> >I am new to SQL Server, we are running SQL Server 7
> >I am having some trouble with creating a delete query.
> >
> >Basically, i have developed a Access database that exports data to a .csv
> >file, to be imported to SQL server via a scheduled DTS package.
> >Most of the .csv files will only have new data to ammend to the SQL
tables
> >which is fine doing the above.
> >But some of the .csv files have the entire data dump of the Access table
and
> >needs to imported over the current data in the SQL table.
> >
> >What i was thinking was to make a Query/View to delete the data in the
> >relevant table and then call the DTS package.
> >I have no idea how to do this - View? Stored Procedure?
> >But ideally it would delete the data and then call the DTS package, all
in
> >one function.....
> >
> >Also, would it then be possible to schedule/automate this service?
> >
> >Thanks for any help
> >
> >Regards
> >
> >Guy
> >
> You can use the Execute SQL Task to run a Delete statement on your
> table. Just link this with workflow to run before your the export
> (DataPump) task. You will need to open the package manually to add this,
> if still using the Wizard only.
> --
> Darren Green (SQL Server MVP)
> DTS - http://www.sqldts.com
> PASS - the definitive, global community for SQL Server professionals
> http://www.sqlpass.org
>|||OK
Too much is if it takes too long. Too long is business driven.
In my solution I would advocate inserting ALL records into the staging area
tables and then using ExecuteSQL tasks to do the
INSERT, UPDATE, DELETE queries.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Guy Hocking" <guy.hocking@.bradflack.com> wrote in message
news:O4KsyCosDHA.2208@.TK2MSFTNGP10.phx.gbl...
> Thanks for that Allan,
> I dont think the amount of data isnt too much (how much is too much?), and
i
> would rather to all the guinea-pig work in Access. The data is currently
> exported into another table in Access with the same naming convention and
> design as its SQL equivalent, then the data is exporeted to CSV, once done
> the data is tagged in access so it is not exported again.
> However, some of the tables will be very different as the data will change
> frequently, it seems alot easier to re-import as its only 30-40k of
records,
> nothing monsterous and it doesnt take an age as iv tested it.
> This difficulty is i have no idea where to write the query? view, stored
> procedure, i really dont know........i cant seem to create a "delete"
> function view, it only seems to do the SELECT function.
> I am very new to SQL, so i apoligise for my ignorance
> Guy
>
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:Or4GN9nsDHA.536@.tk2msftngp13.phx.gbl...
> > OK
> >
> > Reimporting all the data is fine if you have
> >
> > A. No constraints reliant on the data
> > B. Your quantity of data is not too large
> >
> > Personally I would do things like this
> >
> > 1. Have a working table in a staging area to hold each CSV.
> > 2. Import each CSV into their respective staging table
> > 3. Use TSQL statements to compare the working table data with that is
in
> > your destination tables. By comparing Key values you can determine
> whether
> > the Data in the real tables requires
> >
> > 1. Adding
> > 2. Updating
> > 3. Deleteing.
> >
> > As for scheduling then yes you can do this using SQL Server Agent and a
> Job.
> > Be careful to read this though
> >
> > http://support.microsoft.com/?kbid=269074
> >
> >
> >
> >
> > --
> >
> > --
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> > "Guy Hocking" <guy.hocking@.bradflack.com> wrote in message
> > news:O6KLUlnsDHA.2244@.TK2MSFTNGP09.phx.gbl...
> > > Hi there,
> > >
> > > I am new to SQL Server, we are running SQL Server 7
> > > I am having some trouble with creating a delete query.
> > >
> > > Basically, i have developed a Access database that exports data to a
> .csv
> > > file, to be imported to SQL server via a scheduled DTS package.
> > > Most of the .csv files will only have new data to ammend to the SQL
> tables
> > > which is fine doing the above.
> > > But some of the .csv files have the entire data dump of the Access
table
> > and
> > > needs to imported over the current data in the SQL table.
> > >
> > > What i was thinking was to make a Query/View to delete the data in the
> > > relevant table and then call the DTS package.
> > > I have no idea how to do this - View? Stored Procedure?
> > > But ideally it would delete the data and then call the DTS package,
all
> in
> > > one function.....
> > >
> > > Also, would it then be possible to schedule/automate this service?
> > >
> > > Thanks for any help
> > >
> > > Regards
> > >
> > > Guy
> > >
> > >
> >
> >
>

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
Grant
Check the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
GrantCheck the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
GrantCheck the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.