carrying values over to nonexistint date fields

[daddypost]

hopefully the title was confusing enough to someone that they may look in to see what in the world I'm talking about.
I have a table that contains product_ids, review_dates, and aggregated ratings. Here's what I need: I need to create a file that has every day(starting with the first date a product was reviewed) and the aggregated rating for that day. The issue is coming when product x is reviewed on 9/1,9/2,9/8,10/4. I have physical rows for each of those 4 in my current table. Now i have to fill 9/3-9/7 with the values for 9/2(since the rating hasn't changed) and like wise 9/9-10/3 with the rating value for 9/8. ie: This is what I have
table
x 9/1 5.0
x 9/2 4.75
x 9/5 4.25
x 9/7 4.8

What I need is
table
x 9/1 5.0
x 9/2 4.75
x 9/3 4.75
x 9/4 4.75
x 9/5 4.25
x 9/5 4.25
x 9/7 4.8

Each item in the table will have different start dates. I just need to fill in any missing dates to current from the starting date forward.

 

[SantaMufasa]

Daddy,

First of all, does the REVIEW_DATE column have as its data type VARCHAR2 or is it DATE? (That makes a difference in the solution that we might post.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[daddypost]

it is a date in the form 7/21/2007

 

[SantaMufasa]

Daddy,

For my peace of mind, could you do a SQL*Plus DESCRIBE on the table and post the results for at least your three focus columns, above?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[daddypost]

Name Null? Type
----------------------------------------- -------- ----------------------------
SUBMISSION_DT NOT NULL DATE
MASTER_ID NOT NULL NUMBER(10)
DAILY_REVIEWS NUMBER
DAILY_RATING_TOTAL NUMBER
TOTAL_REVIEWS NUMBER
TOTAL_RATING NUMBER


The aggregate rating is a calc of total_rating/total_reviews

 

[SantaMufasa]

Daddy,

I plugged your original data plus some additional hypothetical rows (from a different MASTER_ID) into columns with names from your DESCRIBE to produce this sample table:

select * from ratings;

                   Daily
Master Submission Rating
ID     Dt          Total
------ ---------- ------
x      01-SEP-07    5.00
x      02-SEP-07    4.75
x      05-SEP-07    4.25
x      07-SEP-07    4.80
y      06-SEP-07    4.75
y      07-SEP-07    4.50
y      10-SEP-07    4.00
y      12-SEP-07    4.55

Here is code and the code's results:

col master_id heading "Master|ID" format a6
col submission_dt heading "Submission|Dt" format a10
col daily_rating_total heading "Daily|Rating|Total" format 99.99
select y.master_id
      ,y.all_dates submission_dt
      ,nvl(x.daily_rating_total,(select daily_rating_total
                       from ratings
                      where (master_id, submission_dt) = 
                            (Select master_id, max(submission_dt)
                               from ratings
                              where master_id = y.master_id
                                and submission_dt < all_dates
                              group by master_id)
                    )
           ) daily_rating_total
  from ratings x
      ,(select master_id, min_dt+(rn-1) all_dates
          from (select rownum rn from all_tab_columns
                 where rownum <= (select max(diff)
                                    from (select max(trunc(submission_dt))
                                                -min(trunc(submission_dt))+1 diff
                                            from ratings
                                           group by master_id)
                                 )
               )
              ,(select master_id
                      ,max(trunc(submission_dt)) max_dt
                      ,min(trunc(submission_dt)) min_dt
                  from ratings
                 group by master_id)
         where min_dt+(rn-1) <= max_dt
        ) y
 where x.submission_dt(+) = y.all_dates
   and x.master_id(+) = y.master_id
/

                   Daily
Master Submission Rating
ID     Dt          Total
------ ---------- ------
x      01-SEP-07    5.00
x      02-SEP-07    4.75
x      03-SEP-07    4.75
x      04-SEP-07    4.75
x      05-SEP-07    4.25
x      06-SEP-07    4.25
x      07-SEP-07    4.80
y      06-SEP-07    4.75
y      07-SEP-07    4.50
y      08-SEP-07    4.50
y      09-SEP-07    4.50
y      10-SEP-07    4.00
y      11-SEP-07    4.00
y      12-SEP-07    4.55
*********************************************************************************

There are probably many ways to solve your need, including some code using Oracle Analytics (that would probably be much tighter than mine, above), but this is what I threw together without thinking about Analytics.

I did not comment the code, so if you need explanation of what any piece of the code means/does, please post a follow-up question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[daddypost]

Thank you for the code. It did work to a point, and probably only because I didn't explain myself well enough. Right now it is filling in the missing dates between reviews. The issue is when an item only has one review on 7/12/2007, I need it then to fill in the missing dates between that date and today. All of the items, regardless of when their last review was, needs to be filled in to current. I'm thinking I can just change the max(trunc(submission_dt)) to trunc(sysdate) and that should take care of everything. I will try that and let you know, but I wanted to express how helpful you have been, not only today but in a few instances in the past. You are an absolute lifesaver. It probably would have taken me 4 years to figure out what you wrote in probably 5 minutes. But I learn something everytime you give advice.

 

[daddypost]

By the way, if you happen to have spare time and feel like explaining exactly what you did, or at least the logic behind it, I would greatly appreciate it. I will have to say I'm a little confused about the select rownum from all_tab_columns?
Well, to be honest, I may be somewhat dumbfounded over some other things as well.
Thank you in advance should you have time.