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 |
|
stonebreaker
Yak Posting Veteran
92 Posts |
 Posted - 2010-12-14 : 09:06:37
|
| I need to find both the current address (most recent move-in date) and the most recent previous address (next most recent move-in date).What is the most efficient way to do that?StonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-12-14 : 09:16:36
|
| In my opinion, row_number function should give you output fast.Use the row_number function and order by move_in date descand take the first two row_number returned for each address. |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-12-14 : 14:12:05
|
| Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. If you have properly designed history table, there will be two columns for the (move_in_date, move-out_date). The current residence will have (move_out_date IS NULL). Go back one day to get the prior address.SELECT R1.move_in_date AS current_move_in_date, R2.move_in_date AS prior_move_in_date FROM Residence_History AS R1, Residence_History AS R2WHERE R1.tenant_id + R2.tenant_id AND R1.move_out_date IS NULL AND R2.move_out_date = DATEADD (DAY, -1, R1.move_in_date);Did you notice all the assumptions we have to make because you don't know how to post a question? Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
stonebreaker
Yak Posting Veteran
92 Posts |
Posted - 2010-12-14 : 18:42:02
|
| Did you notice how the poster above you was able to give a perfectly satisfactory answer in just two sentences without bitching once?I asked a general question because I wanted a general answer.StonebreakerThe greatest obstacle to discovery is not ignorance - it is the illusion of knowledge. -Daniel Boorstin |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2010-12-15 : 10:51:01
|
quote:
Originally posted by stonebreaker
Did you notice how the poster above you was able to give a perfectly satisfactory answer in just two sentences without bitching once?
Did you notice how rude people have trouble getting answers to questions here? Perhaps because people don't like being treated disrespectfully when they're trying to give FREE help?Celko was trying to give you suggestions that would help you get better (and faster) answers to your questions in the future. That's not "bitching"; it's offering constructive advice. |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-12-15 : 13:05:58
|
| >> I asked a general question because I wanted a general answer.<,In that case the answer is ALWAYS "It depends.." in SQL, but the rest of that sentence in this case is "..on proper DDL."--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
How to find 2nd most recent date?