I'm trying to make a query that I can't produce...
This is my table capturainfo
captureid int(11)
userid int(11)
date date
dateinit datetime
datefin datetime
I need to select all rows that have datefin=null
and is not the last row of the day ( ie there is one more row with date is the same day but dateinit1<dateinit2
.
I just can't do it.
Here are some attempts I've made:
The basic one:
SELECT * FROM capturainfo where date < "2015-02-27"
AND datefin is null
it gets me all datefin is null, but I can't know if there is one more row in the same day.
SELECT * FROM capturainfo a where date < "2015-02-27"
AND datefin is null
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.datefin<= b.datefin) AND a.userid = b.userid )
... Well this last one doesn't work.
here is the data sample
+-----------+--------+------------+---------------------+---------------------+
| id | userid | date | dateinit | datefin |
+-----------+--------+------------+---------------------+---------------------+
| 0001 | 2034 | 2015-02-20 | 2015-02-20 11:41:31 | 2015-02-20 20:16:07 |
| 0002 | 1789 | 2015-02-20 | 2015-02-20 12:41:31 | 2015-02-20 13:43:34 |
| 0003 | 1432 | 2015-02-20 | 2015-02-20 12:55:33 | NULL |
| 0004 | 1432 | 2015-02-20 | 2015-02-20 13:44:21 | 2015-02-20 13:45:31 |
| 0005 | 1444 | 2015-02-20 | 2015-02-20 14:41:31 | NULL |
+-----------+--------+------------+---------------------+---------------------+
In this case, query should return id 0003 because user 1432 have made a posterior register in the same day
Anybody knows how to do it?