CODEWITHSUNDEEP
sqloracle-databasedate
jichoul shin
jichoul shin

Reputation: 71

Fill in missing dates in date range from a table

table A
no  date        count
1   20160401    1
1   20160403    4
2   20160407    3

result
no  date        count
1   20160401    1
1   20160402    0
1   20160403    4
1   20160404    0
.
.
.
2   20160405    0
2   20160406    0
2   20160407    3
.
.
.

I'm using Oracle and I want to write a query that returns rows for every date within a range based on table A.

Is there some function in Oracle that can help me?

Upvotes: 0

Views: 192

Answers (3)

saphsys
saphsys

Reputation: 96

Try this:

with 
  A as (
    select 1 no, to_date('20160401', 'yyyymmdd') dat, 1 cnt from dual union all
    select 1 no, to_date('20160403', 'yyyymmdd') dat, 4 cnt from dual union all
    select 2 no, to_date('20160407', 'yyyymmdd') dat, 3 cnt from dual),
  B as (select min(dat) mindat, max(dat) maxdat from A t),
  C as (select level + mindat - 1 dat from B connect by level + mindat - 1 <= maxdat),
  D as (select distinct no from A),
  E as (select * from D,C)
select E.no, E.dat, nvl(cnt, 0) cnt
from E
full outer join A on A.no = E.no and A.dat = E.dat
order by 1, 2, 3

Upvotes: 0

sql_dummy
sql_dummy

Reputation: 745

you can use the SEQUENCES.

First create a sequence 

 Create Sequence seq_name start with 20160401 max n;

where n is the max value till u want to display.

Then use the sql 

 select seq_name.next,case when seq_name.next = date then count else 0 end from tableA;

Note:- Its better not to use date,count as the column names.

Upvotes: 1

John Bingham
John Bingham

Reputation: 2006

This isn't an oracle specific answer, you'll need to translate it to oracle yourself.

Create an intervals table, containing all integers from 0 to 999. Something like this:

CREATE TABLE intervals (days int);
INSERT INTO intervals (days) VALUES (0), (1);
DECLARE @rc int;
SELECT @rc = 2;
WHILE (SELECT Count(*) FROM intervals) < 1000 BEGIN
    INSERT INTO intervals (days) SELECT days + @rc FROM intervals WHERE days + @rc < 1000;
    SELECT @rc = @rc * 2
END;

Then all the dates in the range can be identified by adding intervals.days to the first date you've got, where the first date + intervals.days is <= the end date, and the resultant date is new. Do this by cross joining intervals to your own table. Something like (it would be in SQL, but again you'll need to translate):

SELECT DateAdd(a.date, d, i.days)
FROM (select min(date) from table_A) a, intervals I
WHERE DateAdd(a.date, d, i.days) < (select max(date) from table_A)
    AND NOT EXISTS (select 1 from table_A aa where aa.date = DateAdd(a.date, d, i.days))

Hope this gives you a starting point

Upvotes: 0

Related Questions