CODEWITHSUNDEEP
mysql
enderwigg
enderwigg

Reputation: 13

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.

Upvotes: 0

Views: 22

Answers (1)

Barmar
Barmar

Reputation: 782498

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 |
+------------+----------+-------+

Upvotes: 1

Related Questions