sql server 2000 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

sql server 2000 query help

Author

Topic

gfaryd
Starting Member

27 Posts

Posted - 2012-01-15 : 05:43:43

Dear all
i have following table structure and sample data in sql server 2000

create table mytab (Region varchar(10), seq_no int, ref_no int)
go
insert into mytab values('East',1,200)
insert into mytab values('East',2,205)
insert into mytab values('East',3,300)
insert into mytab values('East',4,301)
insert into mytab values('West',1,100)
insert into mytab values('West',2,200)
insert into mytab values('West',3,500)
insert into mytab values('North',1,1200)
insert into mytab values('North',2,1210)
insert into mytab values('North',3,1230)
insert into mytab values('North',4,1300)
insert into mytab values('North',5,1320)

what i want is difference between two ref_no within region with respect to seq_no

Like

Query result should be like something

Region Seq_no Difference_Ref_no

East 1-2 5
East 2-3 95
East 3-4 1
West 1-2 100
West 2-3 300
North 1-2 10
North 2-3 20
North 3-4 70
North 4-5 20
.
.
.
and so on

what will be the query for this kind of out put

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-15 : 10:17:09

[code]
SELECT m1.Region,
CAST(m1.Seq_No AS varchar(5)) + '-' + CAST(m2.Seq_No AS varchar(5)) AS Seq_no,
m2.ref_no - m1.ref_no AS Difference_Ref_no
FROM mttab m1
INNER JOIN mytab m2
ON m2.Region = m1.Region
AND m2.Seq_No = m1.Seq_No + 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gfaryd
Starting Member

27 Posts

Posted - 2012-01-15 : 12:06:14

Thanx visakh

Farid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-15 : 13:20:58

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources