I am pretty new to MySQL and wanted to ask for help which would be much appreciated!
Let's have opp_history table like (plus its pk):
opp_id date_created stage
00658000009Pa0gAAC 2016-11-05 Qualification
00658000009Pa0gAAC 2016-11-06 Validating benefits
00658000009Pa0gAAC 2016-11-09 Offer
00658000009Pa0gAAC 2016-11-09 Validating benefits
00658000009Pa0gAAC 2016-11-10 Offer
00658000009Pa0gAAC 2016-11-15 Closed Lost
00658000009PIZHAA4 2016-11-04 Validating benefits
00658000009PIZHAA4 2016-11-04 Offer
00658000009PIZHAA4 2016-11-04 Qualification
00658000009PIZHAA4 2016-11-04 Offer
00658000009PIZHAA4 2016-11-10 Closed Lost
And what I would like to do is to fill in the gaps with dates between each stage with the previous stage value, so:
opp_id date_created stage
00658000009Pa0gAAC 2016-11-05 Qualification
00658000009Pa0gAAC 2016-11-06 Validating benefits
00658000009Pa0gAAC 2016-11-07 Validating benefits
00658000009Pa0gAAC 2016-11-08 Validating benefits
00658000009Pa0gAAC 2016-11-09 Offer
00658000009Pa0gAAC 2016-11-09 Validating benefits
00658000009Pa0gAAC 2016-11-10 Offer
00658000009Pa0gAAC 2016-11-11 Offer
00658000009Pa0gAAC 2016-11-12 Offer
00658000009Pa0gAAC 2016-11-13 Offer
00658000009Pa0gAAC 2016-11-14 Offer
00658000009Pa0gAAC 2016-11-15 Closed Lost
00658000009PIZHAA4 2016-11-04 Validating benefits
00658000009PIZHAA4 2016-11-04 Offer
00658000009PIZHAA4 2016-11-04 Qualification
00658000009PIZHAA4 2016-11-05 Offer
00658000009PIZHAA4 2016-11-06 Offer
00658000009PIZHAA4 2016-11-07 Offer
00658000009PIZHAA4 2016-11-08 Offer
00658000009PIZHAA4 2016-11-09 Offer
00658000009PIZHAA4 2016-11-10 Closed Lost
However even though I read this post: MySQL Fill in missing dates between two dates for a given status I am still struggling to apply the same logic cause the opp_id where the max date must be the max created date for each opp_id.
I already created the calendar dummy table and view as suggested there.
Thanks so much for your advises! Feel free to ask anything, Z.