How to fill in missing dates with groups in a table in sql

Question

I want to know how to use loops to fill in missing dates with a value of zero based on start and end dates for groups in sql so that I have consecutive time series in each group. I have two questions.

• how is the loop for each group?
• How to use start and end dates for each group to dynamically populate missing dates?

My input and expected output are listed below.

Input: I have table A as

date value grp_no 8/06/12 1 1 8/08/12 1 1 8/09/12 0 1 8/07/12 2 2 8/08/12 1 2 8/12/12 3 2 

I also have a table B that can be used to left join with A to fill in the missing dates.

 date ... 8/05/12 8/06/12 8/07/12 8/08/12 8/09/12 8/10/12 8/11/12 8/12/12 8/13/12 ... 

How can I use A and B to create the next output in sql?

Output:

 date value grp_no 8/06/12 1 1 8/07/12 0 1 8/08/12 1 1 8/09/12 0 1 8/07/12 2 2 8/08/12 1 2 8/09/12 0 2 8/10/12 0 2 8/11/12 0 2 8/12/12 3 2 

Send me your code and offer. Thank you so much in advance.

Answer 1

You can do it without cycles

 SELECT p.date, COALESCE(a.value, 0) value, p.grp_no FROM ( SELECT grp_no, date FROM ( SELECT grp_no, MIN(date) min_date, MAX(date) max_date FROM tableA GROUP BY grp_no ) q CROSS JOIN tableb b WHERE b.date BETWEEN q.min_date AND q.max_date ) p LEFT JOIN TableA a ON p.grp_no = a.grp_no AND p.date = a.date 

The innermost subquery captures minutes and maximum numbers for each group. Then cross-connecting to TableB creates all possible dates in the min-max range for each group. Finally, the outer selection uses an outer join with TableA and populates the value 0 column for dates not in TableA .

Output:

  |  DATE |  VALUE |  GRP_NO |
 | ------------ | ------- | -------- |
 |  2012-08-06 |  1 |  1 |
 |  2012-08-07 |  0 |  1 |
 |  2012-08-08 |  1 |  1 |
 |  2012-08-09 |  0 |  1 |
 |  2012-08-07 |  2 |  2 |
 |  2012-08-08 |  1 |  2 |
 |  2012-08-09 |  0 |  2 |
 |  2012-08-10 |  0 |  2 |
 |  2012-08-11 |  0 |  2 |
 |  2012-08-12 |  3 |  2 |

Here is the SQLFiddle demo