how do i find Gap and Overlap - 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 2005 Forums

Transact-SQL (2005)

how do i find Gap and Overlap

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 14:33:24

I got new problems.
I have data like this:

ID BegNo EndNo
1 001 050
2 051 100
3 110 150
4 140 160
5 160 180

I have to find out the Gap number or overlap number from BegNo and EndNo into next record.

The Output looks like this:

ID BegNo EndNo Status
1 001 050 NULL
2 051 100 NULL
3 110 150 Gap
4 140 160 OverLap
5 160 180 OverLap

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 14:39:36

Can you explain your output ? I don't understatnd it.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 14:41:03

I tried to find the Gap this way: but does not work

update tbl1 set Status='Gap' where id in(
select b.id from tbl1 a join tbl1 b on b.id = a.id+1 where CAST(a.EndNo as Int) < CAST(b.BegNo as Int)-1 )

for Overlap:

update tbl1 set Status='Overlap' where id in(
select b.id from table_2 a join table_2 b on b.id = a.id+1 and b.BegNo <= a.EndNo)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 14:54:49

This ?

update a
set a.status=case when a.endno>b.begno then 'Gap' when a.endno=b.begno then 'Overlap' end
from
tbl1 a join tbl1 b on b.id=a.id+1

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-18 : 15:21:51

Assuming this restates your problem correctly: Given a relationship between each row "a" and it's subsequently numbered row "b", you would like to update the status for each row "b". If the BegNo of row "b" is exactly 1 greater than the EndNo of row "a", then status should be NULL. If the BegNo of row "b" is less than or equal to the EndNo or row "a" then the status should be 'Overlap'. If the BegNo of row "b" is greater than the EndNo of row "a" plus 1, then the status should be 'Gap'.....

Then I think you're looking for this:

update b
set b.status = case when b.begno>(a.endno+1) then 'Gap'
when b.begno<(a.endno+1) then 'Overlap'
end
from tbl1 a join tbl1 b on a.id+1 = b.id

Where the join represents a relation between 2 rows, "a" being the earlier row, and "b" being the next consecutively numbered row.

Hope this helps.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 15:35:13

yes SQLforGirls
But i have some records like this: 100_1 or 10_33_44
so i got error.

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-18 : 15:49:25

Can you put those values into the sample record set in your original post? Do you mean that you have those values (100_1, 10_33_44) as BegNo and EndNo values? If you want to compare values to see if they overlap, you need to know you are dealing with a consistent data type, and that you know how to define an overlap. The simplest would be to convert all BegNo and EndNo values to numerical values first. If they are decimals, then you may also need to redefine the overlap/gap criteria.

So your first task would be to figure out what you have in those BegNo and EndNo columns, and convert them into something that both you and the server know how to compare.

I'm sure we're all happy to help with more specifics.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 15:55:15

Okay, take a look after record no. 5

ID BegNo EndNo
1 001 050
2 051 100
3 110 150
4 140 160
5 160 180
6 181_01 184_01
7 185_01_002 190_01_002

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-18 : 15:57:33

http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx

E 12°55'05.63"
N 56°04'39.26"

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-18 : 16:15:24

So, what do you know about the meaning of the data in rows 6 and 7? Whether they represent Datetime values, or some other numbering scheme, you need to define whether or not you need to take into account the 2nd and 3rd number places when evaluating a Gap or Overlap right? Look at Row 6. Is that a Gap, since 180 to 181_01 is more than 1 whole number difference? (Assuming 181_01 is analogous to 181.01).

You either need to provide more meaning behind the values so you can come up with a decent Gap rule, and then convert the values as necessary, or provide a Gap rule that takes into account the possible data format, without providing more meaning behind the actual values.

I could imagine several possible meanings of the data, and several possible ways of defining Gap or Overlap, all of which might make sense depending on the possible business rules and reasons you are dealing with. Each would have a different code solution, so it really makes sense to know your entire situation first.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-18 : 18:04:45

Thanks SQLforGirls,
in rows 6 and 7 we don't need after first _ i mean in row 6 BegNo is 185 and EndNo is 190

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-18 : 18:50:31

Do you know that your BegNo and EndNo columns will always follow the format '999_99_999'? Or at least that the first section will always contain 3 digits? If so, then you can apply this conversion:

cast(left(BegNo,3) as int)

Then you have BegNo and EndNo as integers and you compare them from there.

update b
set b.status = case when cast(left(b.begno,3) as int)>(cast(left(a.endno,3) as int)+1) then 'Gap'
when cast(left(b.begno,3) as int)<(cast(left(a.endno,3) as int)+1) then 'Overlap'
end
from tbl1 a join tbl1 b on a.id+1 = b.id

That's one way....

If, however, you do not know that your first section will always be 3 digits, but you can define your first section as whatever digits come before the first underscore.... then you can construct a substring() evaluation with the end position being the charindex() of the first underscore found in the value. In the case that there is no underscore (as in rows 1-5) charindex() will return 0, so you would need to check for that as well.

If it turns out you need the charindex, and need help, check back and we can go from there.

Have fun!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources