Creating a Daily Report from non-Daily time-serie without the use of a calendar table

Question

I have a "time_serie" table with date gaps (not existing dates) that looks like:

+-----+-------+-------------+------+
| id  | TS_ID | Date_publi  | Val  |
+-----+-------+-------------+------+
|   4 |     3 | 1996-11-01  | 50.5 |
|   5 |     3 | 1996-12-02  |   53 |
|   6 |     3 | 1997-01-02  | 55.2 |
 ...       ...  ..........      ... 

I would like to create an output which replaces missing values with either zeros or #N/A or previous value so it looks like:

1996-10-30 : #N/A
1996-10-31 : #N/A
1996-11-01 : 50.5
1996-11-02 : #N/A
1996-11-03 : #N/A
....          ...

To do so, I thought about creating a "calendar" table with every single date in it and then call the right-join query:

    SELECT calendar.dates AS DATE, IFNULL(time_serie.val, "#N/A") AS val
      FROM time_serie RIGHT JOIN calendar ON (DATE(time_serie.date_publi) = calendar.dates)
      WHERE (calendar.datefield BETWEEN start_date AND end_date)

But, I would rather not have to create and manage a calendar table.

Does someone has an idea how to do such a report without using a calendar table?

Answer 1

You can use the following to get it done without using Calendar table. It isn't in MySQL but would do the trick:

DECLARE @temp TABLE (Date DATETIME, Users NVARCHAR(40), Score INT) ---- Temporary table strucrure

INSERT INTO @temp (Date, Users, Score)
VALUES ---- Default values
    ('20120101', 'User1', 17),('20120201', 'User1', 19),
    ('20120401', 'User1', 15),('20120501', 'User1', 16),
    ('20120701', 'User1', 14),('20120801', 'User1', 15),
    ('20120901', 'User1', 15),('20121001', 'User1', 13),
    ('20121201', 'User1', 11),('20130101', 'User1', 10),
    ('20130201', 'User1', 15),('20130301', 'User1', 13),
    ('20130501', 'User1', 18),('20130601', 'User1', 14),
    ('20130801', 'User1', 15),('20130901', 'User1', 14),
    ('20161001', 'User1', 10),('20120601', 'User1', 10)

;WITH cte AS ---- Created a common table expression. You can say another query executed here
(
   SELECT Users, StartDate = MIN(Date), EndDate = MAX(Date) ---- Retrieved the max and min date from the table
   FROM @temp
   GROUP BY Users

   UNION ALL ---- Used 'UNION ALL' to combine all the dates missing and existing one

   SELECT Users, DATEADD(MONTH, 1, StartDate), EndDate ---- Checks the months that are missing
   FROM cte 
   WHERE StartDate <= EndDate
)

SELECT e.StartDate, t.Users, Score = ISNULL(t.Score, 0) ---- Finally checks the user scores for the existing and non-existing months using 'CTE'
FROM cte e
LEFT JOIN @temp t ON e.StartDate = t.Date AND e.Users = t.Users
ORDER BY e.StartDate, t.Users

The above returns 0 or null if there is no entry for a specific month.

Please check this out for more: Find Missing Dates - MySQL

More specifically, this would do just fine: Find Missing Dates - MySQL 2