locked
How to identify when there is a break in continuity of an integer RRS feed

  • Question

  • Hi guys, 

    Can you give me an idea on how to solve the following issue?

    column01       column02       column03       column04      

    1                    001                 002                  0                       

    2                    003                 004                  0

    3                    009                 010                  1

    4                    015                 018                  1

    5                    019                 020                  0

    so basically the fourth column (column04) is what I want to get if I am given the three columns. The flag 0 or 1 will be applied in the forth column if column02 is not a follow up number of the previous row's column03 value. 

    example, column04 is o for the second row because column03 os the first row is 2 and column02 of the second row is 3...hence 3 is the next number to 2.

    thanks in advance


    ebro

    Tuesday, January 6, 2015 10:06 PM

Answers

  • Try this:

    DECLARE @table TABLE (column01 INT, column02 INT, column03 INT)
    INSERT INTO @table (column01, column02, column03)
    VALUES (1, 001, 002),(2, 003, 004),(3, 009, 010),(4, 015, 018),(5, 019, 020)     
    
    -->=2012
    SELECT *, CASE WHEN LAG(column03) OVER (ORDER BY column01) +1 <> column02 THEN 1 ELSE 0 END
      FROM @table   
      
    --<=2008  
    SELECT t.*, CASE WHEN t2.column03 + 1 <> t.column02 THEN 1 ELSE 0 END
      FROM @table t
        LEFT OUTER JOIN @table t2
    	ON t.column01 = t2.column01 + 1

    The >=2012 version will only work on SQL Server 2012 or newer, and only with databases set to 2012 compatability mode.

    • Proposed as answer by Vaibhav.Chaudhari Wednesday, January 7, 2015 1:38 PM
    • Marked as answer by ebrolove Wednesday, January 7, 2015 2:59 PM
    Tuesday, January 6, 2015 10:17 PM
  • create table test  (column01 INT, column02 INT, column03 INT)
    INSERT INTO test (column01, column02, column03)
    VALUES (1, 1, 2),(2, 3, 4),(3, 9, 10),(4, 15, 18),(5, 19, 20)     
    
    
    
    SELECT t.*, CASE WHEN t.column02 - d.column02 <>1 Then 1 Else 0 END as column04
      FROM test t
      outer apply (select top 1 column03 from test where column01<t.column01 Order by column01 desc) d(column02)
    
    drop table test
    

    • Marked as answer by Charlie Liao Tuesday, January 13, 2015 9:47 AM
    Wednesday, January 7, 2015 4:04 PM

All replies

  • Try this:

    DECLARE @table TABLE (column01 INT, column02 INT, column03 INT)
    INSERT INTO @table (column01, column02, column03)
    VALUES (1, 001, 002),(2, 003, 004),(3, 009, 010),(4, 015, 018),(5, 019, 020)     
    
    -->=2012
    SELECT *, CASE WHEN LAG(column03) OVER (ORDER BY column01) +1 <> column02 THEN 1 ELSE 0 END
      FROM @table   
      
    --<=2008  
    SELECT t.*, CASE WHEN t2.column03 + 1 <> t.column02 THEN 1 ELSE 0 END
      FROM @table t
        LEFT OUTER JOIN @table t2
    	ON t.column01 = t2.column01 + 1

    The >=2012 version will only work on SQL Server 2012 or newer, and only with databases set to 2012 compatability mode.

    • Proposed as answer by Vaibhav.Chaudhari Wednesday, January 7, 2015 1:38 PM
    • Marked as answer by ebrolove Wednesday, January 7, 2015 2:59 PM
    Tuesday, January 6, 2015 10:17 PM
  • Thanks Patrick!....

    to the point!!!!


    ebro

    Wednesday, January 7, 2015 3:00 PM
  • create table test  (column01 INT, column02 INT, column03 INT)
    INSERT INTO test (column01, column02, column03)
    VALUES (1, 1, 2),(2, 3, 4),(3, 9, 10),(4, 15, 18),(5, 19, 20)     
    
    
    
    SELECT t.*, CASE WHEN t.column02 - d.column02 <>1 Then 1 Else 0 END as column04
      FROM test t
      outer apply (select top 1 column03 from test where column01<t.column01 Order by column01 desc) d(column02)
    
    drop table test
    

    • Marked as answer by Charlie Liao Tuesday, January 13, 2015 9:47 AM
    Wednesday, January 7, 2015 4:04 PM
  • Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI', 'ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI', 'ISO Standard SQL. And you need to read and download the PDF for: 
    https:www.simple-talk.com/books/sql-books/119-sql-code-smells/

    >> Can you give me an idea on how to solve the following issue? <<

    No, we have to only guess. Why did you fail to post DDL? The silly picture of a table you posted has no name, no keys, no constraints. Yet you expect other people to read your mind.  

    Why are you using flags in SQL? That was assembly language.  SQL is based on logic and predicates; we like to detect the actual state of being in the schema. Your ASCII picture shows leading zeros on some of th4e columns, so they have to be strings; sure would help if you told us if this guess is right. 

    Here is my first attempt at doing what you shroud have done if you were polite: 

    CREATE TABLE Foobars
    (foobar_seq INTEGER NOT NULL PRIMARY KEY, -- did I guess right?? 
     col_2 CHAR(3) NOT NULL CHECK(col_2 LIKE '[0-9][0-9][0-9])
     col_3 CHAR(3) NOT NULL CHECK(col_3 LIKE '[0-9][0-9][0-9])
    -- ,stupid_flg INTEGER NOT NULL CHECK(stupid_flg IN (0, 1)) -- drop this column! 
    );

    INSERT INTO Foobars
    VALUES
    (1, '001', '002', 0), 
    (2, '003', '004', 0), 
    (3, '009', '010', 1), 
    (4, '015', '018', 1), 
    (5, '019', '020', 0), 

    >> so basically the fourth column (stupid_flg) is what I want to get if I am given the three columns. The flag 0 or 1 will be applied in the forth column if col_2 is not a follow up number of the previous row's col_3 value. <<

    Does “follow up number” mean that we are supposed to convert the string to a numeric, and look for a successor? Is col_1 actually the key? 

    Did you know that SQL is based on sets? Sets are mathematical structures which have no ordering. This whole exercise is highly suspect. What does the sequenced pair mean in  your data model?  

    SELECT foobar_seq, col_2, col_3 
           CASE WHEN CAST(col_2 AS INTEGER) +1 = CAST(col_3 AS INTEGER)
                THEN 'TRUE' ELSE 'FALSE' END AS stupid_flg
      FROM Foobars;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, January 7, 2015 8:50 PM
  • Thanks for the advise sir.

    Regards,


    ebro

    Tuesday, February 17, 2015 3:42 PM