Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Posts
    19

    Unanswered: Isolate repititive records from a sequence

    COL1 COL2

    FNCP_LGT_UW2ON 0
    FNCP_LGT_UW2ON -1
    FNCP_LGT_UW2ON 0
    FNCP_LGT_UW2ON 0
    FNCP_LGT_UW2ON 0
    FNCP_LGT_UW2ON 0
    FNCP_LGT_UW2ON 0
    FNCP_LGT_UW1ON 0
    FNCP_LGT_UW1ON -1
    FNCP_LGT_UW1ON 0
    FNCP_LGT_UW1ON 0
    FNCP_LGT_UW1ON 0
    FNCP_LGT_UW1ON 0
    FNCP_LGT_UW1ON 0
    FNCP_FLTRP2T 0
    FNCP_FLTRP2T 0
    FNCP_FLTRP2T 0
    FNCP_FLTRP2T 0
    FNCP_FLTRP2T 0
    FNCP_FLTRP2RST -1
    FNCP_FLTRP2RST -1
    FNCP_FLTRP2RST -1
    FNCP_FLTRP2RST -1
    FNCP_FLTRP2RST -1
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H 0
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H 0
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H 0
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H 0
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H 0
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H 0
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H 0
    FNCP_FLTRP2R2H -1
    FNCP_FLTRP2R2H 0
    FNCP_FLTRP2R2H 0
    FNCP_FLTRP2R -1
    FNCP_FLTRP2R 0
    FNCP_FLTRP2R -1
    FNCP_FLTRP2R 0
    FNCP_FLTRP2R -1
    FNCP_FLTRP2R -1
    FNCP_FLTRP2R -1
    FNCP_FLTRP2R 0
    FNCP_FLTRP2R -1
    FNCP_FLTRP2R 0
    FNCP_FLTRP2R -1
    FNCP_FLTRP2R -1
    FNCP_FLTRP2R 0
    FNCP_FLTRP2R -1
    FNCP_FLTRP2R 0
    FNCP_FLTRP2R -1
    FNCP_FLTRP2R 0
    FNCP_FLTRP2R 0
    FNCP_FLTRP1T 0
    FNCP_FLTRP1T 0
    FNCP_FLTRP1T 0
    FNCP_FLTRP1T 0
    FNCP_FLTRP1T 0
    FNCP_FLTRP1RST -1
    FNCP_FLTRP1RST -1
    FNCP_FLTRP1RST -1
    FNCP_FLTRP1RST -1
    FNCP_FLTRP1RST -1
    FNCP_FLTRP1R2H 0
    FNCP_FLTRP1R2H 0
    FNCP_FLTRP1R2H 0
    FNCP_FLTRP1R2H 0
    FNCP_FLTRP1R2H 0

    I am recording a sequence of ON/FF conditions whoch are recorded as -1 and 0 in my system. The valid sequence should be either -1,0,-1,0... or 0,-1,0,-1... etc. In short there has to be alternative -1, 0 or 0,-1.

    If any record repeats continuously more than once, only the first record should be retained and the rest should be deleted untill the status changes. For example, a sequence like -1, -1, -1, 0, 0, 0, -1... should become -1,0,-1. Can some one help me with the correct query to isolate the unwanted records?. Sorry, i am new to SQL. Thanks.
    Last edited by alamsha; 01-17-09 at 04:04.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    What is/are the key(s) of your table?

    What column(s) tell us the sequence? There is no obvious sequence in your sample data other than the order you wrote them in. Tables have no inherent order so there is no "first" or "next" row unless you have columns to record that fact.

  3. #3
    Join Date
    Jan 2009
    Posts
    19
    This is how the table looks like.

    TagKey TagID TagvALUE TagTimeStamp

    20081015060558625744 PAMA2_FDRPL13_OFF 0 2008/10/15 06:05:46.253
    20081014180816312237 PAMA2_FDRPL13_OFF -1 2008/10/14 18:08:02.785
    20081013073646687873 PAMA2_FDRPL13_OFF 0 2008/10/13 06:05:47.020
    20081012060554500466 PAMA2_FDRPL13_OFF 0 2008/10/12 06:05:40.890

    As i am new to SQL, i may not be able to answer too technical questions. But, i hope anybody can understand the basic problem from my simple explanation.

    I request you to consider any table of your convenience with similar data and kindly give your solution.
    Last edited by alamsha; 01-17-09 at 04:29.

  4. #4
    Join Date
    Jan 2009
    Posts
    19
    As i am new to SQL, i may not be able to answer too technical queries. But, i hope anybody can understand the basic problem from my simple explanation.

    I request you to consider any table of your convenience with similar data and kindly give your solution.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What version of SQL Server?
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2009
    Posts
    19
    MS SQL Express 2008

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This any good?
    Code:
    DECLARE @date datetime
        SET @date = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
    
    DECLARE @seq table (
       col1     char(14)
     , col2     smallint
     , recorded datetime
    )
    
    INSERT INTO @seq (col1, col2, recorded)
          SELECT 'FNCP_LGT_UW2ON',  0, DateAdd(dd,  0, @date)
    UNION SELECT 'FNCP_LGT_UW2ON', -1, DateAdd(dd,  1, @date)
    UNION SELECT 'FNCP_LGT_UW2ON',  0, DateAdd(dd,  2, @date)
    UNION SELECT 'FNCP_LGT_UW2ON',  0, DateAdd(dd,  3, @date)
    UNION SELECT 'FNCP_LGT_UW2ON',  0, DateAdd(dd,  4, @date)
    UNION SELECT 'FNCP_LGT_UW2ON',  0, DateAdd(dd,  5, @date)
    UNION SELECT 'FNCP_LGT_UW2ON',  0, DateAdd(dd,  6, @date)
    UNION SELECT 'FNCP_LGT_UW1ON',  0, DateAdd(dd,  7, @date)
    UNION SELECT 'FNCP_LGT_UW1ON', -1, DateAdd(dd,  8, @date)
    UNION SELECT 'FNCP_LGT_UW1ON',  0, DateAdd(dd,  9, @date)
    UNION SELECT 'FNCP_LGT_UW1ON',  0, DateAdd(dd, 10, @date)
    UNION SELECT 'FNCP_LGT_UW1ON',  0, DateAdd(dd, 11, @date)
    UNION SELECT 'FNCP_LGT_UW1ON',  0, DateAdd(dd, 12, @date)
    UNION SELECT 'FNCP_LGT_UW1ON',  0, DateAdd(dd, 13, @date)
    UNION SELECT 'FNCP_FLTRP2T'  ,  0, DateAdd(dd,  0, @date)
    UNION SELECT 'FNCP_FLTRP2T'  ,  0, DateAdd(dd,  1, @date)
    UNION SELECT 'FNCP_FLTRP2T'  ,  0, DateAdd(dd,  2, @date)
    UNION SELECT 'FNCP_FLTRP2T'  ,  0, DateAdd(dd,  3, @date)
    UNION SELECT 'FNCP_FLTRP2T'  ,  0, DateAdd(dd,  4, @date)
    UNION SELECT 'FNCP_FLTRP2RST', -1, DateAdd(dd,  0, @date)
    UNION SELECT 'FNCP_FLTRP2RST', -1, DateAdd(dd,  1, @date)
    UNION SELECT 'FNCP_FLTRP2RST', -1, DateAdd(dd,  2, @date)
    UNION SELECT 'FNCP_FLTRP2RST', -1, DateAdd(dd,  3, @date)
    UNION SELECT 'FNCP_FLTRP2RST', -1, DateAdd(dd,  4, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd,  5, @date)
    UNION SELECT 'FNCP_FLTRP2R2H',  0, DateAdd(dd,  6, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd,  7, @date)
    UNION SELECT 'FNCP_FLTRP2R2H',  0, DateAdd(dd,  8, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd,  9, @date)
    UNION SELECT 'FNCP_FLTRP2R2H',  0, DateAdd(dd, 10, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd, 11, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd, 12, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd, 13, @date)
    UNION SELECT 'FNCP_FLTRP2R2H',  0, DateAdd(dd, 14, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd, 15, @date)
    UNION SELECT 'FNCP_FLTRP2R2H',  0, DateAdd(dd, 16, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd, 17, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd, 18, @date)
    UNION SELECT 'FNCP_FLTRP2R2H',  0, DateAdd(dd, 19, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd, 20, @date)
    UNION SELECT 'FNCP_FLTRP2R2H',  0, DateAdd(dd, 21, @date)
    UNION SELECT 'FNCP_FLTRP2R2H', -1, DateAdd(dd, 22, @date)
    UNION SELECT 'FNCP_FLTRP2R2H',  0, DateAdd(dd, 23, @date)
    UNION SELECT 'FNCP_FLTRP2R2H',  0, DateAdd(dd, 24, @date)
    UNION SELECT 'FNCP_FLTRP2R'  , -1, DateAdd(dd,  0, @date)
    UNION SELECT 'FNCP_FLTRP2R'  ,  0, DateAdd(dd,  1, @date)
    UNION SELECT 'FNCP_FLTRP2R'  , -1, DateAdd(dd,  2, @date)
    UNION SELECT 'FNCP_FLTRP2R'  ,  0, DateAdd(dd,  3, @date)
    UNION SELECT 'FNCP_FLTRP2R'  , -1, DateAdd(dd,  4, @date)
    UNION SELECT 'FNCP_FLTRP2R'  , -1, DateAdd(dd,  5, @date)
    UNION SELECT 'FNCP_FLTRP2R'  , -1, DateAdd(dd,  6, @date)
    UNION SELECT 'FNCP_FLTRP2R'  ,  0, DateAdd(dd,  7, @date)
    UNION SELECT 'FNCP_FLTRP2R'  , -1, DateAdd(dd,  8, @date)
    UNION SELECT 'FNCP_FLTRP2R'  ,  0, DateAdd(dd,  9, @date)
    UNION SELECT 'FNCP_FLTRP2R'  , -1, DateAdd(dd, 10, @date)
    UNION SELECT 'FNCP_FLTRP2R'  , -1, DateAdd(dd, 11, @date)
    UNION SELECT 'FNCP_FLTRP2R'  ,  0, DateAdd(dd, 12, @date)
    UNION SELECT 'FNCP_FLTRP2R'  , -1, DateAdd(dd, 13, @date)
    UNION SELECT 'FNCP_FLTRP2R'  ,  0, DateAdd(dd, 14, @date)
    UNION SELECT 'FNCP_FLTRP2R'  , -1, DateAdd(dd, 15, @date)
    UNION SELECT 'FNCP_FLTRP2R'  ,  0, DateAdd(dd, 16, @date)
    UNION SELECT 'FNCP_FLTRP2R'  ,  0, DateAdd(dd, 17, @date)
    UNION SELECT 'FNCP_FLTRP1T'  ,  0, DateAdd(dd, 18, @date)
    UNION SELECT 'FNCP_FLTRP1T'  ,  0, DateAdd(dd, 19, @date)
    UNION SELECT 'FNCP_FLTRP1T'  ,  0, DateAdd(dd, 20, @date)
    UNION SELECT 'FNCP_FLTRP1T'  ,  0, DateAdd(dd, 21, @date)
    UNION SELECT 'FNCP_FLTRP1T'  ,  0, DateAdd(dd, 22, @date)
    UNION SELECT 'FNCP_FLTRP1RST', -1, DateAdd(dd,  0, @date)
    UNION SELECT 'FNCP_FLTRP1RST', -1, DateAdd(dd,  1, @date)
    UNION SELECT 'FNCP_FLTRP1RST', -1, DateAdd(dd,  2, @date)
    UNION SELECT 'FNCP_FLTRP1RST', -1, DateAdd(dd,  3, @date)
    UNION SELECT 'FNCP_FLTRP1RST', -1, DateAdd(dd,  4, @date)
    UNION SELECT 'FNCP_FLTRP1R2H',  0, DateAdd(dd,  5, @date)
    UNION SELECT 'FNCP_FLTRP1R2H',  0, DateAdd(dd,  6, @date)
    UNION SELECT 'FNCP_FLTRP1R2H',  0, DateAdd(dd,  7, @date)
    UNION SELECT 'FNCP_FLTRP1R2H',  0, DateAdd(dd,  8, @date)
    UNION SELECT 'FNCP_FLTRP1R2H',  0, DateAdd(dd,  9, @date)
    
    ; WITH cte AS (
      SELECT col1
           , col2
           , recorded
           , Row_Number() OVER (PARTITION BY col1 ORDER BY recorded ASC) As [n]
      FROM   @seq
    )
    SELECT c1.col1
         , c1.col2
    FROM   cte c1
     INNER
      JOIN cte c2
        ON c1.col1 = c2.col1
       AND c1.n + 1 = c2.n
    WHERE  c1.col2 <> c2.col2
    ORDER
        BY c1.col1
         , c2.recorded ASC
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2009
    Posts
    19
    Thanks georgev. If you can help me on this one, it will solve all my problems.

    http://www.dbforums.com/microsoft-sq...ous-value.html

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I take it what I posted above works then?
    George
    Home | Blog

Posting Permissions

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