Query Help - 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

Query Help

Author

Topic

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-03-14 : 02:08:33

I have below sample data
ID RecordIndicator
1 RA
2 SR
3 RX
4 RX
5 RX
6 RX
7 RX
8 RX
9 ST
10 SR
11 RX
12 RX
13 RX
14 RX
15 RX
16 RX
17 ST
18 XT

I should get the following Numbering column
ID Indicator Numbering
1 RA NULL
2 SR 1
3 RX 1
4 RX 1
5 RX 1
6 RX 1
7 RX 1
8 RX 1
9 ST 1
10 SR 2
11 RX 2
12 RX 2
13 RX 2
14 RX 2
15 RX 2
16 RX 2
17 ST 2
18 XT 2

NOTE: When Indicator = 'SR' then number is 1 up to next coming 'SR'. Whenever next SR indicator present in the row then it should increment the previous number by 1

--
Chandu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-14 : 02:34:27

[code]
; with sr as
(
select ID, Indicator, Numbering = row_number() over (order by ID)
from yourtable
where Indicator = 'SR'
)
select *
from yourtable t
outer apply
(
select top 1 Numbering
from sr
where sr.ID <= t.ID
order by sr.ID desc
) n
order by ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-03-14 : 02:35:46

@SampleData is sample data.

DECLARE @SampleData table (ID int, RecordIndicator varchar(10))

--INSERT INTO @SampleData VALUES...

SELECT
	ID,
	Indicator,
	CASE WHEN Numbering = 0 THEN NULL ELSE Numbering END Numbering
FROM (
SELECT
	ID,
	RecordIndicator Indicator,
	(SELECT COUNT(*) FROM @SampleData SUB WHERE SUB.ID <= BASE.ID AND SUB.RecordIndicator = 'SR') Numbering
FROM @SampleData BASE) TBL

-------------------------------------
From Japan
Sorry, my English ability is limited.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-03-14 : 02:52:26

Thank you so much........ Both of U

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources