Consecutive count for multiple records

Question

Consecutive count for multiple records

Nathan Chapman

SSChasing Mays

Points: 620
More actions
November 9, 2012 at 1:17 pm

#264616

Hi there,
I've read a lot of threads on performing consecutive count and just can't get my head around this one.
Basically, I've established a working query for a single record history and want to adapt it for mutliple records without having to use looping per record.
DECLARE @data TABLE (Record int, GroupName varchar(50), History_Order int)
INSERT INTO @data(Record, GroupName, History_Order)
SELECT 300037, 'GroupD', 1 union all
SELECT 300037, 'GroupD', 2 union all
SELECT 300037, 'GroupA', 3 union all
SELECT 300037, 'GroupD', 4 union all
SELECT 300037, 'GroupA', 5 union all
SELECT 300037, 'GroupA', 6 union all
SELECT 300037, 'GroupA', 7
; with CTE AS (
SELECT [Record],[HISTORY_ORDER], [GroupName],
[HISTORY_ORDER] -
ROW_NUMBER() OVER (PARTITION BY [GroupName] ORDER BY [HISTORY_ORDER]) as rowDiff
FROM @data)
SELECT
[Record],[GroupName],[HISTORY_ORDER],
ROW_NUMBER() OVER (PARTITION BY GroupName, rowDiff ORDER BY HISTORY_ORDER) As Consec
from CTE
order by [Record],[HISTORY_ORDER]
/* == RESULTS ==
RecordGroupNameHISTORY_ORDERConsec
300037GroupD11
300037GroupD22
300037GroupA31
300037GroupD41
300037GroupA51
300037GroupA62
300037GroupA73
*/
INSERT INTO @data(Record, GroupName, History_Order)
SELECT 300035, 'GroupA', 1 union all
SELECT 300035, 'GroupB', 2 union all
SELECT 300035, 'GroupB', 3 union all
SELECT 300035, 'GroupA', 4 union all
SELECT 300035, 'GroupC', 5 union all
SELECT 300035, 'GroupA', 6 union all
SELECT 300035, 'GroupA', 7 union all
SELECT 300035, 'GroupA', 8 union all
SELECT 300037, 'GroupD', 1 union all
SELECT 300037, 'GroupD', 2 union all
SELECT 300037, 'GroupA', 3 union all
SELECT 300037, 'GroupD', 4 union all
SELECT 300037, 'GroupA', 5 union all
SELECT 300037, 'GroupA', 6 union all
SELECT 300037, 'GroupA', 7

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

I am extremely confused on what you want to do here. Initially I thought what you wanted was ROW_NUMBER with partition but you posted the query that will do that. Then there is a second insert into @data.

Can you clarify what is your source data and what you want as output?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Nathan Chapman SSChasing Mays Points: 620 More actions · Answer 2

Hi Sean,

From the first DataSet

For the 1st DataSet ..

/* == RESULTS ==

Record GroupName HISTORY_ORDER Consec

300037 GroupD 1 1

300037 GroupD 2 2 <--2nd Consecutive occurance of 'GroupD'

300037 GroupA 3 1

300037 GroupD 4 1

300037 GroupA 5 1

300037 GroupA 6 2

300037 GroupA 7 3 <--3rd Consecutive occurance of 'GroupD'

*/

--For the second DataSet, I am looking for an output like:

