CODEWITHSUNDEEP
sqloracle-database
John
John

Reputation: 181

Adding missing records to table Oracle between specified dates

I have table as below :

enter image description here

But what I want is below output:

enter image description here

For each name record should be present on each day starting from min.date to max. date. If the record is not present in original table then it's value should be updated as 0 on particular date.

I'm not able to figure out the query for this. Pls. can anyone could suggest me writing query for this.

Thanks in advance..!

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you have all dates in the table, you can use the data in the original tables. Use cross join to generate the rows and then left join to bring in the values:

select n.name, d.date, coalesce(t.value, 0) as value
from (select distinct name from t) n cross join
     (select distinct date from t) d left join
     t
     on t.name = n.name and t.date = d.date;

If some dates are missing entirely from the table, then you need a calendar table or some sort of CTE to generate all the dates.

Upvotes: 3

Related Questions