``````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
``````

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.