# Thread: Can you solve my problem?

1. Registered User
Join Date
Oct 2008
Posts
46

## Unanswered: Can you solve my problem?

I have data like this:

ID Col1 Col2 Col3
1 BS-S001 BS-S004 NULL
2 BS-S005 BS-S011 NULL
3 BS-S011 BS-S020 NULL
4 BS-S021 BS-S024 NULL
5 BS-S024 BS-S036 NULL

I need to script to update Col3 like following output:

ID Col1 Col2 Col3
1 BS-S001 BS-S004 NULL
2 BS-S005 BS-S011 Gap
3 BS-S011 BS-S020 Gap
4 BS-S021 BS-S024 Overlap
5 BS-S024 BS-S036 Overlap

anybody have idea how to verify col1 and Col2 and update Col3.

2. Registered User
Join Date
Nov 2003
Location
London
Posts
169
What's the criteria for GAP and Overlap?

*edit* I think you're wearing a pink hat MCrowley...am I right? :P

3. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
I can probably solve your problem, but just to make it interesting, you must answer a question for me: Tell me what color hat I am wearing.

While you are thinking about that one, can you shed a little more light on the conditions for "Gap" and "Overlap"? It is not terribly intuitive from the sample data.

4. Registered User
Join Date
Oct 2008
Posts
46
col1 is starting num and col2 is ending num
If the starting and ending secuance is diff so i have to update col3
Look at the col1 and col2 's value.

in first record its good....start from 001 and ednd to 004
in second record start from 005 and end 011....but in 3rd record also start from 011 so its duplicate so i have to update col3.

And also, if there is unsequance no. eg. after start 001 end 005 and next record start from 008 and end 010......there is missing 009 so its also gap.

5. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
huh?

So, the first record is always NULL by definition, unless it begins at 002?

If the current record (by ID) starts with the same number, or before the end of the last record, it should be marked "Overlap"?

If the current record (by ID) starts with a number that is greater than 1 + (end of last record), then it should be marked "Gap"?

But the description says
Originally Posted by rajan142
next record start from 008 and end 010......there is missing 009 so its also gap.
Isn't 009 included in that record?

I am still missing something pretty basic here.....

Oh, and SQLSlammer, you are close. But Mauve is the correct name for the color. ;-)

6. Registered User
Join Date
Oct 2008
Posts
46
Lets explain this way:
I have 3 columns in a single table:
BEGDOC,ENDDOC, Remark (these are column name)
BS-S001 BS-S004 NULL >> in this record doc start form 001 and end 004
We don't have to do anything in this record

BS-S006 BS-S011 Gap >> in this record doc start from 006 and end 011 in this record doc 005 is gaping so it has to update on Remark column.

And also

BS-S011 BS-S024 Overlap >> on this record doc 011 is overlap
BS-S024 BS-S036 Overlap >> on this record doc 024 is overlap

7. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
Is you original sample data correct, then? It looks nothing like what you have just posted.

8. Registered User
Join Date
Oct 2008
Posts
46
Here are my data:

DocID BEGDoc ENDDoc Remarks
1 BS-S001 BS-S099 NULL >> It will not Update
2 BS-S100 BS-S110 NULL >> It will not Update
3 BS-S120 BS-S150 NULL >> It has to Update with "Gap"
4 BS-S140 BS-S190 NULL >> It has to Update with "Overlap"

9. Registered User
Join Date
Oct 2008
Posts
46
I think we have to use CURSOR.

I have 1175574 row(s) in that table

10. Registered User
Join Date
Jan 2009
Posts
2
-- pls take a backup of the table first..

update X set remark ='overlap' where docid in(
select b.id from X a join X b on b.docid = a.docid+1 and b.begdoc <= a.enddoc)
-- if the above query is taking long, we can try a WHILE loop if you know the maximum value of DocID

11. Registered User
Join Date
Oct 2008
Posts
46
I gonna try this. But i have two criteria, Gap and Overlap

12. Registered User
Join Date
Oct 2008
Posts
46
I got the Overlap
Can you try Gap also?

13. Registered User
Join Date
Jan 2009
Posts
2
use earlier query, but change the logic to
set a record's Remark to 'Gap' if its Begdoc > the previous record's Enddoc + 1

14. Registered User
Join Date
Oct 2008
Posts
46
I try this but could not get exact record.

update cds_csv set Remarks ='Gap' where DocID in(
select b.DocID from cds_csv a join cds_csv b on b.DocID = a.DocID+1 and a.ENDDOC<=b.BEGDOC)

15. Registered User
Join Date
May 2006
Posts
29