enderwigg enderwigg - 1 month ago 7
MySQL Question

MySql query to compare data between two dates. Issue with 9/30 to 10/01?

Here's the query:

Select a.Date as Date, count(0) as Count(*) from mytable
where (not(exists(select 1 from mytable b where ((a.ID = b.ID) and (b.Date = a.Date+1))))) Group by a.Date


So everything works fine except for the transistion from
9/30/16
to
10/01/16

In that case, for some strange reason it returns ALL of the IDs for
9/30/16
instead of just the ones that have disappeared from the table on
10/01/16
.
I have confirmed there is data in
10/01/16
.

Answer

Don't use a.Date+1 to get the next date, use DATE_ADD(a.Date, INTERVAL 1 DAY). When you use arithmetic, it first converts the date to a number, then performs arithmetic on the number. So 9/30/16 becomes 20160930, and +1 results in 20160931. Since this isn't equal to 10/01/16, none of the rows are considered to match. This will happen at the end of every month.

Select a.Date as Date, count(*) as Count 
from mytable a where (not(exists(
    select 1 from mytable b 
    where a.ID = b.ID and b.Date = date_add(a.Date, interval 1 day))))
Group by a.Date;

I discovered this by adding a.Date+1 to the select clause to see what it was comparing with, and it printed:

+------------+----------+-------+
| Date       | a.Date+1 | count |
+------------+----------+-------+
| 2016-09-30 | 20160931 |     1 |
| 2016-10-01 | 20161002 |     2 |
+------------+----------+-------+