I'd like to retrieve the activity of a user from the last 7 days. The table I'm querying is a many-to-many, linking users, events and dates.
If today is the 7th of May, but the user only has activity recorded for the 4th and 2nd of May, can I build a query that will return a NULL corresponding with the dates of no activity recorded?
May 2 42 May 4 88
May 1 NULL May 2 42 May 3 NULL May 4 88 May 5 NULL May 6 NULL May 7 NULL
How can this be done in MySQL?Answer1:
SELECT DISTINCT users, events, dates
WHERE dates between date_format(date1, '%Y-%m-%d')
and date_format(date2, '%Y-%m-%d') or NOW() or CURDATE()....
and user = 'username'
This is pretty general, but if you replace the names and the dates (probably don't need to format for your purposes), this will return all results regardless of null place holders
You could populate a date_reference_table...here is the stored proc for this:
I use a date format to only recognize the year and month, but you can alter this...
You would also need an inner join then... so you would do an inner join on the date_reference table and it will spit out the results as mentioned in the comment above.
CREATE PROCEDURE `generate_date_reference_table`(d1 date, d2 date) BEGIN declare d datetime; create table BLMBP.date_reference (d char(7) not null); set d = d1; while d <= d2 do insert into BLMBP.date_reference (d) values (date_format(d, '%Y-%m')); set d = date_add(d, interval 1 month); end while; END$$