End date - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

End date

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-11-08 : 15:44:04

I have a table looks like:


Id	Start date
1	1/1/2004
1	03/11/04
1	06/22/04
2	2/13/2004
2	10/23/2004
3	2/14/2004
3	4/15/2004
3	6/5/2004
3	8/8/2004

I am trying to add a end date which comes from the startdate of next row. If a startdate is the last row in the same ID group, just put 'None' as the enddate. The output should looks like:


Id	Start date        End date
1	1/1/2004         03/11/04
1	03/11/04         06/22/04
1	06/22/04         None
2	2/13/04          10/23/04
2	10/23/04         None
3	2/14/04          4/15/04
3	4/15/04          6/5/04
3	6/5/04           8/8/04
3	8/8/04           None

Do I have to use a cursor for this?

chadmat
The Chadinator

1974 Posts

Posted - 2004-11-08 : 16:02:11

Well, you can't insert 'None' into a Datetime field. But if you just want it to be a varchar field the following would work (However, I would suggest keeping it Datetime, and using NULL to represent no end date)


create table #t1 ([ID] int, StartDate Datetime)

go

Insert into #t1 values(1	,'1/1/2004')
Insert into #t1 values(1	,'03/11/04')
Insert into #t1 values(1	,'06/22/04')
Insert into #t1 values(2	,'2/13/2004')
Insert into #t1 values(2	,'10/23/2004')
Insert into #t1 values(3	,'2/14/2004')
Insert into #t1 values(3	,'4/15/2004')
Insert into #t1 values(3	,'6/5/2004')
Insert into #t1 values(3	,'8/8/2004')
 

select ID, StartDate, ISNULL((Select convert(varchar(12),MIN(StartDate), 101) From #t1 Where ID = a.ID AND StartDate > a.StartDate), 'None')
From #t1 a
go
Drop Table #t1
go

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources