Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to display years and months between Jan 1 2008 and current date?

    Hi,
    I would like to write an SELECT statement that would return two columns: year and month that are between Jan 1, 2008 and current date (current date is variable that is changing each day)

    Result should be:
    Code:
    YEAR  MONTH
    ----  -----
    2008      1
    2008      2
    2008      3
    ...
    2011     10
    2011     11
    2011     12   <-- today is Dec 22, 2011 so this should be the last month
    I should probably write some kind of recursive SQL, query staring with: "WITH TEMP...", but I don't have enough knowledge to write such a SQL. Any help is appreciated.
    Regards

  2. #2
    Join Date
    May 2008
    Posts
    17
    This might work

    create an integers table as per rudy's page

    Integer table

    Only one field and ten rows, and will always come in handy

    then you can do something like this

    Code:
    select year((current_date -i.ii months)) as "Year" 
            , month((current_date -i.ii months)) as "Month"
              from 
             (select 10*tens.i + units.i ii 
              from integers tens 
              cross join integers units) i 
              where (current_date -i.ii months) > '31/12/2007' 
              order by "Year","Month"

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the reference, tony

    nowadays, i don't bother with that cross join nonsense, i've loaded up my table with 937 integers to keep the queries simple

    why 937, you ask? because most problems require no more numbers than that

    frinstance, the "months since 2008" calculation won't require a cross join for at least 75 years

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you are using DB2 for LUW, VALUES clause can be used instead of the "integer table".
    And, repeated expressions can be calculated once by using LATERAL keyword.
    (DB2 for z/OS and DB2 for iSeries also support LATERAL(or TABLE) keyword.)

    Example 1 :
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT YEAR ( monthly ) AS year
         , MONTH( monthly ) AS month
     FROM  (VALUES 0,1  ,2  ,3  ,4  ,5  ,6  ,7  ,8  ,9  ) k(k1)
     CROSS JOIN
           (VALUES 0,10 ,20 ,30 ,40 ,50 ,60 ,70 ,80 ,90 ) k(k2)
     CROSS JOIN
           (VALUES 0,100,200,300,400,500,600,700,800.900) k(k3)
     CROSS JOIN
           LATERAL
           (VALUES DATE('2008-01-01') + (k1 + k2 + k3) MONTHs ) f(monthly)
     WHERE monthly <= CURRENT_DATE
     ORDER BY
           monthly
    ;
    ------------------------------------------------------------------------------
    
    YEAR        MONTH      
    ----------- -----------
           2008           1
           2008           2
           2008           3
    ...
    ...
    ...
           2011          10
           2011          11
           2011          12
    
      48 record(s) selected.

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    How about This:
    with temp (year,month) as ( values(2008,1),(2008,2),(2011,11),(2011,12) )
    select * from temp
    where DATE(trim(CHAR(year))||'-'||trim(CHAR(month))||'-01')>=DATE('2008-01-01')
    and DATE(trim(CHAR(year))||'-'||trim(CHAR(month))||'-01') <= DATE(current_date)

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Another possibility is a recursive common table expression:
    Code:
    WITH YY_MM_TAB (CALC_DT, YY, MM)
      AS (
    SELECT CAST('2008-01-01' AS DATE), YEAR('2008-01-01'), MONTH('2008-01-01') FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT CALC_DT + 1 MONTH, YEAR(CALC_DT + 1 MONTH), MONTH(CALC_DT + 1 MONTH)
    FROM YY_MM_TAB
    WHERE CALC_DT + 1 MONTH < CURRENT DATE
    )
    SELECT *
    FROM YY_MM_TAB
    
    CALC_DT    YY          MM         
    ---------- ----------- -----------
    01/01/2008        2008           1
    02/01/2008        2008           2
    03/01/2008        2008           3
    04/01/2008        2008           4
    05/01/2008        2008           5
    06/01/2008        2008           6
    07/01/2008        2008           7
    08/01/2008        2008           8
    09/01/2008        2008           9
    10/01/2008        2008          10
    11/01/2008        2008          11
    12/01/2008        2008          12
    01/01/2009        2009           1
    02/01/2009        2009           2
    03/01/2009        2009           3
    (removed entries to save space)
    12/01/2010        2010          12
    01/01/2011        2011           1
    02/01/2011        2011           2
    03/01/2011        2011           3
    04/01/2011        2011           4
    05/01/2011        2011           5
    06/01/2011        2011           6
    07/01/2011        2011           7
    08/01/2011        2011           8
    09/01/2011        2011           9
    10/01/2011        2011          10
    11/01/2011        2011          11
    12/01/2011        2011          12
    
      48 record(s)

Posting Permissions

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