How do I fill in missing dates by group in Oracle with changing count value

user_9005317 0
Viewed 123

This is a slight modification to a question I posted previously: How do I fill in missing dates by group in Oracle

I have the following table in Oracle SQL:

+-----------+-------+-------+
|   Date    | Color | Count |
+-----------+-------+-------+
| 16-Jan-20 | blue  |     7 |
| 19-Jan-20 | blue  |    32 |
| 23-Jan-20 | blue  |    16 |
| 28-Jan-20 | blue  |    24 |
| 31-Jan-20 | blue  |    41 |
| 2-Feb-20  | blue  |    11 |
| 28-Jan-20 | red   |     1 |
| 3-Feb-20  | red   |     5 |
| 6-Feb-20  | red   |    11 |
| 11-Feb-20 | red   |     4 |
| 15-Feb-20 | red   |     6 |
+-----------+-------+-------+

What I want to do is for each color, fill in the missing dates from January 1, 2020 to February 29, 2020. In doing so, I want to use the COUNT column in the following way:

  1. Get the COUNT for a COLOR in a forward-fill manner. For example, in my table, January 17, 2020 is missing for BLUE. I will fill in the COUNT for January 17, 2020 with 7 because it is the latest available count for that color. Similarly, for January 22, 2020, for BLUE, I will fill in 32 for COUNT.
  2. If there is no COUNT value that exist before the given date, then fill in the COUNT value with the earlierst COUNT value available. For example, for January 2, 2020, COUNT value will be 7 for BLUE.

The resulting table is below:


