Apply right join on a derived table and a calendar table to get all dates between two days

Question

I'm not a db expert. I'm just working on a project where we need to show page views on daily basis between two dates selected by a user from a calendar. I have the following

SQL query that brings brings total number of page views like

SELECT DATE_FORMAT(created_at,'%d %b %Y') as this_date, count(*) as Views 
from promotion_insights WHERE f_id = '2' AND p_id = '12' 
AND created_at BETWEEN '2012-08-15' AND '2012-08-19' 
GROUP BY this_date

Result comes like

----------------------
this_date   View
---------------------
15 Aug 2012     3
16 Aug 2012     2
----------------------

I have also a calendar table and a store procedure.calendar table has one column of dates named (datefield). I have already generated dates by calling a stored procedure so don't worry about that. Now what I want, is to make a right join on date basis to above table having (this_date and view columns) with calendar table to show all_dates between selected by the user and put 0 for the dates having 0 views. count(*) is also making trouble by returing 1 instead 0

Expected output by the join I want is like this:

----------------------
this_date   View
---------------------
15 Aug 2012     3
16 Aug 2012     2
17 Aug 2012     0
18 Aug 2012     0
19 Aug 2012     0

----------------------

Any help would be highly highly appreciated.

Why are you doing this in SQL instead of in your application, where looping from one date to another is trivial? — Dan Grossman, Sep 02 '12 at 07:36

Gordon Linoff · Accepted Answer · 2012-09-02T13:19:19.740

You can readily do this with a calendar table -- a good idea in most databases.

SELECT DATE_FORMAT(c.date,'%d %b %Y') as this_date, count(*) as Views 
from calendar c left outer join
     promotion_insights pi
     on c.date between '2012-08-15' AND '2012-08-19' and
        c.date = pi.created_at
WHERE f_id = '2' AND p_id = '12'
GROUP BY c.date

Note: this assumes that created_at is stored as a date, not a date time. Having a time component could throw off the comparisons.

score 0 · Answer 2 · edited May 23 '17 at 10:34

If you want to do this in SQL you need a row generator to generated a row for each day in your date range, and then join your query to the generated rows to get results for each day in you date range (even if there is no data present for some days in the range).

If you are using Oracle you can use ALL_OBJECTS or DUAL to generate rows. As you are using DATE_FORMAT in your query you seem to use MySQL. A question on how to make a row generator in MySQL was posted before. It states:

Hate to say this, but MySQL is the only RDBMS of the big four that doesn't have this feature.

Apply right join on a derived table and a calendar table to get all dates between two days

2 Answers2