query help 5

DotNetGnat · Oct 16, 2007

Hello All,

I have a table as shown below:

Code:

trackid    | beginAt | endAt
_____________________________
track1     |  0      | 1.55
track1     |  1.55   | 2.20
track1     |  2.20   | 3.25

track2     |  0      | 0.55
track2     |  1.05   | 1.80

track3     |  0      | 2.21
track3     |  2.15   | 2.80
track3     |  3.20   | 3.25

All tracks identified by trackid begin at 0 and can have number of segments and the end of the track is identified by the highest endAt number.

I want help with writing a query that identifies the breaks in the segments...for example...track1 is fine and has no problems and no breaks, ending point is the same as next beginning point...

but for track2 and track3 there are breaks as ending point is not same as the beginning point...

thanks in advance...

-DNG

Gixonita · Oct 16, 2007

Hello,

you could try something like

Code:

DECLARE	@TABLE	TABLE (trackid VARCHAR(20), beginAt DECIMAL (6,2), endAt DECIMAL (6,2))

INSERT INTO	@TABLE

SELECT	'track1',0,1.55
UNION ALL
SELECT	'track1',1.55,2.20
UNION ALL
SELECT	'track1',2.20,3.25
UNION ALL
SELECT	'track2',0,0.55
UNION ALL
SELECT	'track2',1.05,1.80
UNION ALL
SELECT	'track3',0,2.21
UNION ALL
SELECT	'track3',2.15,2.80
UNION ALL
SELECT	'track3',3.20,3.25

SELECT	trackid,
	SUM (beginAt),
	SUM (endAt),
	MAX (endAt),
	SUM (endAt) - MAX (endAt)

FROM	@TABLE

GROUP BY	trackid

HAVING	SUM (beginAt) != SUM (endAt) - MAX (endAt)

Hope this helps

AlexCuse · Oct 16, 2007

DNG - have a look at this example:

Code:

[COLOR=blue]declare[/color] @tracks [COLOR=blue]table[/color] (trackID [COLOR=blue]int[/color], beginAt [COLOR=blue]numeric[/color](10,2), endAt [COLOR=blue]numeric[/color](10,2))

[COLOR=blue]insert[/color] @tracks
[COLOR=blue]select[/color] 1, 0, 1.55
union all [COLOR=blue]select[/color] 1, 1.55, 2.2
union all [COLOR=blue]select[/color] 1, 2.2, 3.25
union all [COLOR=blue]select[/color] 2, 0, .55
union all [COLOR=blue]select[/color] 2, 1.05, 1.8
union all [COLOR=blue]select[/color] 3, 0, 2.21
union all [COLOR=blue]select[/color] 3, 2.15, 2.8
union all [COLOR=blue]select[/color] 3, 3.2, 3.25




[COLOR=green]--insert your sample data to temp table (with ID column)
[/color][COLOR=blue]declare[/color] @trackTemp [COLOR=blue]table[/color] (ID [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), trackID [COLOR=blue]int[/color], beginAt [COLOR=blue]numeric[/color](10,2), endAt [COLOR=blue]numeric[/color](10,2))

