Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aleta
Starting Member
2 Posts |
 Posted - 2010-11-17 : 17:50:59
|
| Okay, so here's the deal: I have a daily summary table that summarizes several metrics by day. Because this table is being used for reporting that rolls up to month and week, the table also assigns a month and week date according to that day, so my fields list looks like this:t_table_daily_metricsmonth_dtweek_dtday_dtmetric1_cntmetric2_cntI was recently asked to add in some data that is only available monthly--there is no daily breakout. In the interest of precision I'm building a separate table for monthly metrics with these fields:t_table_monthly_metricsmonth_dtmonthly_metric1_cntmonthly_metric2_cntNow here's my dilemma. I'm pretty well committed to keeping month_dt in my daily summary table, but I'm concerned that if I name the field in the monthly table month_dt I'll encourage users to join on month_dt, which will run them into obvious issues. However, because the table is monthly, I'd hate to use the moniker day_dt, as it is not very self-documenting. I could just stick the monthly values into the first of the month date field in table_daily, but I don't like the idea, since the field would be 0s the remainder of the month, and has potential to cause some confusion. So if a user pulled, say, a rollup report for 9/30-11/01, it would be sorely misleading with respect to average daily counts.So in terms of maintaining a neat little database, how would you approach this problem? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-11-17 : 18:06:19
|
How about using "metric_dt" or "measure_dt" for both tables, and possibly have it replace "day_dt" in t_table_daily_metrics? That's pretty clear (to me anyway) and it works for any date part/reporting period. The date value itself can be anything, as long as it's consistent for the reporting period (first of month for monthly, first day of week for weekly, etc.)I'd also suggest changing month_dt and week_dt in the first table to be computed columns based on day_dt, like so:CREATE TABLE t_table_daily_metrics(day_dt datetime,month_dt AS DateAdd(month, DateDiff(month, '1/1/1900', day_dt), '1/1/1900'), week_dt AS DateAdd(week, DateDiff(week, '1/1/1900', day_dt), '1/1/1900'), ...) That way you only insert a value for day_dt, the others are calculated based on that, and the rollup reports can uses whichever date column they need for grouping or whatnot. |
 |
|
|
aleta
Starting Member
2 Posts |
Posted - 2010-11-17 : 18:54:09
|
| rob, that's a great solution. Exactly the clever idea I was looking for.Also, to your point about calculating month_dt and week_dt...I do it in the load exactly as you describe. I'm doing something right! I think my explanation was a little lacking there. But thanks for being so generous with your advice! |
|
|
|
|
|
|
|
|
Date Field Names Recommendation