Dynamic Date/Calendar

Hey,
I have a financial query that has to retrieve sales totals for each day of
the month for the current year and prior year. Some days though (current an
d
prior) don't have any sales on those days so I still have to return zero. I
have this working just fine...but to do it I created a month_day_year table
that has all the month/day/year columns in it. I join to this and thus am
always assured to have rows/columns returned regardless if there are sales
for those days. The problem is the maintenance of this month_day table. If I
forget to add new months...things go bad. Surely there has to be a better wa
y
to do this...like some way to dynamically generate the month/day/year idea a
t
query run time...and not use this table to join to to ensure I get something
whether data is there or not.
Thanks in advance,
Paul
--
---
Jackson-Reed, Inc. www.jacksonreed.comActually, a calendar table is a great way to handle financial data like
this. As you know fiscal periods don't always line up to calendar
periods; a calendar table let yous manage this and other problems.
Managing this table shouldn't be a big deal; 10 years of days is only
slightly more than 3600 rows; 20 years is still less than 10,000 rows
of data, which should be nothing to manage. I would just generate the
calendar entries for 10 to 20 years, and leave it alone.
Stu|||> for those days. The problem is the maintenance of this month_day table. If
> I
> forget to add new months...things go bad.
Why do you need to add months and days in an ongoing fashion? Just put the
next 30 years in the table, and you might have to think about it again once
before you retire. 10,000 rows is less stressful than a walk in the park
for SQL Server.
http://www.aspfaq.com/2519