/* == RESULTS ==

Record GroupName HISTORY_ORDER Consec

300035 GroupA 1 1

300035 GroupB 2 1

300035 GroupB 3 2

300035 GroupA 4 1

300035 GroupC 5 1

300035 GroupA 6 1

300035 GroupA 7 2

300035 GroupA 8 3

300037 GroupD 1 1

300037 GroupD 2 2

300037 GroupA 3 1

300037 GroupD 4 1

300037 GroupA 5 1

300037 GroupA 6 2

300037 GroupA 7 3

Hope this clarifies.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Nathan Chapman (11/9/2012)
Hi Sean,
From the first DataSet
For the 1st DataSet ..
/* == RESULTS ==
Record GroupName HISTORY_ORDER Consec
300037 GroupD 1 1
300037 GroupD 2 2 <--2nd Consecutive occurance of 'GroupD'
300037 GroupA 3 1
300037 GroupD 4 1
300037 GroupA 5 1
300037 GroupA 6 2
300037 GroupA 7 3 <--3rd Consecutive occurance of 'GroupD'
*/
--For the second DataSet, I am looking for an output like:
/* == RESULTS ==
Record GroupName HISTORY_ORDER Consec
300035 GroupA 1 1
300035 GroupB 2 1
300035 GroupB 3 2
300035 GroupA 4 1
300035 GroupC 5 1
300035 GroupA 6 1
300035 GroupA 7 2
300035 GroupA 8 3
300037 GroupD 1 1
300037 GroupD 2 2
300037 GroupA 3 1
300037 GroupD 4 1
300037 GroupA 5 1
300037 GroupA 6 2
300037 GroupA 7 3
Hope this clarifies.

Yeah I am not clear what you are after still. Your original data has 7 rows and you somehow get 15 as output. You don't have GroupB or GroupC in the source but they both automagically shows up in the results. Where did Record 300035 come from? What are the business rules here?

Here is your data as I understand it.

--This is the source data.

DECLARE @data TABLE (Record int, GroupName varchar(50), History_Order int)

INSERT INTO @data(Record, GroupName, History_Order)

SELECT 300037, 'GroupD', 1 union all

SELECT 300037, 'GroupD', 2 union all

SELECT 300037, 'GroupA', 3 union all

SELECT 300037, 'GroupD', 4 union all

SELECT 300037, 'GroupA', 5 union all

SELECT 300037, 'GroupA', 6 union all

SELECT 300037, 'GroupA', 7

select * from @data

;with Results (Record, GroupName, History_Order) as

(

SELECT 300035, 'GroupA', 1 union all

SELECT 300035, 'GroupB', 2 union all

SELECT 300035, 'GroupB', 3 union all

SELECT 300035, 'GroupA', 4 union all

SELECT 300035, 'GroupC', 5 union all

SELECT 300035, 'GroupA', 6 union all

SELECT 300035, 'GroupA', 7 union all

SELECT 300035, 'GroupA', 8 union all

SELECT 300037, 'GroupD', 1 union all

SELECT 300037, 'GroupD', 2 union all

SELECT 300037, 'GroupA', 3 union all

SELECT 300037, 'GroupD', 4 union all

SELECT 300037, 'GroupA', 5 union all

SELECT 300037, 'GroupA', 6 union all

SELECT 300037, 'GroupA', 7

)

select * from Results

order by Record

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 4

I think you need to look into your Row Numbering. Perhaps a PARTITION BY the Group in addition to the ORDER BY.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

venoym (11/9/2012)
I think you need to look into your Row Numbering. Perhaps a PARTITION BY the Group in addition to the ORDER BY.

That was actually posted quite nicely in the first post. There is the magic generated data that I don't understand. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

CapnHector SSCoach Points: 16143 More actions · Answer 6

After looking at the posts this is what i think the op is after with the second set of provided input (notice there are 2 record numbers)

DECLARE @data TABLE (Record int, GroupName varchar(50), History_Order int)

INSERT INTO @data(Record, GroupName, History_Order)

SELECT 300035, 'GroupA', 1 union all

SELECT 300035, 'GroupB', 2 union all

SELECT 300035, 'GroupB', 3 union all

SELECT 300035, 'GroupA', 4 union all

SELECT 300035, 'GroupC', 5 union all

SELECT 300035, 'GroupA', 6 union all

SELECT 300035, 'GroupA', 7 union all

SELECT 300035, 'GroupA', 8 union all

SELECT 300037, 'GroupD', 1 union all

SELECT 300037, 'GroupD', 2 union all

SELECT 300037, 'GroupA', 3 union all

SELECT 300037, 'GroupD', 4 union all

SELECT 300037, 'GroupA', 5 union all

SELECT 300037, 'GroupA', 6 union all

SELECT 300037, 'GroupA', 7

and here is the query:

;WITH rnCTE AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY Record ORDER BY History_Order) AS RN1, ROW_NUMBER() OVER (PARTITION BY Record, GroupName ORDER BY History_Order) AS RN2

FROM @data)

SELECT Record, GroupName, History_Order, ROW_NUMBER() OVER (PARTITION BY Record, RN1-RN2 ORDER BY History_Order)

FROM rnCTE

What the query is doing is generating 2 row numbers, one over the entire list one over all entries for a record and another over the record and group. when we subtract these 2 numbers any consecutive enteries for each group in order of History_Order will then increment. If you select every thing from the CTE but display the RN1 - RN2 you will see what is going on:

Record GroupName History_Order RN1 RN2 rnDiff

----------- -------------------------------------------------- ------------- -------------------- -------------------- --------------------

300035 GroupA 1 1 1 0

300035 GroupB 2 2 1 1

300035 GroupB 3 3 2 1

300035 GroupA 4 4 2 2

300035 GroupC 5 5 1 4

300035 GroupA 6 6 3 3

300035 GroupA 7 7 4 3

300035 GroupA 8 8 5 3

300037 GroupD 1 1 1 0

300037 GroupD 2 2 2 0

300037 GroupA 3 3 1 2

300037 GroupD 4 4 3 1

300037 GroupA 5 5 2 3

300037 GroupA 6 6 3 3

300037 GroupA 7 7 4 3

EDIT: @sean i saw two different inputs in his original post, one with a single record number and the second with 2. not very clear with out the code tags.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Nathan Chapman SSChasing Mays Points: 620 More actions · Answer 7

Ok I know you guys are trying to understand, so I'm going to try to be as explicit as possible.

DECLARE @data TABLE (Record int, GroupName varchar(50), History_Order int)

--First DataSet (Only a single Record '300037' is used)

INSERT INTO @data(Record, GroupName, History_Order)

SELECT 300037, 'GroupD', 1 union all

SELECT 300037, 'GroupD', 2 union all

SELECT 300037, 'GroupA', 3 union all

SELECT 300037, 'GroupD', 4 union all

SELECT 300037, 'GroupA', 5 union all

SELECT 300037, 'GroupA', 6 union all

SELECT 300037, 'GroupA', 7

--Query to get the consecutive count '('Consec') of the GroupName, according to the HISTORY_ORDER

; with CTE AS (

SELECT [Record],[HISTORY_ORDER], [GroupName],

[HISTORY_ORDER] -

ROW_NUMBER() OVER (PARTITION BY [GroupName] ORDER BY [HISTORY_ORDER]) as rowDiff

FROM @data)

SELECT

[Record],[GroupName],[HISTORY_ORDER],

ROW_NUMBER() OVER (PARTITION BY GroupName, rowDiff ORDER BY HISTORY_ORDER) As Consec

from CTE

order by [Record],[HISTORY_ORDER]

/* == RESULTS for First DataSet == (CORRECT)

RecordGroupNameHISTORY_ORDER Consec

300037GroupD11

300037GroupD22

300037GroupA31

300037GroupD41

300037GroupA51

300037GroupA62

300037GroupA73

*/