[COLOR=blue]insert[/color] @trackTemp (trackID, beginAt, endAt) 
[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @tracks
[COLOR=blue]order[/color] [COLOR=blue]by[/color] trackID, beginAt


[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @trackTemp a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] @trackTemp b
[COLOR=green]--thes join conditions allow us to compare current row to next (where trackID is the same only)
[/color][COLOR=blue]on[/color] a.trackID = b.trackID 
	and a.id + 1 = b.id
[COLOR=blue]where[/color] a.endAt <> b.beginAt

It will need some refinement to return exactly the information you want, but should show you how to identify breaks in the tracks. Let me know if you have any questions.

Hope it helps,

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

gmmastros · Oct 16, 2007

I encourage you to test this a thousand different ways to make sure it always returns the correct results. [smile]

Code:

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](TrackId [COLOR=blue]VarChar[/color](20), BeginAt [COLOR=blue]Decimal[/color](10,2), EndAt [COLOR=blue]Decimal[/color](10,2))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'track1'[/color],0   ,1.55)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'track1'[/color],1.55,2.20)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'track1'[/color],2.20,3.25)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'track2'[/color],0   ,0.55)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'track2'[/color],1.05,1.80)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'track3'[/color],0   ,2.21)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'track3'[/color],2.15,2.80)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'track3'[/color],3.20,3.25)

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp [COLOR=blue]As[/color] AllTracks
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]Select[/color] A.TrackId,
                A.BeginAt [COLOR=blue]As[/color] BeginAt1,
				B.BeginAt [COLOR=blue]As[/color] BeginAt2
         [COLOR=blue]From[/color]   @Temp A
                [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] @Temp B
                  [COLOR=blue]On[/color] A.TrackId = B.TrackId
                  And A.EndAt = B.BeginAt
         ) [COLOR=blue]As[/color] TracksOk
         [COLOR=blue]On[/color] AllTracks.TrackId = TracksOk.TrackId
         And (AllTracks.BeginAt = TracksOk.BeginAt1 Or AllTracks.BeginAt = TracksOk.BeginAt2)
[COLOR=blue]Where[/color]	TracksOK.TrackId [COLOR=blue]Is[/color] NULL
[COLOR=blue]Order[/color] [COLOR=blue]By[/color] AllTracks.TrackId, AllTracks.BeginAt

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

DotNetGnat · Oct 16, 2007

I did something like this:

Select trackId, beginAt, endAt from mytable a
WHERE a.beginAt NOT IN ( SELECT b.endAt FROM mytable b WHERE a.trackId = b.trackId)
and a.beginAt ! =0

since the table has millions of records, i was wondering about which would be the efficient way...

thanks

-DNG

vongrunt · Oct 16, 2007

Mhm.... maybe:

Code:

select trackid
from blah
group by trackid
having sum(endAt-beginAt) <> max(endAt) - min(beginAt)

------
chemistry - the only natural science that can be broken down into the categories a) making drugs and b) blowing stuff up
[banghead]

DotNetGnat · Oct 16, 2007

Haven't tried Alex's and George's methods yet...

but Gixonita's, vongrunt's and my method all returning different number of records...

hmm..let me look closely...

-DNG

gmmastros · Oct 16, 2007

The problem with using sums, mins and max's, is that multiple problems could cause a problem with the query. [bugeyed]

Consider this data...

Code:

Declare @Temp Table(TrackId VarChar(20), BeginAt Decimal(10,2), EndAt Decimal(10,2))

Insert Into @Temp Values('track1',0   ,1.55)
Insert Into @Temp Values('track1',1.55,2.20)
Insert Into @Temp Values('track1',2.20,3.25)
Insert Into @Temp Values('track2',0   ,0.55)
Insert Into @Temp Values('track2',1.05,1.80)
Insert Into @Temp Values('track3',0   ,[!]2.21[/!])
Insert Into @Temp Values('track3',[!]2.15,3.14[/!])
Insert Into @Temp Values('track3',[!]3.20[/!],3.25)

select trackid
from   @Temp
group by trackid
having sum(endAt-beginAt) <> max(endAt) - min(beginAt)

The Begin At is 0.06 less than it should be for row2, and 0.06 more than it should be for row3. These differences essentially cancel each other out and Track 3 is not returned as being a problem (eventhough it should be).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Gixonita · Oct 16, 2007

My method is basically the same as vongrunt's, I just left a bunch of columns so you could see the logic behind it, if you select distinct trackid then it should just give you track 2 and 3

Gixonita · Oct 16, 2007

gmmastros:

shouldn't the value of beginAt be always greater than the last endAt?

vongrunt · Oct 16, 2007

Yes, methods using HAVING() aren't 100% safe...

Is there any way to do it without self-join?

------
chemistry - the only natural science that can be broken down into the categories a) making drugs and b) blowing stuff up
[banghead]

DotNetGnat · Oct 16, 2007

Yes Gixonita,

