Count in Transition Function - 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

General SQL Server Forums

New to SQL Server Programming

Count in Transition Function

Author

Topic

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-19 : 14:57:06

Hello All,

This is a snippet of the data in Table1:

ID RID StartTime StopTime Event
978 220 2008-02-21 04:19:27.000 2008-02-21 04:19:57.000 S1
979 220 2008-02-21 04:19:57.000 2008-02-21 04:20:27.000 S0
980 220 2008-02-21 04:20:27.000 2008-02-21 04:20:57.000 S0
981 220 2008-02-21 04:20:57.000 2008-02-21 04:21:27.000 S1
982 220 2008-02-21 04:21:27.000 2008-02-21 04:21:57.000 S1
983 220 2008-02-21 04:21:57.000 2008-02-21 04:22:27.000 S2
984 220 2008-02-21 04:22:27.000 2008-02-21 04:22:57.000 S1
985 220 2008-02-21 04:22:57.000 2008-02-21 04:23:27.000 S2
986 220 2008-02-21 04:23:27.000 2008-02-21 04:23:57.000 S0

Now here is what I want:
Whenever An Event Switches to S0 and to count the number of S0 after that.

EX:

978 220 2008-02-21 04:19:27.000 2008-02-21 04:19:57.000 S1
979 220 2008-02-21 04:19:57.000 2008-02-21 04:20:27.000 S0
980 220 2008-02-21 04:20:27.000 2008-02-21 04:20:57.000 S0
981 220 2008-02-21 04:20:57.000 2008-02-21 04:21:27.000 S1

This means an INSTANCE of 1 transition and a COUNT of 2 S0 events in that transition.
Since it changed to S0 and one more followed it.

How can this be done?
Hope I was clear.

Dasman

==========================
Pain is Weakness Leaving the Body.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-19 : 15:06:47

[code]
Declare @t table (
id int identity(1,1),
s varchar(2)
)

Insert Into @t Select 'S1'
Insert Into @t Select 'S0'
Insert Into @t Select 'S0'
Insert Into @t Select 'S1'
Insert Into @t Select 'S1'
Insert Into @t Select 'S2'
Insert Into @t Select 'S1'
Insert Into @t Select 'S2'
Insert Into @t Select 'S0'
Insert Into @t Select 'S0'
Insert Into @t Select 'S0'
Insert Into @t Select 'S0'
Insert Into @t Select 'S1'
Insert Into @t Select 'S0'
Insert Into @t Select 'S1'
Insert Into @t Select 'S0'

;with cte1 As (
Select
*,
r1 = ROW_NUMBER() Over(Order By id)
From @t
), cte2 As (
Select
A.r1,
A.s,
se = isnull(MIN(B.r1),(Select max(r1) From cte1)+1)
From cte1 A
Left Join cte1 B
On A.r1 < B.r1
and A.s <> B.s
Where A.s = 'S0'
Group By A.id, A.r1, A.s
), cte3 As (
Select
r1 = min(r1),
s,
se
From cte2
Group By s, se
)

Select
RangeStart = r1,
RangeLength = se-r1
From cte3
[/code]

Corey

I Has Returned!!

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-19 : 15:10:56

Corey,

Does this code change the existing Table1? Also - Is it creating a Table at the beginning?

Dasman

==========================
Pain is Weakness Leaving the Body.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-19 : 15:12:22

It just declares a table variable for demonstration purposes...

It does not alter or reference Table1

Corey

I Has Returned!!

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-19 : 15:16:45

Where do I then plug in Table1? Table 1 is huge and has the S% events.

Why would I need to declare and INSERT INTO a whole new table variable?

Thanks for all this code - It seems to work on my end. I just want it to work with my data!

Thanks!
Dasman

==========================
Pain is Weakness Leaving the Body.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-19 : 16:35:00

This part is the query:


;with cte1 As (
	Select 
		*,
		r1 = ROW_NUMBER() Over(Order By id) 
	From @t Table1
), cte2 As (
	Select 
		A.r1,
		A.s,
		se = isnull(MIN(B.r1),(Select max(r1) From cte1)+1)
	From cte1 A
	Left Join cte1 B
	On A.r1 < B.r1
	and A.s <> B.s
	Where A.s = 'S0'
	Group By A.id, A.r1, A.s
), cte3 As (
	Select
		r1 = min(r1),
		s,
		se
	From cte2
	Group By s, se
)

Select 
	 RangeStart = r1,
	 RangeLength = se-r1
From cte3

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources