database to comprehensively manage anniversaries

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

Re: database to comprehensively manage anniversaries

"PeterF" 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

Re: database to comprehensively manage anniversaries

Hi David

Thank you for your input; once I understand it fully, I can feel it is
a neat solution to one aspect of my wider problem. I guess I was
hoping (fishing!) for some design inputs on what such a provision
might cater for; or maybe pointers to information elsewhere. It may
have been sneaky of me to post to this group, with possibly a narrow
focus on SQL and SQL Server. It just happens that this will be the
platform of choice.

I am simply certain that there must be many solutions to this problem
out there; and some I expect would be neat and others maybe not so
neat. I know from knowing how I work that once I have implemented my
solution, I will know whether I would do it the same way again; or
improve it heaps.