Querying for calendar, grouped by hour?

[Herb Kauhry]

I've been scratching my head on this for quite awhile and it has me stumped.

I hope to define a query which I can use to fill a "day planner" type of
calendar. Although I've see a lot of these, only one has had what I think
is a really nice feature - it collected into groups all events that
overlapped into contiguous blocks of time. The net result of this is that
it becomes possible to output a calendar (html table) that is much less
cluttered. So I want to use this same idea for my own little project.

The trick is that events may (or may not) start and end such that they
overlap (completely or only at one end). I only am concerned with events on
a given day.

My "events" table contains eventtitle, date,starthour and endhour. Hours
are numbered from "0" to "23". I also have a lookup table of the "hours of
the day" with which I did a JOIN to include the "missing" hours (where there
were no events - making a query that returned events for each hour and nulls
for each hour that had no event scheduled for it. But this makes too many
"blank" rows, which is part of the clutter to which I referred.

I've been able to construct queries that work in some cases, but not all.
I've reread my copy of Celko's SQL For Smarties and came close, but no
cigar. Where he discusses hotels and room-nights is part of the solution I
needed, but my need goes beyond that quite a bit.

Basically, I need to calculate one or more "spans" that contain contiguous
groups of start/end times. By knowing the number of hours spanned, I can
then use that for a <TD rowspan='n'> to collect my data like I want.

I seem to keep hitting all around the solution. Maybe there isn't one (that
is purely a SQL solution). Or maybe I'm just looking at the problem the
wrong way.

So I thought I'd see if anyone here might point me in the right (or at least
"new") direction. I've been looking at this for so long, I'm probably
overlooking some simple and obvious trick to do this. Or maybe I'll get
lucky and someone has seen or done exactly this already and can provide a
solution?

Incidentally, I've avoided utilizing a stored procedure or making a number
of temp tables to collect intermediate results, as I might need to port this
to a "dumb" database that does not provide such facilities. Maybe that's
impractical?

thanks in advance,
--

[Herb Kauhry]

By way of example, here's a link that illustrates what I'm talking about:

http://mywebpages.comcast.net/7432D6.../calsample.gif

--

[Erland Sommarskog]

Herb Kauhry (ti****@tulips. me) writes:

My "events" table contains eventtitle, date,starthour and endhour.
Hours are numbered from "0" to "23". I also have a lookup table of the
"hours of the day" with which I did a JOIN to include the "missing"
hours (where there were no events - making a query that returned events
for each hour and nulls for each hour that had no event scheduled for
it. But this makes too many "blank" rows, which is part of the clutter
to which I referred.

I'm afraid that I have to meet your question with a standard reply.
Please post the following:
o CREATE TABLE statement your table(s).
o INSERT statements with sample data.
o Expected output from that data.

The reason for this only from a narrative is difficult to feel
certaintity that one has full understanding of what you are asking
for. Also, by providing the above, you can also get a tested solution.
So while this requires you do some more work, you save time in the
end.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Joe Celko]

>> My "events" table contains eventtitle, date,starthour and endhour.
Hours are numbered from "0" to "23". <<

I would not do it that way; you are splitting up durations that you have
to re-assemble later. Keep the temporal data in proper units.

CREATE TABLE Events
(event_title VARCHAR(50),
start_time DATETIME NOT NULL
CHECK (DATEPART(MI, start_time) IN (00, 15, 30, 45)),
end_time DATETIME NOT NULL
CHECK (DATEPART(MI, end_time) IN (00, 15, 30, 45)),
CHECK (start_time < end_time),
PRIMARY KEY (start_time, end_time));

I also have a lookup table of the "hours of the day" with which I did a JOIN to include the "missing" hours (where there were no events -
making a query that returned events for each hour and nulls for each
hour that had no event scheduled for it. But this makes too many "blank"
rows, which is part of the clutter to which I referred. <<

Keeping with the idea of temporal data being in tact

CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY);
need to calculate one or more "spans" that contain contiguous groups

of start/end times. <<

I am not sure what you mean by that. Queries using the above model
will make a lot of use of things like this

SELECT C1.cal_date,
COUNT(E1.event_ titles) AS nbr_things_to_d o
FROM Events AS E1, Calendar AS C1
WHERE C1.cal_date BETWEEN E1.start_time AND E1.end_time
GROUP BY C1.cal_date;

Longer spans are determined by not having a cal_date for which a
(start_time, end_time) pair exists within its range -- a gap.
--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!