Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    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. #2
    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. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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. #4
    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. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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
    Quote 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. #6
    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. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is you original sample data correct, then? It looks nothing like what you have just posted.

  8. #8
    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. #9
    Join Date
    Oct 2008
    Posts
    46
    I think we have to use CURSOR.

    I have 1175574 row(s) in that table

  10. #10
    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. #11
    Join Date
    Oct 2008
    Posts
    46
    I gonna try this. But i have two criteria, Gap and Overlap

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

  13. #13
    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. #14
    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. #15
    Join Date
    May 2006
    Posts
    29
    how about this:

    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 where a.ENDDOC<b.BEGDOC-1 )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •