0

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

2011-03-01
2012-03-01
2013-03-01
2014-03-01
2015-03-01
2016-03-01

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.

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.