insert into table when when data missing

[sourabh.asu]

Hello,
   I have the following table, for readability I broke down the after every manager for every case number. The last column is the cumulative sum of the second last column. Value for month should ideally run from 1-3 (Like you see for both the cases for Chicago). But from the table you can see in some cases some entries are missing (marked by <-----). 

CITY     CASE   CASE_NUMBER   MANAGER  MONTH  MONTHLY_TOTAL   FISCAL_TOTAL 
---------------------------------------------------------------------------
chicago  case_1   1             John       1         2              2     
chicago  case_1   1             John       2         3              5 
chicago  case_1   1             John       3         5              10

chicago  case_1   1             Jeff       1         4              4     
chicago  case_1   1             Jeff       2         2              6 
chicago  case_1   1             Jeff       3         3              9

chicago  case_2   2             John       1         3              3     
chicago  case_2   2             John       2         2              5 
chicago  case_2   2             John       3         4              9

chicago  case_2   2             Jeff       1         2              2     
chicago  case_2   2             Jeff       2         7              9 <----

newyork  case_1   1             Lee        1         3              3     
newyork  case_1   1             Lee        2         4              7 <----

newyork  case_1   1             Sue        1         2              2     
newyork  case_1   1             Sue        2         3              5     
newyork  case_1   1             Sue        3         2              7

newyork  case_1   2             Lee        1         2              2     
newyork  case_1   2             Lee        2         4              6 
newyork  case_1   2             Lee        3         4              10  

newyork  case_1   2             Sue        1         3              3     
newyork  case_1   2             Sue        2         2              5 <----


What I want is to first find out those missing rows and insert values. For those missing ones monthly_total = 0
fiscal_total = value in previous row. E.g. for first missing row it should be:    

CITY     CASE   CASE_NUMBER   MANAGER  MONTH  MONTHLY_TOTAL   FISCAL_TOTAL 
---------------------------------------------------------------------------
chicago  case_2   2             Jeff       3         0              9

[adam_k]

In SQL syntax you could use the following to insert the missing records:

insert into table 
(city, case, case_number, manager, month, monthly_total, fiscal_total) 
select a.city, a.case, a.case_number, a.manager, b.month_number, 0, sum(monthly_total) from 
(select distinct city, case, case_number, manager from table) a 
full outer join (select 1 as month_number union select 2 union select 3 ) b 
left join 
table c
on a.city = c.city 
and a.case = c.case 
and a.case_number = c.case_number 
and a.manager = c.manager
and b.month_number = c.month
where c.monthly_total is null 

I haven't tested this script, so it might contain errors and typos.

Good luck.