Mysql Query issue using between

Question

I am trying to track inventory based only on install and removal dates and report what is installed at any given day. Records that are still installed, do not have a removal date. So I am trying to set the removal date to Date(now()). But when I add between install_date and removal_date it only finds records with both dates.

I have an inventory table which contains name, install_date and removal_date and i have a master_date table which contains all dates from 2012-01-01 to 2014-12-31

Select inventory.name, 
       inventory.install_date, 
       IFNULL(inventory.removal_date,DATE(NOW())), 
       master_date.date 
FROM inventory, master_date 
WHERE master_date.date BETWEEN inventory.install_date 
                       AND inventory.removal_date

If I remove the between clause, it sets removal_date to NOW, but continues to include records where the removal_date is prior to NOW. With the where clause, it only returns records where removal_date is not null but the date field is correct.

How can I use NOW in place of a NULL removal_date and return the correct records?

score 1 · Accepted Answer · answered Aug 21 '13 at 22:39

1

You can use COALESCE() which returns the first non-null value in the supplied parameters.

You can do:

BETWEEN inventory.install_date AND COALESCE(inventory.removal_date, DATE(NOW())

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

answered Aug 21 '13 at 22:39

ApplePie

8,814
5
39
60

According to this SO answer, it seems that COALESCE() is standard while IFNULL() is not. http://stackoverflow.com/questions/4747877/mysql-ifnull-vs-coalesce-which-is-faster – ApplePie Aug 21 '13 at 22:43

score 0 · Answer 2 · answered Aug 21 '13 at 22:41

If I understand your question correctly, by saying:

Select IFNULL(inventory.removal_date,DATE(NOW()))

You're returning a new field.

Try:

Select inventory.name, 
       inventory.install_date, 
       IFNULL(inventory.removal_date,DATE(NOW())), 
       master_date.date 
FROM inventory, master_date 
WHERE master_date.date BETWEEN inventory.install_date 
                       AND IFNULL(inventory.removal_date,DATE(NOW()))

Mysql Query issue using between

2 Answers2