DELETE FROM @data

--Second DataSet : Includes GroupNames not found in First DataSet

-- and other GroupNames (GroupE, GroupF, etc.) would appear if the DataSet

-- included more than 2 'Record' Histories as supplied below.

-- For the purpose of this exercise only the Histories for 2 Records

-- ('300035' & '300037') are provided.

INSERT INTO @data(Record, GroupName, History_Order)

SELECT 300035, 'GroupA', 1 union all

SELECT 300035, 'GroupB', 2 union all

SELECT 300035, 'GroupB', 3 union all

SELECT 300035, 'GroupA', 4 union all

SELECT 300035, 'GroupC', 5 union all

SELECT 300035, 'GroupA', 6 union all

SELECT 300035, 'GroupA', 7 union all

SELECT 300035, 'GroupA', 8 union all

SELECT 300037, 'GroupD', 1 union all

SELECT 300037, 'GroupD', 2 union all

SELECT 300037, 'GroupA', 3 union all

SELECT 300037, 'GroupD', 4 union all

SELECT 300037, 'GroupA', 5 union all

SELECT 300037, 'GroupA', 6 union all

SELECT 300037, 'GroupA', 7

--Use same query as was applied to First DataSet

; with CTE AS (

SELECT [Record],[HISTORY_ORDER], [GroupName],

[HISTORY_ORDER] -

ROW_NUMBER() OVER (PARTITION BY [Record],[GroupName] ORDER BY [HISTORY_ORDER]) as rowDiff

FROM @data)

SELECT

[Record],[GroupName],[HISTORY_ORDER],

ROW_NUMBER() OVER (PARTITION BY GroupName, rowDiff ORDER BY HISTORY_ORDER) As Consec

from CTE

order by [Record],[HISTORY_ORDER]

/* ==RESULTS== for Second DataSet == (INCORRECT!)

RecordGroupNameHISTORY_ORDER Consec

300035GroupA11

300035GroupB21

300035GroupB32

300035GroupA42 <-- Consec should =1

300035GroupC51

300035GroupA63 <-- Consec should =1

300035GroupA74 <-- Consec should =2

300035GroupA86 <-- Consec should =3

300037GroupD11

300037GroupD22

300037GroupA31

300037GroupD41

300037GroupA51

300037GroupA62

300037GroupA75 <-- Consec should =3

*/

As I mentioned above, getting the query correct for the 2nd DataSet as well as the would mean that I wouldn't have to resort to a Loop/Cursor type scenario.

Hope this Helps.

CapnHector SSCoach Points: 16143 More actions · Answer 8

Nathan check out my post above yours. the outputed data was demonstrating how the 2 ROW_NUMBER()'s worked together. if you run the actual query it will return the results you are looking for

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Nathan Chapman SSChasing Mays Points: 620 More actions · Answer 9

capnhector you got it! Thanks so much for taking the time to understand and provide exactly what I was looking for. 😀