dividing rows into a group - 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 2005 Forums

Transact-SQL (2005)

dividing rows into a group

Author

Topic

NervousRex
Starting Member

9 Posts

Posted - 2010-07-15 : 12:38:41

I have table that currently looks like this:

Row_ID, Biz_ID, Call_Type_ID, Prospect_ID
1, 10, 20, 500
2, 10, 20, 600
3, 10, 20, 700
4, 10, 20, 800
5, 15, 21, 500

I need to break a count of grouped records into groups, so for instance given the example data above, if the count was 2 I would have added another column in the table which I would name 'Chunk'

Row_ID, Biz_ID, Call_Type_ID, Prospect_ID, Chunk
1, 10, 20, 500, 1
2, 10, 20, 600, 1
3, 10, 20, 700, 2
4, 10, 20, 800, 2
5, 15, 21, 500, 1
6, 10, 20, 900, 3

Was hoping someone could suggest an easy way to do this. I am thinking I will need to create a cursor, but not sure how I will be able to update a specific count of rows in a sequential order.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 12:45:51

quote:
Originally posted by NervousRex

I have table that currently looks like this:

Row_ID, Biz_ID, Call_Type_ID, Prospect_ID, Chunk
1, 10, 20, 500, 1
2, 10, 20, 600, 1
3, 10, 20, 700, 2
4, 10, 20, 800, 2
5, 15, 21, 500, 1
6, 10, 20, 900, 3

Small clarification:
1. On what columns you are grouping.
2. In the expected output, how you are getting the 6th row.

NervousRex
Starting Member

9 Posts

Posted - 2010-07-15 : 13:39:36

Sorry, I meant to add the 6th row to the original table also.

It would be grouped by Biz_ID and Call_Type_ID
The 2nd example is more or less what I expect I'll need to alter the table to in hopes of achieving what I need, which would allow me to query and return from that table WHERE Biz_ID = 10 AND Call_Type_ID = 20 AND Chunk = 1

My problem is getting the chunk value in there, this example is a small scale, the real table holds about 800k rows, with about 150 groups (Biz_ID, Call_Type_ID)...each of those groups needs to be broken into 'chunks' (about 300 rows) so I can assign employees to a chunk of each group.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-15 : 14:26:57

look into ROW_NUMBER()OVER(PARTITION BY x ORDER BY x) as Chunk


create table meow(Row_ID int, Biz_ID int, Call_Type_ID int, Prospect_ID int)
INSERT INTO meow
values(1, 10, 20, 500)
INSERT INTO meow
values(2, 10, 20, 600)
INSERT INTO meow
values(3, 10, 20, 700)
INSERT INTO meow
values(4, 10, 20, 800)
INSERT INTO meow
values(5, 15, 21, 500)

SELECT Row_ID, Biz_ID, Call_Type_ID, Prospect_ID, ROW_NUMBER()OVER(PARTITION BY Biz_ID , Call_Type_ID ORDER BY Call_Type_ID) as Chunk from meow 

SELECT Row_ID, Biz_ID, Call_Type_ID, Prospect_ID, RANK()OVER(PARTITION BY Biz_ID , Call_Type_ID ORDER BY Call_Type_ID) as Chunk from meow 

SELECT Row_ID, Biz_ID, Call_Type_ID, Prospect_ID, DENSE_RANK ()OVER(PARTITION BY Biz_ID , Call_Type_ID ORDER BY Call_Type_ID) as Chunk from meow

If you don't have the passion to help people, you have no passion

NervousRex
Starting Member

9 Posts

Posted - 2010-07-19 : 16:50:14

None of those are giving me what I need.

Unless anyone has a different solution, I think I'll need to create a cursor, that will take the top 300 for each group, assign it a chunk number, looping through selecting records that have a NULL chunk. Seems like there would be a easier and quicker way to do this, but I can't figure one out.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-07-19 : 19:06:39

Row_ID, Biz_ID, Call_Type_ID, Prospect_ID, Chunk
1, 10, 20, 500, 1
2, 10, 20, 600, 1
3, 10, 20, 700, 2
4, 10, 20, 800, 2
5, 15, 21, 500, 1
6, 10, 20, 900, 3

Where is the number 900 comming from?

It is likely people do not understand what you want.

Small clarification:
1. On what columns you are grouping.
2. In the expected output, how you are getting the 6th row.

NervousRex
Starting Member

9 Posts

Posted - 2010-07-20 : 13:53:38

Ok, If I have this Table_1...

Row_ID, Biz_ID, Call_Type_ID, Prospect_ID, Chunk
1, 10, 20, 500, NULL
2, 10, 20, 600, NULL
3, 10, 20, 700, NULL
4, 10, 20, 800, NULL
5, 15, 21, 500, NULL
6, 10, 20, 900, NULL
7, 10, 20, 901, NULL
8, 10, 20, 902, NULL
9, 10, 20, 903, NULL
10, 15, 21, 700, NULL

And I want (2) rows out of a Biz_ID + Call_Type_ID Grouping to be a "Chunk"...how can I update the table to be:

Row_ID, Biz_ID, Call_Type_ID, Prospect_ID, Chunk
1, 10, 20, 500, 1
2, 10, 20, 600, 1
3, 10, 20, 700, 2
4, 10, 20, 800, 2
5, 15, 21, 500, 1
6, 10, 20, 900, 3
7, 10, 20, 901, 3
8, 10, 20, 902, 4
9, 10, 20, 903, 4
10, 15, 21, 700, 1

In a separate table, I would assign my users to a chunk...
I would then be able to:

SELECT * FROM Table_1
WHERE Biz_ID = @Biz_ID AND Call_Type_ID = @Call_Type_ID AND CHUNK = @Chunk

I don't know how else to explain it other than that. The Prospect_ID column is really irrelevant to what I need to do.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-07-20 : 16:05:56

Run this to see if it is what you want ....

declare @temp table(Row_ID int, Biz_ID int, Call_Type_ID int, Prospect_ID int, Chunk int)
insert into @temp values(1, 10, 20, 500, null)
insert into @temp values(2, 10, 20, 600, null)
insert into @temp values(3, 10, 20, 700, null)
insert into @temp values(4, 10, 20, 800, null)
insert into @temp values(5, 15, 21, 500, null)
insert into @temp values(6, 10, 20, 900, null)
insert into @temp values(7, 10, 20, 901, null)
insert into @temp values(8, 10, 20, 902, null)
insert into @temp values(9, 10, 20, 903, null)
insert into @temp values(10, 15, 21, 700, null)

select
Row_ID, Biz_ID, Call_Type_ID, Prospect_ID,
rn/2 + rn%2 as Chunk
from (select *, rn = row_number() over(partition by Biz_ID,Call_Type_ID order by row_ID) from @temp)as t order by Row_ID

--- test result ---
Row_ID Biz_ID Call_Type_ID Prospect_ID Chunk
1 10 20 500 1
2 10 20 600 1
3 10 20 700 2
4 10 20 800 2
5 15 21 500 1
6 10 20 900 3
7 10 20 901 3
8 10 20 902 4
9 10 20 903 4
10 15 21 700 1

If you want the update, run this.

update t set Chunk = rn/2 + rn%2
from(select Chunk, rn = row_number() over(partition by Biz_ID,Call_Type_ID order by row_ID) from @temp)as t

--- test ----
select * from @temp

NervousRex
Starting Member

9 Posts

Posted - 2010-07-22 : 12:36:14

Yes, that is what I was looking for!

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources