Mysql – Get data from previous years dynamically

dateMySQL

I have a table with columns id, fname, date_field. date_field's records are:

I want the data for the past 2 years for each record from that date which the table has.

For example:

2012 year having only one data before that is 2011
2013 year data having two data before 2 year data which are 2012 and 2011
2014 year data having two data before 2 year data which are 2013 and 2012
2015 year data having two data before 2 year data which are 2014 and 2013
2016 year data having two data before 2 year data which are 2015 and 2014

Note: Need to work dynamically for whatever data is in table, not by entering the date manually.

I want the query for MySQL.

Best Answer

You can do that with a simple join and intervals. Assuming the table with your data is named mydata and the table you mention in your question with a list of dates named mydates, you could issue the following query:

SELECT d.id, d.fname, d.date_field, dat.*
FROM mydates d
JOIN mydata dat
    ON (dat.date_field >= (d.date_field - interval 2 years) 
    AND dat.date_field < d.date_field)
ORDER BY 3, 1;

Related Solutions

Mysql – How to organize the tables of a MySQL database with several statistics data of several years

Well , i would add a new column on the Statistics_Entry as your year column !
Next depending on the volume of your data i would partition that table using this column(this will speedup your query when using the year column as your predicate).
This lookup table can be a view !
I would not create a new table just for the year column, this would just make thing complicated ! in my opinion !

Mysql – How to get dates that wrap around new year from non-datetime columns

You can use MAKEDATE(), it makes the day_of_year to a date.

Test:

mysql> SELECT * FROM cli_table;
+-------+--------------+-------------+-------+
| month | day_of_month | day_of_year | value |
+-------+--------------+-------------+-------+
|     1 |            1 |           1 | 23.00 |
|     1 |            2 |           2 | 22.95 |
|    12 |           31 |         365 | 23.12 |
+-------+--------------+-------------+-------+
3 rows in set (0.00 sec)

mysql> SELECT 
    -> MAKEDATE(YEAR(CURDATE()),day_of_year) AS Date,
    ->     value AS DayValue
    -> FROM test.cli_table;
+------------+----------+
| Date       | DayValue |
+------------+----------+
| 2015-01-01 |    23.00 |
| 2015-01-02 |    22.95 |
| 2015-12-31 |    23.12 |
+------------+----------+
3 rows in set (0.00 sec)

mysql>

You can try it in this Fiddle.

EDIT:

According of what you want, you can make it with pure SQL:

mysql> SELECT * FROM test.cli_table;
+-------+--------------+-------------+-------+
| month | day_of_month | day_of_year | value |
+-------+--------------+-------------+-------+
|     1 |            1 |           1 |  5.00 |
|     1 |            2 |           2 |  6.00 |
|     1 |            3 |           3 |  7.00 |
|    12 |           25 |         359 | 23.00 |
|    12 |           26 |         360 | 22.95 |
|    12 |           27 |         361 |  1.00 |
|    12 |           28 |         362 |  2.00 |
|    12 |           29 |         363 |  3.00 |
|    12 |           30 |         364 |  4.00 |
|    12 |           31 |         365 | 23.12 |
+-------+--------------+-------------+-------+
10 rows in set (0.00 sec)

mysql> SELECT 
    -> v.selected_date,
    -> v.dayyear,
    -> c.value AS DayValue,
    -> c.day_of_year 
    -> FROM test.cli_table AS c
    -> JOIN (SELECT 
    -> adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date,
    -> DAYOFYEAR(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i)) AS dayyear
    -> FROM 
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) AS v
    -> ON (v.selected_date between '2015-12-25' AND '2016-01-03'
    -> AND v.dayyear=c.day_of_year);
+---------------+---------+----------+-------------+
| selected_date | dayyear | DayValue | day_of_year |
+---------------+---------+----------+-------------+
| 2015-12-25    |     359 |    23.00 |         359 |
| 2015-12-26    |     360 |    22.95 |         360 |
| 2015-12-27    |     361 |     1.00 |         361 |
| 2015-12-28    |     362 |     2.00 |         362 |
| 2015-12-29    |     363 |     3.00 |         363 |
| 2015-12-30    |     364 |     4.00 |         364 |
| 2015-12-31    |     365 |    23.12 |         365 |
| 2016-01-01    |       1 |     5.00 |           1 |
| 2016-01-02    |       2 |     6.00 |           2 |
| 2016-01-03    |       3 |     7.00 |           3 |
+---------------+---------+----------+-------------+
10 rows in set (0.00 sec)

mysql>

Query Meaning:

SELECT 
    v.selected_date,
    v.dayyear,
    c.value AS DayValue,
    c.day_of_year 
FROM test.cli_table AS c
--- > This JOIN is generating all the dates between the selected_dates ('2015-12-25' AND '2016-01-03')
JOIN (SELECT 
        adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date,
        DAYOFYEAR(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i)) AS dayyear
      FROM 
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) AS v
--- > Here I'm matching every dayyear of the generated dates with the cli_table.day_of_year.
ON (v.selected_date between '2015-12-25' AND '2016-01-03'
    AND v.dayyear=c.day_of_year);