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 |
|
aicha
Starting Member
2 Posts |
 Posted - 2009-03-04 : 07:35:32
|
| Hi,We have a table which store records with effective start and effective end dates. While I prefer to keep the effective end date of the most current record as NULL, there are discussions that we use an artificial end date.To find the most current record, we would have to check for the record with the effective end date set to NULL and if that record does not exist, we have to check for the record with the maximum effective end date. I understand that to allow have NULL in the effective end date would mean using a CASE statement to determine if we use the record with the NULL or the maximum date. This would result in more inefficient processing.However, I do not like to put an artificial value which does not accurately reflect the record.Which solution would you recommend? What are the pros and cons? Are there more efficient processing methodologies in SQL Server (besides using the CASE statement)?Thanks! |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-03-04 : 07:42:20
|
| I'd have kept the record to be displayed as today's date. You could then use BETWEEN clause to join and it will use indexes, provided the columns are indexed. |
 |
|
|
aicha
Starting Member
2 Posts |
Posted - 2009-03-18 : 18:16:43
|
| We decided to use the MAX(effective_start_dt) to find the most current record.Thanks! |
|
|
|
|
|
|
Using NULL or fake value