Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: How to derive this column for immediate duplicates?.

    I have a requirement to derive a column based on the key columns by sorting the UPDATED_DT column.

    The key columns are DEPT_CD,STATUS,START_DT. I have tried all the Analytic functions but couldn't get to that. any help would be greatly appreciated.

    Note:- the Column DUPL in output need to be derived.

    Input

    Code:
    DEPT_CD,STATUS,START_DT,UPDATED_DT
    100,Active    ,Jan 10 2013,Jan 10 2013 9:56:18 AM 000000
    100,Pending   ,Jan 10 2013,Jan 10 2013 9:56:19 AM 000000
    100,Pending   ,Jan 10 2013,Jan 10 2013 9:56:20 AM 000000
    100,Queued    ,Jan 10 2013,Jan 10 2013 9:56:21 AM 000000
    100,Closed    ,Jan 10 2013,Jan 10 2013 9:56:22 AM 000000
    100,Pending   ,Jan 10 2013,Jan 10 2013 9:56:23 AM 000000
    100,Pending   ,Jan 10 2013,Jan 10 2013 9:56:24 AM 000000
    Output

    Code:
    DEPT_CD,STATUS,START_DT,UPDATED_DT,DUPL
    100,Active    ,Jan 10 2013,Jan 10 2013 9:56:18 AM 000000, 1
    100,Pending   ,Jan 10 2013,Jan 10 2013 9:56:19 AM 000000, 1
    100,Pending   ,Jan 10 2013,Jan 10 2013 9:56:20 AM 000000, 2
    100,Queued    ,Jan 10 2013,Jan 10 2013 9:56:21 AM 000000, 1
    100,Closed    ,Jan 10 2013,Jan 10 2013 9:56:22 AM 000000, 1
    100,Pending   ,Jan 10 2013,Jan 10 2013 9:56:23 AM 000000, 1
    100,Pending   ,Jan 10 2013,Jan 10 2013 9:56:24 AM 000000, 2
    Cheers,
    Laknar

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    Have you tried row_number () over() function?

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    Yes. Here row 2,3,6,7 key columns are same.so row_number ordering from 1 to 4.
    But the requirement is row 2 and 3 should be derived as 1&2 and similarly for row 6&7.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some questions.

    (1) Are the values of derived column always 1 or 2? Are there 3, 4, so on...?
    If there are 3, 4, so on, please show the examples additional to your original example.

    (2) Why the derived column of row 6, 7 were not 3, 4?
    How to know the count(the values of derived column) should be reset to 1 for row 6?

  5. #5
    Join Date
    Jul 2008
    Posts
    94
    1. Yes it is possible if we have consecutive duplicates

    Input
    DEPT_CD,STATUS,START_DT,UPDATED_DT
    100,Active ,Jan 10 2013,Jan 10 2013 9:56:18 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:19 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:20 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:21 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:22 AM 000000
    100,Queued ,Jan 10 2013,Jan 10 2013 9:56:23 AM 000000
    100,Closed ,Jan 10 2013,Jan 10 2013 9:56:24 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:25 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:26 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:27 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:28 AM 000000

    Output
    DEPT_CD,STATUS,START_DT,UPDATED_DT,DUPL
    100,Active ,Jan 10 2013,Jan 10 2013 9:56:18 AM 000000, 1
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:19 AM 000000, 1
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:20 AM 000000, 2
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:21 AM 000000, 3
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:22 AM 000000, 4
    100,Queued ,Jan 10 2013,Jan 10 2013 9:56:23 AM 000000, 1
    100,Closed ,Jan 10 2013,Jan 10 2013 9:56:24 AM 000000, 1
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:25 AM 000000, 1
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:26 AM 000000, 2
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:27 AM 000000, 3
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:28 AM 000000, 4

    2. My objective is to retain only one record if there is consecutive duplicates for DEPT_CD,STATUS,START_DT combination. If anyone of this column value changes DEPT_CD,STATUS,START_DT then reset to 1.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In other words,
    How to know "immediate duplicates"?

    For examples:
    (1) updated_dt within 1 second having same (DEPT_CD,STATUS,START_DT).
    or
    (2) No other rows having different (DEPT_CD,STATUS,START_DT) between the rows.

  7. #7
    Join Date
    Jul 2008
    Posts
    94
    (2) No other rows having different (DEPT_CD,STATUS,START_DT) between the rows.

  8. #8
    Join Date
    Aug 2013
    Posts
    1
    have a problem with query...anybody willing to help me???

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by laknar View Post
    1. Yes it is possible if we have consecutive duplicates

    Input
    DEPT_CD,STATUS,START_DT,UPDATED_DT
    100,Active ,Jan 10 2013,Jan 10 2013 9:56:18 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:19 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:20 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:21 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:22 AM 000000
    100,Queued ,Jan 10 2013,Jan 10 2013 9:56:23 AM 000000
    100,Closed ,Jan 10 2013,Jan 10 2013 9:56:24 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:25 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:26 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:27 AM 000000
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:28 AM 000000

    Output
    DEPT_CD,STATUS,START_DT,UPDATED_DT,DUPL
    100,Active ,Jan 10 2013,Jan 10 2013 9:56:18 AM 000000, 1
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:19 AM 000000, 1
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:20 AM 000000, 2
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:21 AM 000000, 3
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:22 AM 000000, 4
    100,Queued ,Jan 10 2013,Jan 10 2013 9:56:23 AM 000000, 1
    100,Closed ,Jan 10 2013,Jan 10 2013 9:56:24 AM 000000, 1
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:25 AM 000000, 1
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:26 AM 000000, 2
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:27 AM 000000, 3
    100,Pending ,Jan 10 2013,Jan 10 2013 9:56:28 AM 000000, 4

    2. My objective is to retain only one record if there is consecutive duplicates for DEPT_CD,STATUS,START_DT combination. If anyone of this column value changes DEPT_CD,STATUS,START_DT then reset to 1.
    A bit simplified, ignoring dep and ignoring start_dt.

    Code:
    with t (STATUS,UPDATED_DT) as (
        values ('Active' , current_timestamp + 0 seconds)
            ,  ('Pending' , current_timestamp + 1 seconds)
            ,  ('Pending' , current_timestamp + 2 seconds)
            ,  ('Pending' , current_timestamp + 3 seconds)
            ,  ('Pending' , current_timestamp + 4 seconds)
            ,  ('Queued'  , current_timestamp + 5 seconds)
            ,  ('Closed'  , current_timestamp + 6 seconds)
            ,  ('Pending' , current_timestamp + 7 seconds)
            ,  ('Pending' , current_timestamp + 8 seconds)
            ,  ('Pending' , current_timestamp + 9 seconds)
            ,  ('Pending' , current_timestamp + 10 seconds)
    )
    select STATUS, UPDATED_DT
        ,  row_number() over (partition by grp_order
                              order by UPDATED_DT)
    from (
        select STATUS, UPDATED_DT
            ,  row_number() over (order by UPDATED_DT) 
            -  row_number() over (partition by STATUS order by UPDATED_DT) as grp_order
        from t
    )
    order by UPDATED_DT;
    
    STATUS  UPDATED_DT                 3                   
    ------- -------------------------- --------------------
    Active  2013-08-23-08.26.48.447870                    1
    Pending 2013-08-23-08.26.49.447870                    1
    Pending 2013-08-23-08.26.50.447870                    2
    Pending 2013-08-23-08.26.51.447870                    3
    Pending 2013-08-23-08.26.52.447870                    4
    Queued  2013-08-23-08.26.53.447870                    1
    Closed  2013-08-23-08.26.54.447870                    1
    Pending 2013-08-23-08.26.55.447870                    1
    Pending 2013-08-23-08.26.56.447870                    2
    Pending 2013-08-23-08.26.57.447870                    3
    Pending 2013-08-23-08.26.58.447870                    4
    
      11 record(s) selected.
    --
    Lennart

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Lennart,

    If more data were added,
    some grp_order might be same even if they should be in different group.

    Example: The data may be not proper.
    So, I hope OP(Laknar) validate the data.
    Code:
    ------------------------------ Commands Entered ------------------------------
    with t (STATUS,UPDATED_DT) as (
        values ('Active'  , current_timestamp +  0 seconds)
            ,  ('Pending' , current_timestamp +  1 seconds)
            ,  ('Pending' , current_timestamp +  2 seconds)
            ,  ('Pending' , current_timestamp +  3 seconds)
            ,  ('Pending' , current_timestamp +  4 seconds)
            ,  ('Queued'  , current_timestamp +  5 seconds)
            ,  ('Closed'  , current_timestamp +  6 seconds)
            ,  ('Pending' , current_timestamp +  7 seconds)
            ,  ('Pending' , current_timestamp +  8 seconds)
            ,  ('Pending' , current_timestamp +  9 seconds)
            ,  ('Pending' , current_timestamp + 10 seconds)
            ,  ('Queued'  , current_timestamp + 11 seconds)
            ,  ('Active'  , current_timestamp + 12 seconds)
            ,  ('Active'  , current_timestamp + 13 seconds)
            ,  ('Pending' , current_timestamp + 14 seconds)
            ,  ('Pending' , current_timestamp + 15 seconds)
            ,  ('Pending' , current_timestamp + 16 seconds)
            ,  ('Closed'  , current_timestamp + 17 seconds)
    )
    select STATUS, UPDATED_DT
        ,  row_number() over (partition by grp_order
                              order by UPDATED_DT)
        , grp_order
    from (
        select STATUS, UPDATED_DT
            ,  row_number() over (order by UPDATED_DT) 
            -  row_number() over (partition by STATUS order by UPDATED_DT) as grp_order
        from t
    )
    order by UPDATED_DT
    ;
    ------------------------------------------------------------------------------
    
    STATUS  UPDATED_DT                 3                    GRP_ORDER           
    ------- -------------------------- -------------------- --------------------
    Active  2013-08-23-15.48.59.214000                    1                    0
    Pending 2013-08-23-15.49.00.214000                    1                    1
    Pending 2013-08-23-15.49.01.214000                    2                    1
    Pending 2013-08-23-15.49.02.214000                    3                    1
    Pending 2013-08-23-15.49.03.214000                    4                    1
    Queued  2013-08-23-15.49.04.214000                    1                    5
    Closed  2013-08-23-15.49.05.214000                    1                    6
    Pending 2013-08-23-15.49.06.214000                    1                    3
    Pending 2013-08-23-15.49.07.214000                    2                    3
    Pending 2013-08-23-15.49.08.214000                    3                    3
    Pending 2013-08-23-15.49.09.214000                    4                    3
    Queued  2013-08-23-15.49.10.214000                    1                   10
    Active  2013-08-23-15.49.11.214000                    1                   11
    Active  2013-08-23-15.49.12.214000                    2                   11
    Pending 2013-08-23-15.49.13.214000                    2                    6
    Pending 2013-08-23-15.49.14.214000                    3                    6
    Pending 2013-08-23-15.49.15.214000                    4                    6
    Closed  2013-08-23-15.49.16.214000                    1                   16
    
      18 record(s) selected.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is another example with additional data.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Input
    ( dept_cd , status , start_dt , updated_dt ) AS (
    SELECT dept_cd , status
         , DATE( TO_DATE(start_dt , 'Mon dd yyyy') )
         , TO_DATE(updated_dt , 'Mon dd yyyy hh:mi:ss ff6')
     FROM  (VALUES
              ( 100 , 'Active'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:18 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:19 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:20 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:21 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:22 000000' )
            , ( 100 , 'Queued'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:23 000000' )
            , ( 100 , 'Closed'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:24 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:25 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:26 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:27 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:28 000000' )
    
            , ( 100 , 'Queued'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:30 000000' )
            , ( 100 , 'Active'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:31 000000' )
            , ( 100 , 'Active'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:32 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:33 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:34 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:35 000000' )
            , ( 100 , 'Closed'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:36 000000' )
           ) AS t( dept_cd , status , start_dt , updated_dt )
    )
    SELECT dept_cd , status , start_dt , updated_dt
         , rnum + 1
           - MAX(CASE
                 WHEN lag_status <> status THEN
                      rnum
                 END)
                OVER( ORDER BY updated_dt ) AS dupl
     FROM  (SELECT t.*
                 , ROW_NUMBER()
                      OVER( ORDER BY updated_dt ) AS rnum
                 , LAG(status , 1 , '')
                      OVER( ORDER BY updated_dt ) AS lag_status
             FROM  Input AS t
           )
    ;
    ------------------------------------------------------------------------------
    
    DEPT_CD     STATUS  START_DT   UPDATED_DT                 DUPL                
    ----------- ------- ---------- -------------------------- --------------------
            100 Active  2013-01-10 2013-01-10-09.56.18.000000                    1
            100 Pending 2013-01-10 2013-01-10-09.56.19.000000                    1
            100 Pending 2013-01-10 2013-01-10-09.56.20.000000                    2
            100 Pending 2013-01-10 2013-01-10-09.56.21.000000                    3
            100 Pending 2013-01-10 2013-01-10-09.56.22.000000                    4
            100 Queued  2013-01-10 2013-01-10-09.56.23.000000                    1
            100 Closed  2013-01-10 2013-01-10-09.56.24.000000                    1
            100 Pending 2013-01-10 2013-01-10-09.56.25.000000                    1
            100 Pending 2013-01-10 2013-01-10-09.56.26.000000                    2
            100 Pending 2013-01-10 2013-01-10-09.56.27.000000                    3
            100 Pending 2013-01-10 2013-01-10-09.56.28.000000                    4
            100 Queued  2013-01-10 2013-01-10-09.56.30.000000                    1
            100 Active  2013-01-10 2013-01-10-09.56.31.000000                    1
            100 Active  2013-01-10 2013-01-10-09.56.32.000000                    2
            100 Pending 2013-01-10 2013-01-10-09.56.33.000000                    1
            100 Pending 2013-01-10 2013-01-10-09.56.34.000000                    2
            100 Pending 2013-01-10 2013-01-10-09.56.35.000000                    3
            100 Closed  2013-01-10 2013-01-10-09.56.36.000000                    1
    
      18 record(s) selected.

  12. #12
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by tonkuma View Post
    Lennart,

    If more data were added,
    some grp_order might be same even if they should be in different group.
    Ah, yes I overlooked that. Thanks for pointing it out. One need to partition with STATUS in the outer select:

    Code:
    with t (STATUS,UPDATED_DT) as (
        values ('Active'  , current_timestamp +  0 seconds)
            ,  ('Pending' , current_timestamp +  1 seconds)
            ,  ('Pending' , current_timestamp +  2 seconds)
            ,  ('Pending' , current_timestamp +  3 seconds)
            ,  ('Pending' , current_timestamp +  4 seconds)
            ,  ('Queued'  , current_timestamp +  5 seconds)
            ,  ('Closed'  , current_timestamp +  6 seconds)
            ,  ('Pending' , current_timestamp +  7 seconds)
            ,  ('Pending' , current_timestamp +  8 seconds)
            ,  ('Pending' , current_timestamp +  9 seconds)
            ,  ('Pending' , current_timestamp + 10 seconds)
            ,  ('Queued'  , current_timestamp + 11 seconds)
            ,  ('Active'  , current_timestamp + 12 seconds)
            ,  ('Active'  , current_timestamp + 13 seconds)
            ,  ('Pending' , current_timestamp + 14 seconds)
            ,  ('Pending' , current_timestamp + 15 seconds)
            ,  ('Pending' , current_timestamp + 16 seconds)
            ,  ('Closed'  , current_timestamp + 17 seconds)
    )
    select STATUS, UPDATED_DT
        ,  row_number() over (partition by status, grp_order
                              order by UPDATED_DT)
        , grp_order
    from (
        select STATUS, UPDATED_DT
            ,  row_number() over (order by UPDATED_DT) 
            -  row_number() over (partition by STATUS order by UPDATED_DT) as grp_order
        from t
    )
    order by UPDATED_DT
    ;
    
    STATUS  UPDATED_DT                 3                    GRP_ORDER           
    ------- -------------------------- -------------------- --------------------
    Active  2013-08-23-09.14.47.537714                    1                    0
    Pending 2013-08-23-09.14.48.537714                    1                    1
    Pending 2013-08-23-09.14.49.537714                    2                    1
    Pending 2013-08-23-09.14.50.537714                    3                    1
    Pending 2013-08-23-09.14.51.537714                    4                    1
    Queued  2013-08-23-09.14.52.537714                    1                    5
    Closed  2013-08-23-09.14.53.537714                    1                    6
    Pending 2013-08-23-09.14.54.537714                    1                    3
    Pending 2013-08-23-09.14.55.537714                    2                    3
    Pending 2013-08-23-09.14.56.537714                    3                    3
    Pending 2013-08-23-09.14.57.537714                    4                    3
    Queued  2013-08-23-09.14.58.537714                    1                   10
    Active  2013-08-23-09.14.59.537714                    1                   11
    Active  2013-08-23-09.15.00.537714                    2                   11
    Pending 2013-08-23-09.15.01.537714                    1                    6
    Pending 2013-08-23-09.15.02.537714                    2                    6
    Pending 2013-08-23-09.15.03.537714                    3                    6
    Closed  2013-08-23-09.15.04.537714                    1                   16
    
      18 record(s) selected.
    --
    Lennart

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    2. My objective is to retain only one record if there is consecutive duplicates for DEPT_CD,STATUS,START_DT combination.
    If anyone of this column value changes DEPT_CD,STATUS,START_DT then reset to 1.
    If you want to konw only that a row was duplicated or no,
    somemore simpler query might be possible.

    Example 2: Added A.M. to data. Slightly changed "CASE LAG(status , 1 , '') ... " to "CASE LAG(status) ... "
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Input
    ( dept_cd , status , start_dt , updated_dt ) AS (
    SELECT dept_cd , status
         , DATE( TO_DATE(start_dt , 'Mon dd yyyy') )
         , TO_DATE(updated_dt , 'Mon dd yyyy hh:mi:ss A.M. ff6')
     FROM  (VALUES
              ( 100 , 'Active'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:18 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:19 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:20 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:21 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:22 A.M. 000000' )
            , ( 100 , 'Queued'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:23 A.M. 000000' )
            , ( 100 , 'Closed'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:24 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:25 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:26 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:27 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:28 A.M. 000000' )
    
            , ( 100 , 'Queued'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:30 A.M. 000000' )
            , ( 100 , 'Active'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:31 A.M. 000000' )
            , ( 100 , 'Active'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:32 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:33 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:34 A.M. 000000' )
            , ( 100 , 'Pending' , 'Jan 10 2013' , 'Jan 10 2013 9:56:35 A.M. 000000' )
            , ( 100 , 'Closed'  , 'Jan 10 2013' , 'Jan 10 2013 9:56:36 A.M. 000000' )
           ) AS t( dept_cd , status , start_dt , updated_dt )
    )
    SELECT t.*
         , CASE LAG(status)
                   OVER( ORDER BY updated_dt )
           WHEN status THEN
                'Duplicated'
           ELSE ''
           END  AS dupl
     FROM  Input AS t
     ORDER BY
           updated_dt
    ;
    ------------------------------------------------------------------------------
    
    DEPT_CD     STATUS  START_DT   UPDATED_DT                 DUPL      
    ----------- ------- ---------- -------------------------- ----------
            100 Active  2013-01-10 2013-01-10-09.56.18.000000           
            100 Pending 2013-01-10 2013-01-10-09.56.19.000000           
            100 Pending 2013-01-10 2013-01-10-09.56.20.000000 Duplicated
            100 Pending 2013-01-10 2013-01-10-09.56.21.000000 Duplicated
            100 Pending 2013-01-10 2013-01-10-09.56.22.000000 Duplicated
            100 Queued  2013-01-10 2013-01-10-09.56.23.000000           
            100 Closed  2013-01-10 2013-01-10-09.56.24.000000           
            100 Pending 2013-01-10 2013-01-10-09.56.25.000000           
            100 Pending 2013-01-10 2013-01-10-09.56.26.000000 Duplicated
            100 Pending 2013-01-10 2013-01-10-09.56.27.000000 Duplicated
            100 Pending 2013-01-10 2013-01-10-09.56.28.000000 Duplicated
            100 Queued  2013-01-10 2013-01-10-09.56.30.000000           
            100 Active  2013-01-10 2013-01-10-09.56.31.000000           
            100 Active  2013-01-10 2013-01-10-09.56.32.000000 Duplicated
            100 Pending 2013-01-10 2013-01-10-09.56.33.000000           
            100 Pending 2013-01-10 2013-01-10-09.56.34.000000 Duplicated
            100 Pending 2013-01-10 2013-01-10-09.56.35.000000 Duplicated
            100 Closed  2013-01-10 2013-01-10-09.56.36.000000           
    
      18 record(s) selected.
    Last edited by tonkuma; 08-23-13 at 04:47. Reason: Added "ORDER BY updated_dt". Added A.M. to data. Slightly changed "CASE LAG(status , 1 , '') ... " to "CASE LAG(status) ... "

Posting Permissions

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