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

Disable TeamViewer Startup Ubuntu 15.04

Problem

I installed Team viewer but I want it to start on demand and not on startup.

Solution

Ubuntu switched from init based daemon to Upstart which is event-based. Some of the changes are listed in the UbuntuBootup documentation.

Starting a Service

# Traditional:
/etc/init.d/myservice start
# Upstart
service myservice start

Stopping a Service

# Traditional: 
/etc/init.d/myservice stop
# Upstart
service myservice stop

Getting a list of Services

# Traditional:
ls /etc/init.d
# Upstart: 
service --status-all
  • Note: Upstart method will show both traditional and upstart services.

Adding a Service to Default runlevels

# Traditional
update-rc.d apache2 defaults
  • Upstart: there is no concept of runlevels, everything is event driven with dependencies. You would add an upstart config to /etc/init and potentially source a config file in /etc/default to allow users to override default behaviour.

Removing a Service from Default runlevels

# Traditional - Something along the lines of
rm /etc/rc*/*myscript
  • Upstart: If no config is available in /etc/default, edit config in /etc/init

Another change is also Upstart doesn’t use run levels, so to disable a daemon like TeamViewer from startup then:

 # ps aux | grep teamviewerd
root 11404 0.3 0.1 245580 18112 ? Ssl 09:54 0:19 /opt/teamviewer/tv_bin/teamviewerd -f
root 13818 0.0 0.0 12968 2276 pts/4 S+ 11:23 0:00 grep --colour=auto teamviewerd

# teamviewer --daemon disable
Wed Feb 3 11:23:23 EAT 2016
Action: Removing ...
initctl stop teamviewerd
teamviewerd stop/waiting

kill '/opt/teamviewer/tv_bin/script/teamviewer' - root (/root/.local/share/teamviewer11 / /root/.local/share/teamviewer11)

# ps aux | grep teamviewerd
root 13937 0.0 0.0 12968 2372 pts/4 S+ 11:24 0:00 grep --colour=auto teamviewerd

References

upstart

UbuntuBootupHowto

upstart cookbook

How to configure teamviewer so it does not load unless needed