SQL Programming Birthday Issue

When learning MySQL, a more classic SQL Programming topic is the birthday problem, known a user's birth date and current date, calculate his recent birthday.

There are two common issues to consider

• Leap year February is 29 days
• Have you finished your birthday this year?

For example: A person's birthday is February 29, 1992, currently if the current date is January 2, 2004, then his latest birthday is February 29, 2004, if the current date is March 3, 2004, then his recent birthday is March 1, 2005.

Now, use SQL programming to solve this problem of birthday.

Create a Employees table

Create table:create Table ' employees ' (  ' emp_no ' int (one) not null,  ' birth_date ' date not null,  ' first_name ' V Archar () Not NULL,  ' last_name ' varchar (+) NOT NULL,  ' Gender ' enum (' M ', ' F ') is not null,  ' hire_date ' date not NULL,  PRIMARY KEY (' emp_no ')) Engine=innodb DEFAULT charset=latin1

and insert some data.

INSERT INTO ' employees ' VALUES (' 10001 ', ' 1953-12-12 ', ' Georgi ', ' Facello ', ' M ', ' 1986-06-26 ') and insert INTO ' employees ' VAL UES (' 10002 ', ' 1953-11-11 ', ' Bezalel ', ' Simmel ', ' F ', ' 1985-11-21 '); INSERT into ' Employees ' VALUES (' 10003 ', ' 1959-12-03 ') , ' Parto ', ' Bamford ', ' M ', ' 1986-08-28 '); INSERT into ' Employees ' VALUES (' 10004 ', ' 1954-05-01 ', ' Chirstian ', ' Koblick ', ' m ', ' 1986-12-01 '); insert INTO ' Employees ' VALUES (' 10005 ', ' 1955-01-21 ', ' Kyoichi ', ' Maliniak ', ' M ', ' 1989-09-12 '); insert Into ' employees ' values (' 10006 ', ' 1953-04-20 ', ' Anneke ', ' preusig ', ' F ', ' 1989-06-02 '); INSERT into ' employees ' values (' 1 0007 ', ' 1957-05-23 ', ' Tzvetan ', ' Zielinski ', ' F ', ' 1989-02-10 '); INSERT into ' Employees ' VALUES (' 10008 ', ' 1958-02-19 ', ' S Aniya ', ' Kalloufi ', ' M ', ' 1994-09-15 '); INSERT into ' Employees ' VALUES (' 10009 ', ' 1952-04-19 ', ' sumant ', ' peac ', ' F ', ' 1985 -02-18 '); insert INTO ' Employees ' VALUES (' 10010 ', ' 1963-06-01 ', ' Duangkaew ', ' Piveteau ', ' F ', ' 1989-08-24 '); Employees ' VALUES (' 10011 ',' 1972-02-29 ', ' Jiang ', ' David ', ' M ', ' 1990-02-20 '); 

View the existing data in the table, and then calculate the most recent birthday date, based on the date of birth and the current date (2014-10-31).

Write SQL to calculate the last birthday date

select name, Birthday, if (cur>today,cur,next) as Birth_dayfrom (select Name,birthday,today, Date_add (c ur, Interval if (day (Birthday) = $ && Day (cur) = 1, 0) as cur, date_add (next, Inter Val if (birthday) = && Day (cur) = 1, 0) as Next from (select Name,birth Day,today, Date_add (Birthday,interval diff year) as cur, date_add (birthday,i                                 Nterval diff+1 year) as Next from (select Concat (last_name, ", first_name) as name, Birth_date as Birthday, (year (today ())-year (birth_date))             As diff, now () as today from employees) as a As b) as C; 
 

The output of the above-mentioned SQL program

Analysis:

3 Sub-queries A, B, C are used

A subquery adds an age field diff, and the query results for subquery b,a are as follows

Sub-query B calculated that this year's birthday date cur and next year's birthday date Next,b query results are as follows:

Note: Here for the leap month processing is February 28 (according to Law, leap year 29th birthday in non-leap year should be March 1), so subquery C is used to deal with the problem of Leap month, C's query results are as follows:

Finally, you need to determine whether the current date is greater than this year's birthday (whether the birthday is over this year), then choose the latest birthday, the final answer is:

SQL Programming Birthday Issue