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 |
|
gs
Starting Member
14 Posts |
 Posted - 2011-05-06 : 00:31:13
|
| Hi,I have a table with a f_number (integer) field and a date_transactions (datetime) field f_number date_transactions1 02/02/19992 04/06/19981 03/02/20001 04/04/20112 09/12/2005Needed Output:I want to create another column called previous_occurence where comparing within the date_transactions field and find out what was the previous date when the f_number field happened.f_number date_transactions previous_occurence1 02/02/1999 <null>1 03/02/2000 02/02/19991 04/04/2011 03/02/20002 04/06/1998 <null>2 09/12/2005 04/06/1998Is there a way to compare dates within a date field and get the previous occurence of the date .ThanksYour help is much appreciated.Any clue to work out this logic will be very helpful. |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-05-06 : 02:56:13
|
| CREATE TABLE #Transactions (f_number INT, date_transactions DATETIME)INSERT INTO #TransactionsSELECT 1, '02/02/1999' UNION ALLSELECT 2, '04/06/1998' UNION ALLSELECT 1, '03/02/2000' UNION ALLSELECT 1, '04/04/2011' UNION ALLSELECT 2, '09/12/2005' SELECT f_number, date_transactions, ( SELECT TOP ( 1 ) date_transactions FROM #Transactions WHERE f_number = OuterTable.f_number AND date_transactions < OuterTable.date_transactions ORDER BY f_number,date_transactions DESC ) AS previous_occurenceFROM #Transactions OuterTableORDER BY f_number,date_transactions DROP TABLE #Transactions--------------------------http://connectsql.blogspot.com/ |
 |
|
|
gs
Starting Member
14 Posts |
Posted - 2011-05-09 : 00:01:26
|
| Thanks! that worked. |
|
|
|
|
|
|
Comparing dates within a field? Is it possible?