Select all years between two years

gompuok · #1 (**permalink**) 01-03-10, 21:46

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.

r937 · #2 (**permalink**) 01-03-10, 22:36

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

gompuok · #3 (**permalink**) 01-04-10, 03:26

Thank you very much my friend. You solution works very well. God bless you.

gompuok · #4 (**permalink**) 01-06-10, 21:02

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.

r937 · #5 (**permalink**) 01-07-10, 00:03

what application language are you using for this? php?

gompuok · #6 (**permalink**) 01-07-10, 01:22

Actually I'm doing reporting using Pentaho. So, I'm directly using mysql syntax to pull out data from database.

r937 · #7 (**permalink**) 01-07-10, 06:00

Quote:

Originally Posted by gompuok

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

gompuok · #8 (**permalink**) 01-11-10, 22:23

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.

r937 · #9 (**permalink**) 01-11-10, 22:35

Quote:

Originally Posted by gompuok

I really appreciate if you can help me doing this in mysql..

i'll give it a shot

show me your exact query please

gompuok · #10 (**permalink**) 01-11-10, 23:17

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

r937 · #11 (**permalink**) 01-12-10, 08:27

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

gompuok · #12 (**permalink**) 01-12-10, 20:43

thanks a lot my friend..you are the man..it works very well!! thanks again.

Internet Services

Web Development

Digital Marketing

Consumer Tech