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

grofaty · #1 (**permalink**) 12-22-11, 09:56

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

TonyF123 · #2 (**permalink**) 12-22-11, 19:18

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"

r937 · #3 (**permalink**) 12-22-11, 19:44

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

tonkuma · #4 (**permalink**) 12-22-11, 20:15

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.

fengsun2 · #5 (**permalink**) 12-22-11, 20:59

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)

Stealth_DBA · #6 (**permalink**) 12-22-11, 22:59

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech