Filling gaps between dates and stage for opportunity

43 views Asked by At

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.

0

There are 0 answers