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 |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2002-05-29 : 01:57:11
|
hi can any one give a tip or solution ran out of ideas...sample data...Account_id Fromdate todate 10 1/2/2002 10 15/2/2002 31/3/2002 15 10/1/2002 15 10/2/2002 20 1/1/2002 if same account id has more than one row then and todate is null then result requied... Account_id Fromdate todate 10 1/2/2002 15/2/2002 10 15/2/2002 31/3/2002 15 10/1/2002 10/2/2002 15 10/2/2002 20 1/1/2002 thanks======================================Ask to your self before u ask someone |
|
|
dsdeming
479 Posts |
Posted - 2002-05-29 : 08:07:49
|
| I had to change the dates around to US standard to get this to wortk on my server, but the concept is solid.create table #a( Account_id int, Fromdate datetime, todate datetime )insert into #a select 10, '2/1/2002', null insert into #a select 10, '2/15/2002', '3/31/2002'insert into #a select 15, '10/1/2002', nullinsert into #a select 15, '10/2/2002', nullinsert into #a select 20, '1/1/2002', nullselect a.account_id, a.fromdate, case when todate is null then ( select min( fromdate ) from #a where a.account_id = account_id and a.fromdate < fromdate ) else fromdate end from #a adrop table #a |
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-05-31 : 03:47:05
|
thanks.... ======================================Ask to your self before u ask someone |
|
|
|
|
|
|
|
|
Problem need solutions