need help with query - 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)

need help with query

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2007-12-05 : 09:45:09

Hi Guys,

I need help in writing a query on the table shown below. I was wondering if there is any better way than writing a cursor...

Table1
________

SegmentId | RouteId | LeftFrom | LeftTo | RightFrom | RightTo
______________________________________________________________
   1      |  1000   |  5001    |  5469  |  5000     |  5468   
   2      |  1000   |  5471    |  5885  |  5470     |  5886
   3      |  1000   |  5887    |  6001  |  5888     |  6000

   5      |  2000   |  391     |  489   |  390      |  488
   6      |  2000   |  491     |  521   |  490      |  520
   7      |  2000   |  521     |  601   |  522      |  600
   8      |  2000   |  603     |  699   |  602      |  698


   9      |  3000   |  10031   |  10211 |  10030    |  10210   
   10     |  3000   |  10201   |  10419 |  10312    |  10418

For each distinct Route:

1. The query should find out if there are any overlapping values(either in the left or right values) in the segments as shown above for the RouteId 2000.

2. The query should find out discrepancies in the increasing left or right values. The values should increase from one segment to the next unlike as shown above for the RouteId 3000 in red.

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 09:58:08

Why are there not any values marked with RED in the 1000 segment?

Try to write your business rules more clearly.

E 12°55'05.25"
N 56°04'39.16"

dupati1
Posting Yak Master

123 Posts

Posted - 2007-12-05 : 10:00:04

The RouteId 1000 is fine...it has no overlapping values or decreasing values as shown in the other two route ids..I was just showing some sample correct and incorrect data...

Please let me know if you need more clarification...

thanks for your attention to the post...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 10:08:39

Is SegmentId guaranteed to always be sequential without gaps?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 10:09:21

Is there a CLUSTERED index over SegmentID?

E 12°55'05.25"
N 56°04'39.16"

dupati1
Posting Yak Master

123 Posts

Posted - 2007-12-05 : 10:16:51

1. No, there is NO guarantee that segmentId is sequential but there are UNIQUE because its the primary key of the table...
2. there is no clustered index over segmentID
3. the numbers should always increase from "Left_From" to "Left_To" and same is the case with the Right side...

thanks again...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 10:19:12

So there is no guarantee that SegmentId is increasing as Left values are?


   17      |  1000   |  5001    |  5469  |  5000     |  5468   
   66      |  1000   |  5471    |  5885  |  5470     |  5886
    5      |  1000   |  5887    |  6001  |  5888     |  6000

are valid entries?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 10:25:10

Are there constraints to ensure From value is always less than To value?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 10:44:45

[code]-- Prepare sample data
DECLARE @Table1 TABLE (SegmentId INT PRIMARY KEY CLUSTERED, RouteId INT, LeftFrom INT, LeftTo INT, RightFrom INT, RightTo INT)

INSERT @Table1
SELECT 1, 1000, 5001, 5469, 5000, 5468 UNION ALL
SELECT 2, 1000, 5471, 5885, 5470, 5886 UNION ALL
SELECT 3, 1000, 5887, 6001, 5888, 6000 UNION ALL
SELECT 5, 2000, 391, 489, 390, 488 UNION ALL
SELECT 6, 2000, 491, 521, 490, 520 UNION ALL
SELECT 7, 2000, 521, 601, 522, 600 UNION ALL
SELECT 8, 2000, 603, 699, 602, 698 UNION ALL
SELECT 9, 3000, 10031, 10211, 10030, 10210 UNION ALL
SELECT 10, 3000, 10201, 10419, 10312, 10418

-- Initialize sequence control
DECLARE @From INT,
@To INT

SELECT @From = MIN(d),
@To = MAX(d)
FROM (
SELECT MIN(LeftFrom) AS d FROM @Table1 UNION ALL
SELECT MAX(LeftFrom) FROM @Table1 UNION ALL
SELECT MIN(LeftTo) FROM @Table1 UNION ALL
SELECT MAX(LeftTo) FROM @Table1 UNION ALL
SELECT MIN(RightFrom) FROM @Table1 UNION ALL
SELECT MAX(RightFrom) FROM @Table1 UNION ALL
SELECT MIN(RightTo) FROM @Table1 UNION ALL
SELECT MAX(RightTo) FROM @Table1
) AS w

-- Show SegmentId for the faulty records
SELECT DISTINCT t.RouteId,
t.SegmentId
FROM (
SELECT t.RouteId,
f.Number
FROM F_TABLE_NUMBER_RANGE(@From, @To) AS f
INNER JOIN @Table1 AS t ON f.Number BETWEEN t.LeftFrom AND t.LeftTo
OR f.Number BETWEEN t.RightFrom AND t.RightTo
GROUP BY t.RouteId,
f.Number
HAVING COUNT(*) > 1
) AS e
INNER JOIN @Table1 AS t ON t.RouteId = e.RouteId
AND (e.Number BETWEEN t.LeftFrom AND t.LeftTo OR e.Number BETWEEN t.RightFrom AND t.RightTo)
ORDER BY t.RouteId,
t.SegmentId[/code]

E 12°55'05.25"
N 56°04'39.16"

dupati1
Posting Yak Master

123 Posts

Posted - 2007-12-05 : 10:57:53

thanks for your time...I will try the query you suggested...and yes what all you assumed is correct...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 11:07:40

F_TABLE_NUMBER_RANGE function can be found here on SQLTeam.

E 12°55'05.25"
N 56°04'39.16"

dupati1
Posting Yak Master

123 Posts

Posted - 2007-12-05 : 11:16:04

thank you...yes I was getting an invalid object error message...

is this the function are you referring to...

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 11:19:40

Yes.

E 12°55'05.25"
N 56°04'39.16"

dupati1
Posting Yak Master

123 Posts

Posted - 2007-12-05 : 11:45:01

Thanks Peso. Looks like its working great. I am trying to understand the query now...How efficient do you think this query is. Do you think it is better than cursors...

Thanks for your time and consideration.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 13:18:14

For a large number of records it will not be very efficient.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources