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 |
|
txDavid
Starting Member
3 Posts |
 Posted - 2006-10-27 : 17:34:29
|
| Hello, first post here.I am re-writing a web based application and trying to make improvements this go round.Problem Statement:I have two tables that need to work independently, but may be related at any given time.tblAccountaccountIDaccountNameaccountNumberlastUpdatelastEngetc...tblEscalationescIDfk_accountIDescNumberescCustomerdescriptionnoteslastUpdatelastEngetc...Escalations come in at any time and need to be tracked. If the escalation gets hot enough there is a need to track this as a "Hot Site" by the account name. Once tracked under the account name, the account may have numerous escalations. Additionally, it is possible to open an account and not yet have any escalations.My problem is that I need to track on the account level when the last change was made, so I would need to find the MAX date of either the tblAccount.lastUpdate or any of the related tblEscalation.lastUpdate fields. Is there a preferred way to do this?In the last iteration I just performed a getdate() on either set of records and have a grotesque query that pulls the max date, but it only works if both tables have related data. Maybe just my query is bad, but seems like I'm missing something obvious here.I also need to track the engineer who made the last change.Any ideas?Thanks! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-27 : 18:49:12
|
| If you want help with a query, it would be helpful if you posted it.CODO ERGO SUM |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-28 : 02:08:40
|
| "I would need to find the MAX date of either the tblAccount.lastUpdate or any of the related tblEscalation.lastUpdate fieldsIf you do this often, and the Escalation table has many rows (millions) it might be worthwhile storing the Most Recent Date in the Account table - i.e. whenever a new entry is created in the Escalation table."it only works if both tables have related data"Sounds like you need, but don't have, an OUTER JOIN. As MVJ said, it would help if you posted the query.Kristen |
|
|
|
txDavid
Starting Member
3 Posts |
Posted - 2006-10-28 : 10:18:42
|
| Kristen,Thanks for the input, I had a feeling that was the solution, but I'm trying stay as close to "best practices" as I can, so that I can improve. The reason that I didn't post the query was simply that I felt the table structure was wrong and don't want to continue to have to kluge queries together to make up for bad structure. I've read quite a few normalization and query books already, but there are always some "basic" concepts that seem to be ignored, I assume out of the fact that people somehow know this already.Thanks again!David |
|
|
|
|
|
|
|
|
Time Stamp Question