MySQL - Adding missing date records to a table

Question

I have this dataset which contains data only for the weekdays. Here is how it looks like:

ID  Name        Some Val    Other Val   Date

10  Somebody    33001.93    33001.93    2018-10-01
10  Somebody    33481.93    33481.93    2018-10-02
10  Somebody    33001.93    33001.93    2018-10-03
10  Somebody    33582.76    33582.76    2018-10-04
10  Somebody    33582.73    33582.79    2018-10-05
10  Somebody    33582.76    33582.76    2018-10-08
10  Somebody    33342.76    33342.76    2018-10-09
10  Somebody    33462.76    33462.76    2018-10-10

My computation requires the data to be populated for all the days in a month. To make the data usable, I need to populate the data for the weekends as well.

In the above data sample, the missing records for the dates 2018-10-06 and 2018-10-7 needs to be replicated by last available value (2018-10-05) in this case. So that the resultant dataset, after the transformation looks like:

ID  Name        Some Val    Other Val   Date

10  Somebody    33001.93    33001.93    2018-10-01
10  Somebody    33481.93    33481.93    2018-10-02
10  Somebody    33001.93    33001.93    2018-10-03
10  Somebody    33582.76    33582.76    2018-10-04
10  Somebody    33582.73    33582.79    2018-10-05

10  Somebody    33582.73    33582.79    2018-10-06
10  Somebody    33582.73    33582.79    2018-10-07

10  Somebody    33582.76    33582.76    2018-10-08
10  Somebody    33342.76    33342.76    2018-10-09
10  Somebody    33462.76    33462.76    2018-10-10

I am looking for guidance on how to retain the missing record, loop through all the available rows and insert row wherever it's missing. I am thinking for creating a lookup table which contains all the date values, merge them and wherever there is no match, I need to insert the last available value.

My experience in SQL is limited and coming from SAS background, I can possibly accomplish this using macros. I am totally stuck on how to attack this problem in SQL.

Need guidance me on how to proceed.

"I am thinking for creating a lookup table which contains all the date values, merge them and wherever there is no match" - That is one way. Try something based on that, and then you can post your code here, which SO community can look at further. — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 26, 2018 at 14:47
Can you help me with some more info on "user-defined" session variables? — Sushant Vasishta
– Sushant Vasishta, Commented Oct 26, 2018 at 14:53
Here is an answer which utilizes that approach. stackoverflow.com/a/52889669/2469308 I am not suggesting that this is a one-to-one match; but it will give you an idea of how this technique works — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 26, 2018 at 14:56
Do you actually need to store this data? Couldn't you just make it up 'on-the-fly'? — Strawberry
– Strawberry, Commented Oct 26, 2018 at 15:06

Gordon Linoff · Accepted Answer · 2018-10-26 15:15:42Z

1

Hmmm . . . is this sufficient?

select ID, Name, val1, val2, date
from t
union all
select id, name, val1, val2, date + interval 1 day
from t
where dayofweek(date) = 6
union all
select id, name, val1, val2, date + interval 2 day
from t
where dayofweek(date) = 6;

If the month ends on a Friday or Saturday, this adds an extra day or two. You can get around this by using where clauses in the subquery.

answered Oct 26, 2018 at 15:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sushant Vasishta Over a year ago

Loved what you did there. I find that very clever. Only issue in this case is, if since we are only creating weekend records, in case a date is missing in the sequence (e.g. a public holiday) say a Wednesday, this could not address that. This solution works for 90% missing rows but I'd still be looking for something that can check for missing records and then utilize the last available record to fill up.

Gordon Linoff Over a year ago

@SushantVasishta . . . This question is quite explicitly about weekends ("To make the data usable, I need to populate the data for the weekends as well."). If you have another question, please ask it as a new question.

Sushant Vasishta Over a year ago

Fair enough. I could have elaborated a little more. I'll mark this query as closed then. Appreciate your help.

Collectives™ on Stack Overflow

MySQL - Adding missing date records to a table

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related