+-----------+-------+-------+
|   Date    | Color | Count |
+-----------+-------+-------+
| 1-Jan-20  | blue  |     7 |
| 2-Jan-20  | blue  |     7 |
| 3-Jan-20  | blue  |     7 |
| 4-Jan-20  | blue  |     7 |
| 5-Jan-20  | blue  |     7 |
| 6-Jan-20  | blue  |     7 |
| 7-Jan-20  | blue  |     7 |
| 8-Jan-20  | blue  |     7 |
| 9-Jan-20  | blue  |     7 |
| 10-Jan-20 | blue  |     7 |
| 11-Jan-20 | blue  |     7 |
| 12-Jan-20 | blue  |     7 |
| 13-Jan-20 | blue  |     7 |
| 14-Jan-20 | blue  |     7 |
| 15-Jan-20 | blue  |     7 |
| 16-Jan-20 | blue  |     7 |
| 17-Jan-20 | blue  |     7 |
| 18-Jan-20 | blue  |     7 |
| 19-Jan-20 | blue  |    32 |
| 20-Jan-20 | blue  |    32 |
| 21-Jan-20 | blue  |    32 |
| 22-Jan-20 | blue  |    32 |
| 23-Jan-20 | blue  |    16 |
| 24-Jan-20 | blue  |    16 |
| 25-Jan-20 | blue  |    16 |
| 26-Jan-20 | blue  |    16 |
| 27-Jan-20 | blue  |    16 |
| 28-Jan-20 | blue  |    24 |
| 29-Jan-20 | blue  |    24 |
| 30-Jan-20 | blue  |    24 |
| 31-Jan-20 | blue  |    41 |
| 1-Feb-20  | blue  |    41 |
| 2-Feb-20  | blue  |    11 |
| 3-Feb-20  | blue  |    11 |
| 4-Feb-20  | blue  |    11 |
| 5-Feb-20  | blue  |    11 |
| 6-Feb-20  | blue  |    11 |
| 7-Feb-20  | blue  |    11 |
| 8-Feb-20  | blue  |    11 |
| 9-Feb-20  | blue  |    11 |
| 10-Feb-20 | blue  |    11 |
| 11-Feb-20 | blue  |    11 |
| 12-Feb-20 | blue  |    11 |
| 13-Feb-20 | blue  |    11 |
| 14-Feb-20 | blue  |    11 |
| 15-Feb-20 | blue  |    11 |
| 16-Feb-20 | blue  |    11 |
| 17-Feb-20 | blue  |    11 |
| 18-Feb-20 | blue  |    11 |
| 19-Feb-20 | blue  |    11 |
| 20-Feb-20 | blue  |    11 |
| 21-Feb-20 | blue  |    11 |
| 22-Feb-20 | blue  |    11 |
| 23-Feb-20 | blue  |    11 |
| 24-Feb-20 | blue  |    11 |
| 25-Feb-20 | blue  |    11 |
| 26-Feb-20 | blue  |    11 |
| 27-Feb-20 | blue  |    11 |
| 28-Feb-20 | blue  |    11 |
| 29-Feb-20 | blue  |    11 |
| 1-Jan-20  | red   |     1 |
| 2-Jan-20  | red   |     1 |
| 3-Jan-20  | red   |     1 |
| 4-Jan-20  | red   |     1 |
| 5-Jan-20  | red   |     1 |
| 6-Jan-20  | red   |     1 |
| 7-Jan-20  | red   |     1 |
| 8-Jan-20  | red   |     1 |
| 9-Jan-20  | red   |     1 |
| 10-Jan-20 | red   |     1 |
| 11-Jan-20 | red   |     1 |
| 12-Jan-20 | red   |     1 |
| 13-Jan-20 | red   |     1 |
| 14-Jan-20 | red   |     1 |
| 15-Jan-20 | red   |     1 |
| 16-Jan-20 | red   |     1 |
| 17-Jan-20 | red   |     1 |
| 18-Jan-20 | red   |     1 |
| 19-Jan-20 | red   |     1 |
| 20-Jan-20 | red   |     1 |
| 21-Jan-20 | red   |     1 |
| 22-Jan-20 | red   |     1 |
| 23-Jan-20 | red   |     1 |
| 24-Jan-20 | red   |     1 |
| 25-Jan-20 | red   |     1 |
| 26-Jan-20 | red   |     1 |
| 27-Jan-20 | red   |     1 |
| 28-Jan-20 | red   |     1 |
| 29-Jan-20 | red   |     1 |
| 30-Jan-20 | red   |     1 |
| 31-Jan-20 | red   |     1 |
| 1-Feb-20  | red   |     1 |
| 2-Feb-20  | red   |     1 |
| 3-Feb-20  | red   |     5 |
| 4-Feb-20  | red   |     5 |
| 5-Feb-20  | red   |     5 |
| 6-Feb-20  | red   |    11 |
| 7-Feb-20  | red   |    11 |
| 8-Feb-20  | red   |    11 |
| 9-Feb-20  | red   |    11 |
| 10-Feb-20 | red   |    11 |
| 11-Feb-20 | red   |     4 |
| 12-Feb-20 | red   |     4 |
| 13-Feb-20 | red   |     4 |
| 14-Feb-20 | red   |     4 |
| 15-Feb-20 | red   |     6 |
| 16-Feb-20 | red   |     6 |
| 17-Feb-20 | red   |     6 |
| 18-Feb-20 | red   |     6 |
| 19-Feb-20 | red   |     6 |
| 20-Feb-20 | red   |     6 |
| 21-Feb-20 | red   |     6 |
| 22-Feb-20 | red   |     6 |
| 23-Feb-20 | red   |     6 |
| 24-Feb-20 | red   |     6 |
| 25-Feb-20 | red   |     6 |
| 26-Feb-20 | red   |     6 |
| 27-Feb-20 | red   |     6 |
| 28-Feb-20 | red   |     6 |
| 29-Feb-20 | red   |     6 |
+-----------+-------+-------+

Please note that there will be indefinite number of COLORs. The COLOR_COUNT will change unpredictably as well.

Can someone show me how to do this in Oracle SQL?

Any help would be greatly appreciated!

Below is the sql query to generate the table in Oracle for your convenience:

