Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Posts
    13

    Red face Unanswered: Select all years between two years

    Hi all,

    This might be a piece of cake question to those who expert in mysql but not for me. I've been trying to figure out whole day how to solve this problem but no luck.

    How can I select all of the years between two years?

    For example, given the start year 2007 and the end year 2010, I want to return:

    2007
    2008
    2009
    2010

    your help are much appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    step 1, create a numbers table --
    Code:
    CREATE TABLE numbers (n INTEGER NOT NULL PRIMARY KEY);
    INSERT INTO numbers (n) VALUES 
     (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),...;
    make sure there are as many numbers as you have for the largest gap of years

    step 2, your query --
    Code:
    SELECT 2007 + n AS year
        FROM numbers
     WHERE 2007 + n <= 2010
    you would likely parameterize the start and end years
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2008
    Posts
    13
    Thank you very much my friend. You solution works very well. God bless you.

  4. #4
    Join Date
    Oct 2008
    Posts
    13
    Hi R937,

    Hope you can help me again. Here is my select statement (after modified yours);

    SELECT 2007 + n as 'year', sales.total
    FROM numbers
    LEFT JOIN sales on sales.year = 2007 + n
    WHERE 2007 + n <= 2010

    so the result looks something like below;
    year total
    2007 1000
    2008 1500
    2009 800
    2010 2000

    My question is, i would like to calculate increase/decrease. What I want is the output should be exactly like below; (current year total - last year total)

    year total_increase_decrease
    2007 200
    2008 500
    2009 -700
    2010 1200

    your help are much appreciated.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what application language are you using for this? php?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2008
    Posts
    13
    Actually I'm doing reporting using Pentaho. So, I'm directly using mysql syntax to pull out data from database.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gompuok View Post
    Actually I'm doing reporting using Pentaho. So, I'm directly using mysql syntax to pull out data from database.
    the reason i asked is because current/previous row calculations are extremely convoluted if you try to do them with SQL

    i've never used pentaho myself but from looking at their web site, i betcha it can do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2008
    Posts
    13
    I have been spending 2 days looking for a solution for this problem in pentaho but i dont think it's doable. I really appreciate if you can help me doing this in mysql..

    Thanks so much again.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gompuok View Post
    I really appreciate if you can help me doing this in mysql..
    i'll give it a shot

    show me your exact query please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2008
    Posts
    13
    Basicaly below is my exact query;
    Code:
    SELECT 2007 + n as 'year', sales.total
    FROM numbers
    LEFT JOIN sales on sales.year = 2007 + n
    WHERE 2007 + n <= 2010
    the output looks like below;

    year total
    2007 1000
    2008 1500
    2009 800
    2010 2000

    so basically i want to add one more column to my query called increase_decrease. So the final output later will become like below;

    year total increase_decrease
    2007 1000 1000
    2008 1500 500
    2009 800 -700
    2010 2000 1200

    Hope you can help me

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SET @start_year = 2004;
    SET @end_year = 2010;
    
    SELECT @start_year + n as 'year'
         , COALESCE(curr_year.total,0) AS curr_total
         , COALESCE(prev_year.total,0) AS prev_total
         , COALESCE(curr_year.total,0) -
           COALESCE(prev_year.total,0) AS increase_decrease
      FROM numbers
    LEFT OUTER
      JOIN sales AS curr_year
        ON curr_year.year = @start_year + n
    LEFT OUTER
      JOIN sales AS prev_year
        ON prev_year.year = @start_year + n - 1
     WHERE @start_year + n <= @end_year
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2008
    Posts
    13
    thanks a lot my friend..you are the man..it works very well!! thanks again.

Posting Permissions

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