Wowsers!!! Nice :-)
Very nice!
So a clustered index basically works like a template in a way? This code
jams.. thank you very much for the help.
Looks like I've got a little SQL homework to do now.
PS- Thanks for pointing out my IP in the message header too! (I didn't
realize)
"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:uU5DztVsDHA.2400@tk2msftngp13.phx.gbl...[color=blue]
> Great use for a calendar table! Try this example out.
>
>
>
> SET NOCOUNT ON
>
> CREATE TABLE TimeSlots
> (
> tsID INT IDENTITY(1,1)
> PRIMARY KEY NONCLUSTERED,
> dt SMALLDATETIME,
> ts TINYINT
> )
> GO
>
> /*
> Might also want a check constraint, e.g. CHECK h >=1
> AND h <= 12. Assuming you allow 12 appts / day
>
> You could also have a computed column that displays
> what time this timeslot actually represents, e.g.
> h = 5, time = dateadd(minute, 30 * (h-1), dt + 9:00)
>
> -- it would be more complicated than that, of course,
> -- if you don't allow appointments over a lunch hour
> */
>
>
>
> CREATE CLUSTERED INDEX dt_ts ON TimeSlots(dt, ts)
> GO
>
>
> CREATE TABLE Appointments
> (
> AppointmentID INT IDENTITY(1,1)
> PRIMARY KEY NONCLUSTERED,
> tsID INT NOT NULL FOREIGN KEY REFERENCES
> TimeSlots(tsID),
> description VARCHAR(32)
> )
> GO
>
> CREATE INDEX tsID ON Appointments(tsID)
> GO
>
> DECLARE @dt SMALLDATETIME, @i INT
> SET @dt = '20031101'
> WHILE @dt <= '20031130'
> BEGIN
> SET @i = 1
> WHILE @i <= 12
> BEGIN
> INSERT TimeSlots(dt, ts)
> VALUES(@dt, @i)
> SET @i = @i + 1
> END
> SET @dt = @dt + 1
> END
> GO
>
> INSERT Appointments(tsID, description)
> VALUES(23, 'Dentist')
>
> INSERT Appointments(tsID, description)
> VALUES(37, 'Chiropractor')
>
> INSERT Appointments(tsID, description)
> VALUES(31, 'Doctor')
> GO
>
>
> -- now your query becomes:
>
> SELECT
> realtime = DATEADD(MINUTE, 30 * (ts-1),
> DATEADD(HOUR, 9, ts.dt)),
> a.description
> FROM
> TimeSlots ts
> LEFT JOIN
> Appointments a
> ON
> ts.tsID = a.tsID
> -- here's the clustered index payoff
> WHERE
> ts.dt >= '20031101'
> AND ts.dt < '20031201'
> GO
>
> -- no looping, no lookup queries for every time
> -- slot, etc.
>
> DROP TABLE Appointments
> DROP TABLE TimeSlots
>
>
>
> You never mentioned if appointments had to be unique, so I didn't code in
> anything for that.
>
> This assumes SQL Server, but you would only have to make minor adjustments
> for other db platforms. It's always useful, though, to tell us which
> platform and version you are using!
>
> --
> Aaron Bertrand
> SQL Server MVP
>
http://www.aspfaq.com/
>
>
>
>
> "Wilk Teverbaugh" <email@notanaddress.com> wrote in message
> news:uNB5ZaQsDHA.3536@tk2msftngp13.phx.gbl...[color=green]
> > I'm using get rows to build a table for a calendar.
> > The specific view for this calendar is for an entire month.
> > Each appointment slot is one half hour long.
> > If I were to generate a page for the month of november I would have to[/color]
> check[color=green]
> > and see if an appointment is scheduled for each appointment slot[/color]
> throughout[color=green]
> > the entire month... this is 240 slots! At this point I've written it so[/color][/color]
it[color=blue][color=green]
> > only checks for an appointment if a particular day has an appointment,[/color]
> even[color=green]
> > so, with only 20 or so appointments scheduled for the entire month, it's
> > taking several seconds to return the results.
> >
> > I was thinking it might be best to pay a javascript programmer to write[/color][/color]
a[color=blue][color=green]
> > script that will parse through the whole mess to offload it onto the[/color]
> client.[color=green]
> > What would you recommend?
> >
> >
> >
> >[/color]
>
>[/color]
