CODEWITHSUNDEEP
mysql
DhruvPathak
DhruvPathak

Reputation: 43245

MySql query to check occurence which happened on each day between two dates?

This is the MYSQL table schema :

+---------------+---------------------+
| username      | last_activity_time  |
+---------------+---------------------+
| raphael       | 2011-11-28 23:16:34 |
| donatello     | 2011-11-28 23:17:36 |
| michaelengelo | 2011-11-29 10:08:28 |
| raphael       | 2011-11-29 11:11:33 |
| leonardo      | 2011-11-29 11:12:30 |
+---------------+---------------------+

A NEW record with username and last_activity_time is inserted for each activity.

Query requirement :

Select all users who did some activity DAILY between two dates say 2011-11-20 and 2011-11-30 ( both included )

"IN" query does not seem to be an option as it works for any value in the IN array, not ALL.

Upvotes: 2

Views: 307

Answers (4)

Naveen Kumar
Naveen Kumar

Reputation: 4601

Try this query 

SELECT count(distinct date(last_activity_date)),username 
FROM users 
WHERE last_activity_date between start_Date and end_Date 
group by user_id;

If the count is equal to date difference, they have logged in at least once per day in between the two days

Upvotes: 0

Skrol29
Skrol29

Reputation: 5552

SELECT username, COUNT(*) AS nbr_day
FROM (
    SELECT username, DATE(last_activity_time) AS last_activity_date
    FROM my_table
    WHERE ( last_activity_time >= @date1 )
    AND ( last_activity_time < DATE_ADD( @date2, INTERVAL 1 DAY) )
    GROUP BY username, last_activity_date
) AS sub
GROUP BY username
HAVING ( COUNT(*) = 1 + DATEDIFF(@date2, @date1) )

This query counts the number of different days for each username. And then retain only those for which this number is equal to the number of days between the two dates.

The expression "DATE_ADD( @date2, INTERVAL 1 DAY)" represents the date just after @date2. It it used rather than @date2 because "last_activity_time" contains date and also time.

Upvotes: 1

BMN
BMN

Reputation: 8508

Try with BETWEEN to get data between two dates.

SELECT username, mast_activity_time FROM table 
WHERE last_activity_time BETWEEN "2011-11-20 00:00:00" AND "2011-11-30 23:59:59"
GROUP BY username, last_activity_time HAVING COUNT(last_activity_time) = 1

Note that this query will work only if you insert a new entry for each new activity. If you just update the entry in your table, you won't be able to retrieve what you want as your previous data will be erased from the table.

To understand better, check HAVING

Upvotes: 0

trembon
trembon

Reputation: 768

if you dont any advanced query, you can use BETWEEN. (Link: here)

and with that you can do something like this:

SELECT username,last_activity_time FROM users WHERE last_activity_time BETWEEN 'date1' AND 'date2';

Upvotes: 0

Related Questions