85325

Return records for last N days, when not every day in N has a record?

Question:

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?

<strong>Source</strong>

May 2 42 May 4 88

<strong>Desired Output</strong>

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 FROM table 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.

delimiter $$

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

Recommend

  • pandas left join where right is null on multiple columns
  • dbus - register object to remote on interface NULL
  • Mysql get average and sum of columns and group by year & month
  • select if there are more than n results but with conditions
  • Datepart for time between (instead of Convert date)
  • Hyperledger Fabric 1.0 how to get channel by name
  • Aggregating date data by calendar month
  • Break into multiple rows based on date range of a single row
  • select n random rows per specified group
  • spring autowire is not working returning null
  • SOLR - sunspot_rails/sunspot_solr 2.1 - NO JSP support
  • mysql convert date to same date of current year
  • How to display all months in pivot query's results even if no data for given month [duplicate]
  • Pass and receive data in Vaadin
  • FirePHP doesn't always write log messages
  • How Intersect and Except result in SSIS
  • PHP: Filter array by date range
  • Converting mysql date from one format to another
  • Add delivery info to query in SAP Crystal Reports
  • Taking mean across rows grouped by a variable in numpy
  • Grouping by blank nodes
  • Trouble connecting to Google Cloud SQL server from deployed app
  • JPA/Hibernate - Entity name seems to be important. If I rename to “Bob” works fine
  • Query to get the Top 2 from each group
  • How do you SELECT several columns with one distinct column
  • How to implement limit with Nhibernate and Sybase
  • How to check if a string contains any string of a column in MySQL and vice versa?
  • Getting syntax error in mysql-php. You have an error in your SQL syntax;
  • PHP multiple file uploads
  • Recording values of radio buttons in ember
  • PDO error when wrong host name
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • Moving mysql files across servers
  • How to check if every primary key value is being referenced as foreign key in another table
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Comma separated Values
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?