with tbl as (
    select to_date('1/28/2020 09:29', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color,  1 color_count from dual union
    select to_date('2/3/2020 07:04', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  5 color_count from dual union
    select to_date('2/6/2020 12:11', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  11 color_count from dual union
    select to_date('2/11/2020 17:15', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  4 color_count from dual union
    select to_date('2/15/2020 03:46', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  6 color_count from dual union
    select to_date('1/16/2020 14:52', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 7 color_count from dual union
    select to_date('1/19/2020 22:30', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 32 color_count from dual union
    select to_date('1/23/2020 05:17', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 16 color_count from dual union
    select to_date('1/28/2020 18:35', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 24 color_count from dual union
    select to_date('1/31/2020 15:38', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 41 color_count from dual union
    select to_date('2/2/2020 16:01', 'MM/DD/YYYY HH24:MI') as color_date,  'blue' as color, 11 color_count from dual
)
select *
from tbl
order by color, color_date
oracle
2 Answers

That's it:

with tbl as (
    select to_date('1/28/2020 09:29', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color,  1 color_count from dual union
    select to_date('2/3/2020 07:04', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  5 color_count from dual union
    select to_date('2/6/2020 12:11', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  11 color_count from dual union
    select to_date('2/11/2020 17:15', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  4 color_count from dual union
    select to_date('2/15/2020 03:46', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  6 color_count from dual union
    select to_date('1/16/2020 14:52', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 7 color_count from dual union
    select to_date('1/19/2020 22:30', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 32 color_count from dual union
    select to_date('1/23/2020 05:17', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 16 color_count from dual union
    select to_date('1/28/2020 18:35', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 24 color_count from dual union
    select to_date('1/31/2020 15:38', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 41 color_count from dual union
    select to_date('2/2/2020 16:01', 'MM/DD/YYYY HH24:MI') as color_date,  'blue' as color, 11 color_count from dual
)
-- get first and last dates:
,first_record(color_date,color,color_count, end_date) as (
   select
      trunc(min(color_date),'yyyy'), -- 1st of Jan of min(color_date) or use own hardcoded date
      min(color) keep(dense_rank first order by color_date), 
      min(color_count) keep(dense_rank first order by color_date),
      last_day(max(color_date)) as end_date -- last dat of max(color_date) or you can replace it with own hardcoded date
   from tbl
)
-- add them into the data from tbl:
,data as (
   select color_date,color,color_count from first_record
   union
   select * from tbl   
)
-- generating all dates:
,dates_generator as (
    select color_date+N as color_date from first_record, xmltable('0 to 100000' columns N int path '.') x
    where x.n <= end_date-color_date
)
-- simple left join:
select 
   g.color_date
  ,nvl(d.color, lag(color ignore nulls) over(order by g.color_date)) as color
  ,nvl(d.color_count, lag(color_count ignore nulls) over(order by g.color_date)) as color_count
from dates_generator g
     left join data d
          on g.color_date=d.color_date
/
user_4291000 
with start_params as (
    select
        to_date('01/01/2020', 'MM/DD/YYYY') as start_date,
        60 numdays
    from dual
),
colors as (
    select to_date('1/28/2020 09:29', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color,  1 color_count from dual union
    select to_date('2/3/2020 07:04', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  5 color_count from dual union
    select to_date('2/6/2020 12:11', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  11 color_count from dual union
    select to_date('2/11/2020 17:15', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  4 color_count from dual union
    select to_date('2/15/2020 03:46', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  6 color_count from dual union
    select to_date('1/16/2020 14:52', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 7 color_count from dual union
    select to_date('1/19/2020 22:30', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 32 color_count from dual union
    select to_date('1/23/2020 05:17', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 16 color_count from dual union
    select to_date('1/28/2020 18:35', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 24 color_count from dual union
    select to_date('1/31/2020 15:38', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 41 color_count from dual union
    select to_date('2/2/2020 16:01', 'MM/DD/YYYY HH24:MI') as color_date,  'blue' as color, 11 color_count from dual
),
upd_colors as (
    select
        (select start_date from start_params) color_date,
        color,
        min(color_count) keep(dense_rank first order by color_date) color_count
    from colors
    group by color
    union
    select trunc(color_date), color, color_count from colors
),
dates as (
    select dat, color
    from (
        select start_date + numtodsinterval(level-1, 'DAY') dat
        from start_params connect by level <= numdays
    ), (select distinct color from colors)
)
select d.dat, d.color,
    nvl(c.color_count, lag(c.color_count ignore nulls) over (partition by d.color order by d.dat)) color_count
from dates d, upd_colors c
where c.color_date(+) = d.dat
  and c.color(+) = d.color
order by color, dat

fiddle

user_137543280
Related