Guys atleast help me in this one

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)

Guys atleast help me in this one

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-15 : 00:33:23

I have a table with values

id diff
1 5
2 10
5 5
6 15
7 20
8 10
10 5
11 10
12 15

I need a sum of such records whose id are continous.I mean the output should be

id sum
2 15
8 50
12 25

Id 1 & 2 are continous so it is added.But after 2 the next number is not 3 so it wont be in the sum of 1 & 2.From 5 to 8 numbers are continous so those diff are added & so on.

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-05-15 : 02:48:35

This has been discussed here on sqlteam. I hope these links can help you, please post back if you run into trouble.

http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88345

Nathan Skerl

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-15 : 03:11:16

No nathan it did not help.The article you said were to check the missing values.I want to sum the records whose ids are continous.

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-15 : 04:49:47

hi ayamas,

try with this

declare @tb table(rec1 int,rec2 int)
insert @tb
select 1, 5
union all select 2 ,10
union all select 5 ,5
union all select 6 ,15
union all select 7 ,20
union all select 8 ,10
union all select 10 ,5
union all select 11 ,10
union all select 12 ,15

select * from
(select t1.rec1,t1.rec2 from @tb t1,@tb t2
where
t1.rec1 = t2.rec1+1

)a
where a.rec1+1 not in (select rec1 from @tb)

will give
2 10
8 10
12 15

ok tanx...

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-15 : 05:11:38

No sooraj it is not what I want.I wanted this
id sum
2 15
8 50
12 25
while urs is giving
2 10
8 10
12 15

Mathias
Posting Yak Master

119 Posts

Posted - 2008-05-15 : 05:31:16

Try this :

drop table #tb
create table #tb(rec1 int,rec2 int, missingbefore int, block int)
insert #tb (rec1, rec2)
select 1, 5
union all select 2 ,10
union all select 5 ,5
union all select 6 ,15
union all select 7 ,20
union all select 8 ,10
union all select 10 ,5
union all select 11 ,10
union all select 12 ,15

update a
Set missingbefore=1
from #tb as a left outer join #tb as b
on a.rec1=(b.rec1+1)
where b.rec1 is null

update a
Set block=(select max(b.rec1) from #tb b where b.missingbefore=1 and b.rec1<=a.rec1)
from #tb a

select max(rec1), sum(rec2) from #tb group by block

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-15 : 05:46:28

WOW...!
WOW...!
WOW...!
Hats of to you Mathias.You are more than genius for me.It worked perfectly.
Thanks a Zillion.

Mathias
Posting Yak Master

119 Posts

Posted - 2008-05-15 : 05:52:48

Thanks, my pleasure
Very interesting challenge indeed. One other way to look at this kind of stuff is to use recursive functions but it is not fast enough.

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-15 : 05:57:18

hi

great idea mathias...
congrats

ok tanx........

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-15 : 08:25:33

mathias this is not working.

create table #tb(rec1 int,rec2 int, missingbefore int, block int)
insert #tb (rec1, rec2)
select 1, 5
union all select 2 ,10
union all select 3 ,5
union all select 6 ,15
union all select 7 ,20
union all select 8 ,10
union all select 10 ,5
union all select 11 ,10
union all select 12 ,15
union all select 2 ,10
union all select 3 ,5
union all select 6 ,15
union all select 7 ,20
union all select 8 ,10
union all select 10 ,5
union all select 11 ,10
union all select 12 ,15

update a
Set missingbefore=1
from #tb as a left join #tb as b
on a.rec1=(b.rec1+1)
where b.rec1 is null

select * from #tb

update a
Set block=(select max(b.rec1) from #tb b where b.missingbefore=1 and b.rec1<=a.rec1)
from #tb a

select max(rec1), sum(rec2) from #tb group by block

drop table #tb

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-15 : 09:16:16

[code]
declare @tb table(rec1 int,rec2 int)
insert @tb
select 1, 5
union all select 2 ,10
union all select 5 ,5
union all select 6 ,15
union all select 7 ,20
union all select 8 ,10
union all select 10 ,5
union all select 11 ,10
union all select 12 ,15

select max(rec1) as rec1, sum(rec2) as total
from
(
select t1.rec1, t1.rec2, max(t2.rec1) as grp
from @tb t1
left outer join @tb t2 on t2.rec1 <= t1.rec1
left outer join @tb t3 on t3.rec1 + 1 = t2.rec1
where t3.rec1 is null
group by t1.rec1, t1.rec2
) x
group by grp
order by max(rec1)

rec1 total
----------- -----------
2 15
8 50
12 30

(3 row(s) affected)
[/code]

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-15 : 10:25:24

quote:
Originally posted by ayamas

mathias this is not working.

create table #tb(rec1 int,rec2 int, missingbefore int, block int)
insert #tb (rec1, rec2)
select 1, 5
union all select 2 ,10
union all select 3 ,5
union all select 6 ,15
union all select 7 ,20
union all select 8 ,10
union all select 10 ,5
union all select 11 ,10
union all select 12 ,15
union all select 2 ,10
union all select 3 ,5
union all select 6 ,15
union all select 7 ,20
union all select 8 ,10
union all select 10 ,5
union all select 11 ,10
union all select 12 ,15

Wait a minute ... what is this sample data? This makes no sense. You can't have your rec1 values repeated multiple times in the table and expect to get a result back! This isn't Excel with row numbers and column numbers, this is a relational database. Your table must have an actual primary key in order to write any meaningful SQL statement or return any results or calculations from your data. If that is truly the data you are analyzing, and there are no other columns in the table or any kind of primary key on the table, then no matter who writes the SQL or how good of a programmer you are, you will not be able to get any kind of result since your data is so poorly defined.

- Jeff
http://weblogs.sqlteam.com/JeffS

Mathias
Posting Yak Master

119 Posts

Posted - 2008-05-20 : 03:16:21

It is not a problem, you just need to change the start and add one extra column

We start creating the autonumber in the #tb

select cast(a.rec1 as int) as rec1
, cast(a.rec2 as int) as rec2
, IDENTITY(int, 1,1) AS REC_NUM
into #tb
from
( select 1 as rec1, 5 as rec2
union all select 2 ,10
union all select 3 ,5
union all select 6 ,15
union all select 7 ,20
union all select 8 ,10
union all select 10 ,5
union all select 11 ,10
union all select 12 ,15
union all select 2 ,10
union all select 3 ,5
union all select 6 ,15
union all select 7 ,20
union all select 8 ,10
union all select 10 ,5
union all select 11 ,10
union all select 12 ,15
) a

by using the same method you should be able to tackle it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources