compositing - 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 2000 Forums

Transact-SQL (2000)

compositing

Author

Topic

jling
Starting Member

5 Posts

Posted - 2008-04-09 : 19:09:11

I want to know how to write query so that it will return each different interval with different lengths as 1 interval with one unique length.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 19:18:07

Huh?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-09 : 19:19:38

Be clear in what you write so people can understand quickly.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-09 : 19:33:52

this reminds me of the specs i've been working from recently...

elsasoft.org

jling
Starting Member

5 Posts

Posted - 2008-04-10 : 02:22:57

What I mean is there are many rows with different uneven intervals for example like
ID mFrom mTo Length
1 29.0 29.4 0.4
1 29.4 29.6 0.2
1 29.6 30.0 0.4
1 30.0 31.0 1.0

I wanted to witre a query that can return the results as follows which has constant interval of 1.0;
ID mFrom mTo Length
1 29.0 30.0 1.0
1 30.0 31.0 1.0

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 02:29:49

select * from yourtable where mTo = mFrom + 1

??

elsasoft.org

jling
Starting Member

5 Posts

Posted - 2008-04-10 : 02:45:43

I have tried using where statement of mTo = mFrom +1, but it will only return 1 row of record which is

ID mFrom mTo Length
1 30.0 31.0 1.0

Sound funny it didn't work.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 03:04:19

maybe you want a self join to compare different rows with the same ID together? just a guess as you req still isn't really clear.

select *
from yourtable t1
join yourtable t2 on t1.ID=t2.ID
where t1.mTo = t2.mFrom + 1

elsasoft.org

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-04-10 : 10:41:49

SELECT ID, MIN(mFrom) mFrom, MAX(mTo) mTo, SUM(Length) Interval FROM yourtable
GROUP BY ID, CONVERT(int, mFrom), CASE WHEN CONVERT(int, mFrom) <> CONVERT(int, mTo) THEN CONVERT(int, mFrom) ELSE CONVERT(int, mTo) END

jling
Starting Member

5 Posts

Posted - 2008-04-10 : 20:05:40

cvipin methods had been tried, but i am having problem of merging 3 rows to become 1 rows and then averaging the values or results of those 3 rows.

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-04-10 : 21:18:01

Can you post the problem you are facing with example?

jling
Starting Member

5 Posts

Posted - 2008-04-11 : 00:08:14

Hi cvipin, thanks for the concern. The followings is the example that i try to use to solve my query problem. I hope you can help me.

HoleID mFrom mTo Values
1 29.0 29.4 1
1 29.4 29.6 2
1 29.6 30.0 3
1 30.0 31.0 7

The query output should be like below which has constant interval of 1.0 and average value calculated;
HoleID mFrom mTo Value
1 29.0 30.0 2.0
1 30.0 31.0 7.0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources