Fill in Missing Dates – Using MySQL

Problem

Had a report that only displayed dates in a calendar month for which there was data. I needed a way to add Zero(s) for the missing days:

Original and final report looked like this:

Selection_140
Original Report
Using Mysql to fill in dates missing in a report
Edited Report with missing days

 

 

Solution

Create a table with dates and then left join this to the other table with missing dates. Instructions on how to create this table called calendar are from this post.

Create a table Calendar:

CREATE TABLE calendar (datefield DATE) 

Create a stored procedure that will be used to fill in the dates:

DELIMITER |
CREATE PROCEDURE fill_calendar(start_date DATE, end_date DATE)
BEGIN
 DECLARE crt_date DATE;
 SET crt_date=start_date;
 WHILE crt_date < end_date DO
 INSERT INTO calendar VALUES(crt_date);
 SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
 END WHILE;
END |
DELIMITER ; 

To fill in the dates enter start date and end date as per the below:

CALL fill_calendar('2008-01-01', '2011-12-31'); 

Note that if your date range is too long the server might timeout your query at some point. Just order by date descending to see the last date inserted and add more if necessary.

Then left join this table with your data:

SELECT
calendar.datefield as calendar,
coalesce(Diagnosis_Table.name,'No Diagnosis') as diagnosis,
coalesce(PatientVisit_Table.diagnosiscode,NULL) AS NoOfDiagnosis
FROM
calendar
LEFT JOIN PatientVisit_Table on DATE(PatientVisit_Table.datetime) = calendar.datefield
AND PatientVisit_Table.diagnosiscode NOT IN ('restricteddiagnosislist)
LEFT JOIN Diagnosis_Table ON PatientVisit_Table.diagnosiscode = Diagnosis_Table.id

WHERE
(calendar.datefield BETWEEN $P{beginDate} AND $P{endDate} )

NOTE: I’ve edited the query abit but you get the gist of what’s happening. You can join many more tables as per your needs. Also note, in my case the filled in dates from the calendar table were being removed (meaning I was back to only getting results of dates with data) when I place conditions such as [PatientVisit_Table.diagnosiscode NOT IN (‘restricteddiagnosislist)] in the WHERE clause.

SO I had to move these conditions to the join statement, I believe this is called using a composite key 

References

Using MySQL to generate daily sales reports with filled gaps

MySQL how to fill missing dates in range?

Filling in missing data with sequences of cardinal integers

using join to merge files

Printing days of the month

Group by day with filled gaps

MYSQL fill group by “gaps”

iReport Group with Date and Incrementing Date? iReport Group with Date and Incrementing Date?

MySQL Query – Include dates without records

MySQL DATE_ADD() Function

Recipe9.10.Filling in Missing Dates

ON clause condition versus WHERE clause condition WITH JOIN in mysql

MySQL join with where clause

An Introduction to MySQL Views

MySQL Tutorial 2: Views and Joins

Creating Views in MySQL

Phone Data Used to Track Disease

So seems there are some useful uses of data being harvested. In this particular case, 2 US based universities used anonymously collected Phone Data to track spread of the Rubella disease in Kenya.

Interesting the study period covered school holidays and shows that phone data can be used to predict seasonal disease patterns and also understand how travel/movement facilitates the spread of disease.