[SGVLUG] Can SQL even do this?

[Emerson, Tom (*IC)]

I've got an "interesting" problem -- easy enough to do with any
procedural language, but I'm curious if this can be implemented as an
SQL query all in one go...

This is for a schedule -- I have in one table a "start date" for a
series of events, a table of events with a "week number" for the event
to take place, and a third table of "blackout dates".  I'd like a query
that returns the date of each event, skipping blackout dates.

For example:

   the main table has a "start date" of 11/02/2008 [a Sunday]
   The event table has 5 entries, in the form of "week:1; event:Game
1","week:2; event:Game 2", etc. through "Game 5"
   The blackout table has an entry for 11/23/2008

The results should be

    11/02/2008  Game 1
    11/09/2008  Game 2
    11/16/2008  Game 3
   [11/23/2008  Blackout]   <--- optional
    11/30/2008  Game 4
    12/07/2008  Game 5

I don't often do calculations within the select (usually preferring to
use the language performing the database access rather than the query to
do the work) so I'm not even sure how to convert "11/02/2008 + 3" to get
"11/16/2008" (for the third entry, that is) [and/or, would it be better
for the first event to occur on "week 0" instead of "week 1"?]  I think
the real killer here is that when a calculated date appears in the
"blackout" table, that date and all subsequent dates need to increase
their offset by an additional week...



-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.sgvlug.net/pipermail/sgvlug/attachments/20081028/93a364f0/attachment.html