Fill in date gaps for all groups

Question

I'm trying to fill in a bunch of date gaps for each group.

I have no problem making this work if have only one group, but when there are more than one, my dates don't fill in as I'd like them to.

As an example, the first screenshot below shows the data coming out of my query. The second screenshot shows what I'd like it to look like. If I do a left join on a date table with only one State, all is good. If there are more one State, the gaps don't fill in correctly.

BEFORE

AFTER

Thank you, this makes perfect sense...just need to spin up a State table and I should be able to go from there! — user1134307, Aug 21 '18 at 14:25

score 2 · Answer 1 · answered Aug 21 '18 at 11:32

The "cheapest" (uses fewest resources, so normally runs fastest) and most maintainable method is to have a table of dates and a table of states. You can then use these table to create a "template" of all the rows you want to return, and then left join your data to that "template".

SELECT
  *
FROM
  dates_table    d
CROSS JOIN
  states_table   s
LEFT JOIN
  data_table     t
    ON  t.date_value = d.date_value
    AND t.state_id   = s.state_id
WHERE
      d.date_value BETWEEN x AND y
  AND s.state_id   IN (1, 2)

Tim Biegeleisen · Answer 2 · 2018-08-21T11:37:33.210

Using a calendar table approach is one option here. You may left join a calendar table containing all date/state combinations to your current table:

WITH dates AS ( 
    SELECT DATE '2018-08-31' AS date FROM dual
    UNION ALL
    SELECT date - 1 AS date
    FROM dates
    WHERE date > DATE '2018-08-01'
),
states AS (
    SELECT 'FL' AS state FROM dual UNION ALL
    SELECT 'GA' FROM dual
)

SELECT
    t1.date,
    t2.state
    t3.qty
FROM dates t1
CROSS JOIN states t2
LEFT JOIN yourTable t3
    ON t1.date = t3.date and t2.state = t3.state
ORDER BY
    t1.date,
    t2.state;

Fill in date gaps for all groups

2 Answers2

Linked