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

    Unanswered: Numbering the Date Column

    Create Table Table_date(date_column Date)

    Date_column

    2009-05-01
    2009-05-05
    2009-05-09
    2009-05-10
    2009-05-11
    2009-05-17
    2009-05-18
    2009-05-21
    2009-05-27
    2009-05-29


    I Want To Query The Date Column(result)

    Date_column Id

    2009-05-01 1
    2009-05-05 1
    2009-05-09 1
    2009-05-10 2
    2009-05-11 3
    2009-05-17 1
    2009-05-18 2
    2009-05-21 1
    2009-05-27 1
    2009-05-28 2

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What is the logic to get these id field.

    And why is the last row not 29, but 28 ?



    Quote Originally Posted by laknar
    Create Table Table_date(date_column Date)

    Date_column

    2009-05-01
    2009-05-05
    2009-05-09
    2009-05-10
    2009-05-11
    2009-05-17
    2009-05-18
    2009-05-21
    2009-05-27
    2009-05-29


    I Want To Query The Date Column(result)

    Date_column Id

    2009-05-01 1
    2009-05-05 1
    2009-05-09 1
    2009-05-10 2
    2009-05-11 3
    2009-05-17 1
    2009-05-18 2
    2009-05-21 1
    2009-05-27 1
    2009-05-28 2
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    Create Table Table_date(date_column Date)

    Date_column

    2009-05-01
    2009-05-05
    2009-05-09
    2009-05-10
    2009-05-11
    2009-05-17
    2009-05-18
    2009-05-21
    2009-05-27
    2009-05-28


    I Want To Query The Date Column(result)

    Date_column Id

    2009-05-01 1
    2009-05-05 1
    2009-05-09 1
    2009-05-10 2
    2009-05-11 3
    2009-05-17 1
    2009-05-18 2
    2009-05-21 1
    2009-05-27 1
    2009-05-28 2

    sorry it was a mistake.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It would be possible by two step calculations. Like:
    (Updated indentions)
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT date_column
         , DAYS(date_column)
           - MAX(CASE
                 WHEN cont_flag = 0 THEN
                      DAYS(date_column)
                 END)
             OVER(ORDER BY date_column
                  ROWS BETWEEN UNBOUNDED PRECEDING
                           AND CURRENT ROW)
           + 1  AS id
      FROM (
           SELECT date_column
                , CASE
                  WHEN MAX(date_column)
                       OVER(ORDER BY date_column
                            ROWS BETWEEN 1 PRECEDING
                                     AND 1 PRECEDING)
                       = date_column - 1 DAY THEN
                       1
                  ELSE 0
                  END  AS cont_flag
             FROM Table_date
           ) S
    ;
    ------------------------------------------------------------------------------
    
    DATE_COLUMN ID         
    ----------- -----------
    2009-05-01            1
    2009-05-05            1
    2009-05-09            1
    2009-05-10            2
    2009-05-11            3
    2009-05-17            1
    2009-05-18            2
    2009-05-21            1
    2009-05-27            1
    2009-05-28            2
    
      10 record(s) selected.
    I'll try to find simpler way.
    Last edited by tonkuma; 06-15-09 at 09:46.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you are using DB2 for LUW 9.5,
    you can use LEAD(date_column) OVER(ORDER BY date_column) olap function instead of:
    Code:
                  WHEN MAX(date_column)
                       OVER(ORDER BY date_column
                            ROWS BETWEEN 1 PRECEDING
                                     AND 1 PRECEDING)

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    recursive_cte(date_column, id) AS (
    SELECT date_column, 1
      FROM Table_date
    UNION ALL
    SELECT pre.date_column
         , id + 1
      FROM recursive_cte pre
         , Table_date    new
     WHERE id < 9999
       AND new.date_column = pre.date_column - pre.id DAYS
    )
    SELECT date_column
         , MAX(id)     AS id
      FROM recursive_cte
     GROUP BY
           date_column
    ;
    ------------------------------------------------------------------------------
    
    DATE_COLUMN ID         
    ----------- -----------
    2009-05-01            1
    2009-05-05            1
    2009-05-09            1
    2009-05-10            2
    2009-05-11            3
    2009-05-17            1
    2009-05-18            2
    2009-05-21            1
    2009-05-27            1
    2009-05-28            2
    
      10 record(s) selected.

  7. #7
    Join Date
    Jul 2008
    Posts
    94
    so far i have tried row number() and rank() functions.
    but i haven't tried aggregation function.
    I'm learning lot from you as well as from dbforums.

    thanks a lot for the query, it worked great.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT date_column
         , DAYS(date_column)
           - (SELECT MAX( DAYS(date_column) )
                FROM Table_date s
               WHERE s.date_column <= t.date_column
                 AND NOT EXISTS
                     (SELECT *
                        FROM Table_date r
                       WHERE r.date_column = s.date_column - 1 DAY
                     )
             )
           + 1  AS id
      FROM Table_date t
    ;
    ------------------------------------------------------------------------------
    
    DATE_COLUMN ID         
    ----------- -----------
    2009-05-01            1
    2009-05-05            1
    2009-05-09            1
    2009-05-10            2
    2009-05-11            3
    2009-05-17            1
    2009-05-18            2
    2009-05-21            1
    2009-05-27            1
    2009-05-28            2
    
      10 record(s) selected.

Posting Permissions

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