I provided the wrong data in my post..but we can safely assume that beginAt is always greater than the last endAt.

-DNG

gmmastros · Oct 16, 2007

Gixonita,

I don't know if beginAt will always be greater than the last endAt. This isn't my data. Also... it sounds like DNG is trying to identify bad data, so the query should be able to accommodate any kind of bad data.

vongrunt,

I don't know of any method that will allow you to do this without using a self join. I wish there were. A couple months ago, I found myself faced with a very similar query. The advice I gave here is similar to the query I used myself. In my case, it was a bit more complicated though. I was trying to identify routes that had broken paths, so each record had a begin Latitude, Begin Longitude, End Latitude and End Longitude.

If this could be done without a self join, I would find that very useful.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

DotNetGnat · Oct 16, 2007

hmm...i noticed that NOT all tracks start at 0, they may have different starting points...but still the meaning of 'broken tracks' should remain the same...

-DNG

AlexCuse · Oct 16, 2007

Somehow I don't think this is what you're after ;-)

Code:

select * from @tracks x
where endAt <> (select max(endAt) from @tracks where trackID = x.trackID)
 	and not exists ( select * from @tracks where trackID = x.trackID and beginAt = x.endAt )

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

DotNetGnat · Oct 16, 2007

ok. the only reason that all the queries returned different results was that the tracks had a starting beginAt other than 0.

thanks for all your suggestions...

have stars...

-DNG

ESquared · Oct 16, 2007

Code:

Declare @Temp Table(TrackId VarChar(20), BeginAt Decimal(10,2), EndAt Decimal(10,2))

Insert Into @Temp Values('track1',0   ,1.55)
Insert Into @Temp Values('track1',1.55,2.20)
Insert Into @Temp Values('track1',2.20,3.25)
Insert Into @Temp Values('track2',0   ,0.55)
Insert Into @Temp Values('track2',1.05,1.80)
Insert Into @Temp Values('track3',0   ,2.21)
Insert Into @Temp Values('track3',2.15,2.80)
Insert Into @Temp Values('track3',3.20,3.25)
Insert Into @Temp Values('track4',1.1 ,1.5 )
Insert Into @Temp Values('track4',1.5 ,2.0 )
Insert Into @Temp Values('track4',1.1 ,2.0 )

SELECT trackid
FROM
   (
      SELECT trackid, cnt = Count(*), a = Sum(a)
      FROM
         @Temp T
         CROSS JOIN (SELECT 1 UNION ALL SELECT 2) x (a)
      GROUP BY
         trackid,
         CASE a WHEN 1 THEN beginat ELSE endat END
   ) y
GROUP BY
   trackid
HAVING
   Sum(case when cnt = 1 and a in (1,2) then 1 else 0 end) <> 2
   OR Sum(case when cnt = 2 and a <> 3 then 1 else 0 end) <> 0
   OR Sum(case when cnt > 3 then 1 else 0 end) <> 0

I don't have more time to put into finding a simpler way to extract the desired data.

If you run the derived table as its own query (adding the grouped by expression) and examine the results, you'll see the pattern that the outer query has to handle.

I had it working at first with just a HAVING COUNT(*) <> 2 on both the inner and outer queries and no Cnt or Sum(a) at all, but then I thought of track 4 which breaks that. Track 4 makes things difficult because it tends to disappear entirely if you put too many conditions on the inner query.

Anyway... no self-join, no unions of the table itself.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

ESquared · Oct 16, 2007

duh... add a DISTINCT after the SELECT and then:

Code:

HAVING
   Sum(case when cnt = 1 and a in (1,2) then 1 else 0 end) <> 2
   OR (cnt = 2 and a <> 3)
   OR cnt > 3

I am stil sure there's a way to simplify all this but this just came to me as I was working on something else...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

DotNetGnat · Oct 17, 2007

Thanks ESquared. I will have a look at your code when I get a chance.

Have a star for your efforts. I appreciate it.

-DNG

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

query help 5

Programmer

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

IS-IT--Management

IS-IT--Management

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor