Urgent request please - 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 2008 Forums

Transact-SQL (2008)

Urgent request please

Author

Topic

rama108
Posting Yak Master

115 Posts

Posted - 2012-08-21 : 16:13:10

if i have records as follows:
ID Status
1 0
2 1
3 0
4 1
5 1
6 1
7 1
8 1
then I need to find from which ID to which ID had consecutive status of 1
for example:
4-8 had 5 (also need to have a count 5 or whatever it is)

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-21 : 16:20:00

[code]-- set up test data
DECLARE @t TABLE(ID INT, Status BIT)
INSERT @t SELECT
1,0 UNION ALL SELECT
2,1 UNION ALL SELECT
3,0 UNION ALL SELECT
4,1 UNION ALL SELECT
5,1 UNION ALL SELECT
6,1 UNION ALL SELECT
7,1 UNION ALL SELECT
8,1

-- query
;WITH CTE(ID,Grp) AS (
SELECT ID, ID-ROW_NUMBER() OVER (ORDER BY id) FROM @t
WHERE Status=1)
SELECT MIN(ID) MinID, MAX(ID) MaxID, COUNT(*) COUNT
FROM CTE
GROUP BY Grp
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC
[/code]

rama108
Posting Yak Master

115 Posts

Posted - 2012-08-21 : 16:23:41

How can I do it without CTE?

rama108
Posting Yak Master

115 Posts

Posted - 2012-08-21 : 16:25:50

The output needs to look like "4-8 had 5".

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-21 : 16:26:00

Why can't you use a CTE?

SELECT MIN(ID) MinID, MAX(ID) MaxID, COUNT(*) COUNT
FROM (SELECT ID, ID-ROW_NUMBER() OVER (ORDER BY id) Grp FROM @t WHERE Status=1) CTE
GROUP BY Grp
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-21 : 16:26:51

You can CAST the columns as varchar and concatenate them to the output you need.

rama108
Posting Yak Master

115 Posts

Posted - 2012-08-21 : 16:31:45

This is not working, I gave you 8 rows of example data but I have over a million records.
minID should show 4
MaxID should show 8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 16:34:25

quote:
Originally posted by rama108

This is not working, I gave you 8 rows of example data but I have over a million records.
minID should show 4
MaxID should show 8

http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Calculating-Running-Streak-over-many-records.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rama108
Posting Yak Master

115 Posts

Posted - 2012-08-21 : 16:36:30

Robvolk,
I sincerely apologize. This works precisely the way I wanted, I was making mistake in ordering. This proves that this is a number 1 site for sql answers. Thank you and god bless you and the creator of this site.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources