MySQL 8 View: Fill in the missing dates
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

I need help creating a MySQL View

I have a table of prices for products on select days.

If there is no price between two given times, the price is to be assumed the same as the previously recorded price for the partner|partnerproduct combination.
If the price <=0, the partnerproduct is out of stock at the partner.

The table looks like this:
Sample

Ultimately, what I am looking for is a view which returns the average price for a given day for each partner and partnerproduct, including the days which are omitted. Each row should also have an "instock indicator" column which should (1 when instock [price is >0] or 0 when out of stock [price is <=0].

If the partner|partnerproduct is out of stock for any given day, the 'price' field on said day should report the value from the previous day.

If the partner|partnerproduct is out of stock for part of a given day, the average price for the instock indicator should be 0 for that entire day.

A partner|partnerproduct should not appear in the results for a given date until the first date it has been recorded in the table.

I am OK in hard keying the lower and upper dates for the results into the view.

Tags
MySQL

Crowdsource coding tasks.

0 Solutions