split out number into groups - 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)

split out number into groups

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-03-20 : 07:01:11

hi,
not sure if the title really expains what I want to achieve...

I have a table which is like :

name groups number
bob 1 160
bob 51 160
bob 101 160

What I need to do is split the 160 into the correct groups..
so the first 50 goes into group 1, then next 50 in group 51, then the next 60 in 101..

so the result would look like :

name groups number
bob 1 50
bob 51 50
bob 101 60

thank you for any advice.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-20 : 08:26:32

This would assume that your [groups] and [number] values don't conflict with each other.


declare @t table ( groups int, number int)
insert @t
select  1, 160 union all
select  51, 160 union all
select  101, 160

;with cte (groups, number, rn) as 
(
select groups, number, row_Number() over (order by groups) from @t
)
select	c1.groups
	,isNull(c2.groups-c1.groups, c1.number-c1.groups+1) number
from	cte c1
left outer join cte c2 on c2.rn = c1.rn+1


output:
groups      number
----------- -----------
1           50
51          50
101         60

Be One with the Optimizer
TG

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-03-20 : 08:31:24

hi, they could conflict.

ie, could have :

1 200
100 200
200 200

so that would be

1 99
100 100
200 1

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-20 : 09:39:15

try this Jamie,

DECLARE @t TABLE ( groups INT, number INT)

INSERT @t
/*
SELECT 1, 160 UNION ALL
SELECT 51, 160 UNION ALL
SELECT 101, 160
*/
SELECT 1, 200 UNION ALL
SELECT 100, 200 UNION ALL
SELECT 200, 200

SELECT T1.groups, ISNULL(T2.groups - T1.groups, T1.number - T1.groups + 1)
FROM ( SELECT ROW_NUMBER() OVER(ORDER BY groups) Row, groups, number FROM @t) T1
LEFT JOIN ( SELECT ROW_NUMBER() OVER(ORDER BY groups) Row, groups, number FROM @t) T2 ON T1.Row + 1 = T2.Row

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-03-20 : 09:53:53

thats great !
one minor change.
what if I had my info like : groupname 1 51 100
bob 200 200 200

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-03-20 : 10:08:40

sorry, info is like :

name g1 g2 g3 g4 calc1 calc2 calc3 calc4
bob 1 51 101 0 200 200 200 200

its te calcs I need to correct..

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-03-20 : 10:29:44

hi, the above code doesn't work.

if I have :
1 6
51 6
101 6

instead of doing :
1 6
51 0
101 0

it is counting into the minus figures :
eg.
1 6
51 -49
101 -98

jrogers
Starting Member

34 Posts

Posted - 2008-03-20 : 10:37:06

you could use Case..


SELECT 
     T1.groups, 
     CASE WHEN ISNULL(T2.groups - T1.groups, T1.number - T1.groups + 1)<0 then 0
     ELSE ISNULL(T2.groups - T1.groups, T1.number - T1.groups + 1) End
FROM ( SELECT ROW_NUMBER() OVER(ORDER BY groups) Row, groups, number FROM @t) T1
LEFT JOIN ( SELECT ROW_NUMBER() OVER(ORDER BY groups) Row, groups, number FROM @t) T2 ON T1.Row + 1 = T2.Row

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources