how to get the longest serices

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 2005 Forums

Transact-SQL (2005)

how to get the longest serices

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2009-05-15 : 10:20:38

I have a table t.
id date value
1 01/01/2007 10
1 10/09/2006 20
1 08/29/2007 25
1 09/06/2008 21
1 07/03/2008 20
1 09/01/2008 25
1 08/29/2007 25
1 09/16/2004 21
1 07/13/2004 10
1 09/11/2003 25
2 01/01/2004 10
2 10/09/2004 20
2 08/29/2007 24
2 09/06/2008 27
2 07/03/2008 10
2 09/01/2008 25
2 08/29/2007 25
2 09/16/2004 21
2 07/13/2004 10

I want to find the starting date and ending date and # of times for each id which have the longest continue value >=20 .
the output table should look like or

id startdate enddate times
1 29aug2007 06sep2008 5
2 16sep2004 19aug2007 4

Thanks.
Jeff
2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-15 : 10:58:39

This may be of interest?
http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources