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 |
|
tpavan7329
Starting Member
18 Posts |
 Posted - 2011-02-08 : 03:42:30
|
| Table A has columns Phone Number and Post Date, data as belowPhoneNumber PostDate 123456 12/02/2010123456 12/03/2010456789 12/04/2010456789 12/03/2010456789 12/01/2010Table B has columns phone number and Billing DatePhone Number Billing Date123456 11/20/2010123456 11/11/2010123456 11/10/2010456789 11/02/2010456789 10/10/2010456789 11/30/2010I want the result set as belowphone number post date billing date123456 12/02/2010 11/11/2010123456 12/03/2010 11/20/2010and same for phone number 45678Im trying to find a way to have a select statement based on below From table data above, 123456 phone number has 2 different post dates, step 1: To get max of post date(which is 12/03/2010) from table A for phone number 123456 and then move to table 2 to get max(billing date) which is 11/20/2010 for billing date in table cStep 2:For same phone number in table A 123456, i need to get second largest post date from table A for post date in table c and get the second largest billing datefrom table b for table c billing date and so on...Can you help me find a way to join both the table and match above steps scenarios?ThanksPavan |
|
|
djorre
Yak Posting Veteran
94 Posts |
Posted - 2011-02-08 : 04:07:53
|
| Maybe you can first become this table with row_number() over (partition by phone order by phone, postdate)Nr PhoneNumber PostDate 1 123456 12/02/20102 123456 12/03/20101 456789 12/04/20102 456789 12/03/20103 456789 12/01/2010Nr Phone Number Billing Date1 123456 11/20/20102 123456 11/11/20103 123456 11/10/20101 456789 11/02/20102 456789 10/10/20103 456789 11/30/2010then outer join that two query tables on Phone, Nr |
 |
|
|
|
|
|
Max and Max-1 based on tables