Posted by David Portas on 10/21/07 14:42

"PeterF" <fischerp@melbpc.org.au> wrote in message
news:1192955688.998715.85180@z24g2000prh.googlegroups.com...
> Hi there
> I have been given the job of developing a provision that enables
> recording of anniversaries - that is dates - against a variety of
> entities to which these dates are relevant. And this needs to generate
> new dates for any that 'grow old', ie drop beyond today into the past.
> This whole thing needs to be highly generic an flexible but, in the
> end, I see it like a calendar facility but not just for myself; but
> for an open-ended set of users.
> My suspicion is that there would be those among you who have come with
> very smart solutions to just this problem. And I therefore wonder, if
> you are willing to share your conclusions and maybe solutions.
> thanks for any input you care to offer
>

Assume you create a Numbers table (integers from 0 to 100,000, say). You can
generate 20 years worth of anniversaries like this:

SELECT EventName, DATEADD(YEAR, n.number, EventDt) AS EventDt
FROM ImportantEvents, Numbers n
WHERE n.number BETWEEN 0 AND 20;

Put that query in a view and you should have all the data you need. I don't
see any particular reason to "generate" new dates.

--
David Portas

[Back to original message]