generate sequence - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

generate sequence

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-11 : 15:46:33

Ok here goes. I have a table that has all the times from
6:00AM to 9:00PM each time has it's own field checked on or
off for available. For duplicate times you need to add another row.

I need to be able to spit out a list that shows
something like this:

times availabe to schedule into:

8:00(2)
8:30(1)
9:00(2)
10:00(1)

My tabledef is listed below: Any suggestions????

--****************************************

CREATE TABLE [dbo].[tblclosedef] (
[CLOID] [int] IDENTITY (1, 1) NOT NULL ,
= [namedesc] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hourinterval] [int] NULL ,
[sun] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mon] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tue] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wed] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[thr] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fri] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sat] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[t600] [int] NULL ,
[t615] [int] NULL ,
[t630] [int] NULL ,
[t645] [int] NULL ,
[t700] [int] NULL ,
[t715] [int] NULL ,
[t730] [int] NULL ,
[t745] [int] NULL ,
[t800] [int] NULL ,
[t815] [int] NULL ,
[t830] [int] NULL ,
[t845] [int] NULL ,
[t900] [int] NULL ,
[t915] [int] NULL ,
[t930] [int] NULL ,
[t945] [int] NULL ,
[t1000] [int] NULL ,
[t1015] [int] NULL ,
[t1030] [int] NULL ,
[t1045] [int] NULL ,
[t1100] [int] NULL ,
[t1115] [int] NULL ,
[t1130] [int] NULL ,
[t1145] [int] NULL ,
[t1200] [int] NULL ,
[t1215] [int] NULL ,
[t1230] [int] NULL ,
[t1245] [int] NULL ,
[t1300] [int] NULL ,
[t1315] [int] NULL ,
[t1330] [int] NULL ,
[t1345] [int] NULL ,
[t1400] [int] NULL ,
[t1415] [int] NULL ,
[t1430] [int] NULL ,
[t1445] [int] NULL ,
[t1500] [int] NULL ,
[t1515] [int] NULL ,
[t1530] [int] NULL ,
[t1545] [int] NULL ,
[t1600] [int] NULL ,
[t1615] [int] NULL ,
[t1630] [int] NULL ,
[t1645] [int] NULL ,
[t1700] [int] NULL ,
[t1715] [int] NULL ,
[t1730] [int] NULL ,
[t1745] [int] NULL ,
[t1800] [int] NULL ,
[t1815] [int] NULL ,
[t1830] [int] NULL ,
[t1845] [int] NULL ,
[t1900] [int] NULL ,
[t1915] [int] NULL ,
[t1930] [int] NULL ,
[t1945] [int] NULL ,
[t2000] [int] NULL ,
[t2015] [int] NULL ,
[t2030] [int] NULL ,
[t2045] [int] NULL
GO

--***********************

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-11 : 16:02:33

Yes, ABSOLUTELY DO NOT use this table definition to store appointments or availability! You will go insane trying to report on this, not to mention that it's nowhere near a relational structure (see here http://www.sqlteam.com/redir.asp?ItemID=7022 for some definitions)

See if this helps:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11079

A better way to go about this is to store an appointment. It has a start date & time and an end date/time. If you need to fill in gaps to show availability, you can use the technique listed here:

http://www.sqlteam.com/item.asp?ItemID=3332

Something like this is INFINITELY easier to set up and use, and is more flexible in case you need to expand or limit the time or date values for appointments.

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-11 : 16:07:10

This table is just the availability table.

The actual scheduling is done into another table. Each office must profile how many slots and times on each day of the week they want to make available to schedule. This is thier office profile that the manipulate to set up the customer view.

slow down to move faster...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-11 : 16:12:28

Even if it shows availability instead of appointments, a structure like that will cuase you nothing but grief in the long run.

If you already have a table for scheduling, you can just modify the techniques used in the last link I provided:

http://www.sqlteam.com/item.asp?ItemID=3332

...to create a view or stored procedure that returns a cross-tab of the available times. That way you won't need a separate table for availability, which could very easily become out-of-sync with the schedule table.

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-12 : 12:36:34

thanks rob

I think I have convinced my boss not to use the flag on/off
approach here.

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources