CODEWITHSUNDEEP
mysqldatecase
Eddi
Eddi

Reputation: 821

Nested MySQL cases including date functions

I have a MySQL table with the following structure:

    id |    date    |  type   |     description
    ---------------------------------------------------
     0 | 1979-09-20 | 'bday'  | 'Birthday Employee1 '
     1 | 1984-05-04 | 'bday'  | 'Birthday Employee2 '
     2 | 1989-01-04 | 'bday'  | 'Birthday Employee3 '
     3 | 2013-10-10 | 'visit' | 'Visit of Visitor1  '

This table should handle all upcoming and bygone events in an employee portal. There are two different types of events; birthdays and visits. These are declared as string in the column type. Birthdays should only be entered once, not every year, so there is the need of a complexe query to handle this case:

   SELECT * ,
   CASE type
   WHEN 'bday'
   THEN
     (CASE 
      WHEN ADDDATE( date, INTERVAL YEAR( NOW( ) ) - YEAR( date ) YEAR )< NOW()
       THEN ADDDATE(ADDDATE(datum,INTERVAL YEAR(NOW())- YEAR(date) YEAR),
       INTERVAL 1 YEAR)
       ELSE ADDDATE( date, INTERVAL YEAR( NOW( ) ) - YEAR( date ) YEAR )
     END)
   ELSE date
   END 
   AS dates
   FROM events
   WHERE date > NOW() AND type='visit' OR ADDDATE(date, INTERVAL 
   YEAR NOW())-YEAR( date ) YEAR) > NOW() AND type='bday'
   ORDER BY dates ASC
   LIMIT 0 ,10

This query doesn't work for me since i added the nested case clause in the brackets. I replaced it for the following line

   ADDDATE( date, INTERVAL YEAR( NOW( ) ) - YEAR( date ) YEAR )

This worked but the problem is that - as you may see in the nested CASE clause -, birthdays which already took place in the recent year won't be selected. So the purpose of the extra CASE is to determine whether the year should be like 2013 or 2014 ( if it already took place in 2013).

This is quite important for birthdays in the first two weeks of the year. The reminding notification has to be seen already in the year before, otherwise it wouldn't make that much sense.

So actually i just need to figure out why the nested CASE clause is not working. It's also possible that there are obviously easier ways to solve this, but right now I am not able to see them.

Upvotes: 0

Views: 222

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

I suppose you want to show "near" future events and birthdays. One way would be to separate the logic into 3 parts and UNION them. The 3 parts would be:

  • visits
  • birthdays of this year

  • birthdays of next year

    ( SELECT id, date, type, description, 
         date AS dates
  FROM events
  WHERE type='visit' 
    AND date > NOW() 
)
UNION ALL
( SELECT id, date, type, description,
         ADDDATE(date, INTERVAL (YEAR(NOW())-YEAR(date)) YEAR)
  FROM events
  WHERE type='bday'
    AND ADDDATE(date, INTERVAL (YEAR(NOW())-YEAR(date)) YEAR) > NOW()
)
UNION ALL
( SELECT id, date, type, description,
         ADDDATE(date, INTERVAL (1+YEAR(NOW())-YEAR(date)) YEAR)
  FROM events
  WHERE type='bday'
)
ORDER BY dates ASC
LIMIT 0, 10 ;

Your CASE seems correct, too. But if you want all birthdays to be considered, you should replace the OR ADDDATE(date, INTERVAL YEAR NOW())-YEAR( date ) YEAR) > NOW() AND type='bday' condition with the simple OR type='bday'

Upvotes: 1

jaczes
jaczes

Reputation: 1404

You have to change YEAR DIFF part:

SELECT ADDDATE(date, INTERVAL (FLOOR(DATEDIFF(CURDATE(),date)/365)) YEAR )<NOW()

example for id=0

SELECT ADDDATE('1979-09-20', 
INTERVAL (FLOOR(DATEDIFF(CURDATE(),'1979-09-20')/365)) YEAR )< NOW();

Upvotes: 0

Related Questions