<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6619.12">
<TITLE>Can SQL even do this?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><B><FONT FACE="Courier New">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...</FONT></B></P>
<P><B><FONT FACE="Courier New">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.</FONT></B></P>
<P><B><FONT FACE="Courier New">For example:</FONT></B>
</P>
<P><B><FONT FACE="Courier New"> the main table has a "start date" of 11/02/2008 [a Sunday]</FONT></B>
<BR><B><FONT FACE="Courier New"> The event table has 5 entries, in the form of "week:1; event:Game 1","week:2; event:Game 2", etc. through "Game 5"</FONT></B>
<BR><B><FONT FACE="Courier New"> The blackout table has an entry for 11/23/2008</FONT></B>
</P>
<P><B><FONT FACE="Courier New">The results should be</FONT></B>
</P>
<P><B><FONT FACE="Courier New"> 11/02/2008 Game 1</FONT></B>
<BR><B><FONT FACE="Courier New"> 11/09/2008 Game 2</FONT></B>
<BR><B><FONT FACE="Courier New"> 11/16/2008 Game 3</FONT></B>
<BR><B><FONT FACE="Courier New"> [11/23/2008 Blackout] <--- optional</FONT></B>
<BR><B><FONT FACE="Courier New"> 11/30/2008 Game 4</FONT></B>
<BR><B><FONT FACE="Courier New"> 12/07/2008 Game 5</FONT></B>
</P>
<P><B><FONT FACE="Courier New">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...</FONT></B></P>
<BR>
<BR>
</BODY>
</HTML>