Oracle: Select Missing Dates

Oracle: select missing dates

Something like this (assuming your table is named your_table and the date column is named the_date):

with date_range as (
      select min(the_date) as oldest, 
             max(the_date) as recent, 
             max(the_date) - min(the_date) as total_days
      from your_table
),
all_dates as (
   select oldest + level - 1 as a_date
   from date_range
   connect by level <= (select total_days from date_range)
)
select ad.a_date
from all_dates ad
  left join your_table yt on ad.a_date = yt.the_date
where yt.the_date is null
order by ad.a_date;

Edit:

the WITH clause is called a "common table expression" and is equivalent to a derived table ("inline view").

It's similar to 

select * 
from ( 
     ..... 
) all_dates
join your_table ...

The second CTE simply creates a list of dates "on-the-fly" using a undocumented feature of Oracle's connect by implementation.

Re-using a select (like I did with calculating the first and last date) is a bit easier (and IMHO more readable) than using derived tables.

Edit 2:

This can be done with a recursive CTE as well:

with date_range as (
      select min(the_date) as oldest, 
             max(the_date) as recent, 
             max(the_date) - min(the_date) as total_days
      from your_table
),
all_dates (a_date, lvl) as (
   select oldest as a_date, 1 as lvl
   from date_range 
   union all
   select (select oldest from date_range) + lvl, lvl + 1
   from all_dates 
   where lvl < (select total_days from date_range)
)
select ad.a_date, lvl
from all_dates ad    
  left join your_table yt on ad.a_date = yt.the_date
where yt.the_date is null
order by ad.a_date;

Which should work in all DBMS supporting recursive CTEs (PostgreSQL and Firebird - being more standard compliant - do need the recursive keyword though).

Note the hack select (select oldest from date_range) + lvl, lvl + 1 in the recursive part. This should not be necessary, but Oracle still has some bugs with regards to DATEs in a recursive CTE. In PostgreSQL the following works without problems:

....
all_dates (a_date, lvl) as (
   select oldest as a_date, 0 as lvl
   from date_range 
   union all
   select a_date + 1, lvl + 1
   from all_dates 
   where lvl < (select total_days from date_range)
)
....

Find missing dates using SQL

This will get you all missing days for one year (SQL Fiddle).

all_dates generates a list of all dates of 2012 (adjust as required), and the LEFT JOIN checking for IS NULL eliminates those dates that exist in your source-table.

WITH all_dates AS (
  SELECT TO_DATE('01-jan-2012') + ROWNUM - 1 AS d
  FROM dual
  CONNECT BY ROWNUM <= ADD_MONTHS(TO_DATE('01-jan-2012'), 12 ) - TO_DATE('01-jan-2012')
)
SELECT all_dates.d
FROM all_dates
LEFT JOIN t ON ( t.d = all_dates.d )
WHERE t.d IS NULL
ORDER BY all_dates.d
;

Make sure to use a bind variable instead of hard-coding the date three times.

SQL to identify missing dates in column

For multiple names, you can use the LEAD analytic function to find the next date and then CROSS JOIN LATERAL (available from Oracle 12) a row-generator to generate the missing values:

SELECT t.name,
       m.missing
FROM   (
         SELECT name,
                dt,
                LEAD(dt) OVER (PARTITION BY name ORDER BY dt) AS next_dt
         FROM   table_name
       ) t
       CROSS JOIN LATERAL (
         SELECT dt + LEVEL AS missing
         FROM   DUAL
         WHERE  dt + 1 < next_dt
         CONNECT BY dt + LEVEL < next_dt
       ) m

Which, for the sample data:

CREATE TABLE table_name (Name,dt) AS
  SELECT 'A', DATE '2000-01-04' FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-05' FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-08' FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-08' FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-10' FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-05' FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-09' FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-10' FROM DUAL;

Outputs:



































NAMEMISSING
A06-JAN-00
A07-JAN-00
A09-JAN-00
B06-FEB-01
B07-FEB-01
B08-FEB-01

ORACLE SQL: Fill in missing dates

You can get the 30-day period from SYSDATE as follows (I assume you want to include SYSDATE?):

WITH mydates AS (
    SELECT TRUNC(SYSDATE) - 1 + LEVEL AS due_date FROM dual
   CONNECT BY LEVEL <= 31
)

Then use the above to do a LEFT JOIN with your query (perhaps not a bad idea to put your query in a CTE as well):

WITH mydates AS (
    SELECT TRUNC(SYSDATE) - 1 + LEVEL AS due_date FROM dual
   CONNECT BY LEVEL <= 31
), myorders AS (
    select 
    (case when trunc(so.revised_due_date) <= trunc(sysdate) 
        then trunc(sysdate) else trunc(so.revised_due_date) end) due_date, 
    (case 
        when (case when sp.pr_typ in ('VV','VD') then 'DVD' when sp.pr_typ in ('RD','CD') 
        then 'CD' end) = 'CD' 
        and  (case when so.tec_criteria in ('PI','MC') 
        then 'XX' else so.tec_criteria end) = 'OF'
        then sum(so.revised_qty_due)
    end) CD_OF_VOLUME
    from shop_order so
    left join scm_prodtyp sp
    on so.prodtyp = sp.prodtyp
    where so.order_type = 'MD' 
    and so.plant = 'W' 
    and so.status_code between '4' and '8' 
    and trunc(so.revised_due_date) <= trunc(sysdate)+30
    group by trunc(so.revised_due_date), so.tec_criteria, sp.pr_typ
    order by trunc(so.revised_due_date)
)
SELECT mydates.due_date, myorders.cd_of_volume
  FROM mydates LEFT JOIN myorders
    ON mydates.due_date = myorders.due_date;

If you want to show a zero on "missing" dates instead of a NULL, use COALESCE(myorders.cd_of_volume, 0) AS cd_of_volume above.

Find missing dates by comparing two columns in Table and View using PL/SQL

Loops over millions of rows are slow. Why wouldn't you skip them entirely and do something like this?

insert into tablename (date_column_name)
select date_column_name from viewname
minus
select date_column_name from tablename;

I guess that this code lacks quite a lot of info (additional columns), but hey - you didn't post them either. So - see whether such a principle would work. If it has to be dynamic SQL (as you have 100+ tables), so be it. But, I presume & hope that it would perform better.

As of invalid dates: well, that's the cost of storing dates as strings.

One option might be to create a function which returns e.g. 1 (if date is valid) or 0 (if it is not). You'd use it as

select date_column_name from viewname 
where f_date_valid(date_column_name) = 1

but it'll affect performance.

Perhaps you could do it in two steps:

  • first insert all missing dates (including invalid ones)
  • then delete rows with invalid dates

Though, I think that whichever approach you take, performance will suffer, and all that because you store dates as strings ... bad, bad idea. I'm not saying it was yours (probably it is not).

How do I fill in missing dates by group in Oracle with changing count value

with start_params as (
    select
        to_date('01/01/2020', 'MM/DD/YYYY') as start_date,
        60 numdays
    from dual
),
colors as (
    select to_date('1/28/2020 09:29', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color,  1 color_count from dual union
    select to_date('2/3/2020 07:04', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  5 color_count from dual union
    select to_date('2/6/2020 12:11', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  11 color_count from dual union
    select to_date('2/11/2020 17:15', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  4 color_count from dual union
    select to_date('2/15/2020 03:46', 'MM/DD/YYYY HH24:MI') as color_date,  'red' as color,  6 color_count from dual union
    select to_date('1/16/2020 14:52', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 7 color_count from dual union
    select to_date('1/19/2020 22:30', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 32 color_count from dual union
    select to_date('1/23/2020 05:17', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 16 color_count from dual union
    select to_date('1/28/2020 18:35', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 24 color_count from dual union
    select to_date('1/31/2020 15:38', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 41 color_count from dual union
    select to_date('2/2/2020 16:01', 'MM/DD/YYYY HH24:MI') as color_date,  'blue' as color, 11 color_count from dual
),
upd_colors as (
    select
        (select start_date from start_params) color_date,
        color,
        min(color_count) keep(dense_rank first order by color_date) color_count
    from colors
    group by color
    union
    select trunc(color_date), color, color_count from colors
),
dates as (
    select dat, color
    from (
        select start_date + numtodsinterval(level-1, 'DAY') dat
        from start_params connect by level <= numdays
    ), (select distinct color from colors)
)
select d.dat, d.color,
    nvl(c.color_count, lag(c.color_count ignore nulls) over (partition by d.color order by d.dat)) color_count
from dates d, upd_colors c
where c.color_date(+) = d.dat
  and c.color(+) = d.color
order by color, dat

fiddle

How to find missing data in a range of dates in oracle sql

I want a query to retreive the empno and the date where the employee
was absent

In such a case you need to generate all possible dates in a given range using for example this query:

SELECT date '2016-07-11' + level - 1 As "DATE" from dual
CONNECT BY LEVEL <= date '2016-07-13' - date '2016-07-11' + 1;

DATE            
-----------------
16/07/11 00:00:00
16/07/12 00:00:00
16/07/13 00:00:00

then use a cross join to generate all possible pairs: date+employe

SELECT e.empno, d."DATE"
FROM (
    SELECT date '2016-07-11' + level - 1 As "DATE" from dual
    CONNECT BY LEVEL <= date '2016-07-13' - date '2016-07-11' + 1
) d
CROSS JOIN empno e

and then filter existing records out (leaving only not existing pairs: empno+date) using an outer join and IS NULL condition

SELECT e.empno, d."DATE"
FROM (
    SELECT date '2016-07-11' + level - 1 As "DATE" from dual
    CONNECT BY LEVEL <= date '2016-07-13' - date '2016-07-11' + 1
  ) d
CROSS JOIN EMPLOYEES e 
LEFT JOIN ATTENDANCE a 
ON ( a.empno = e.empno AND a."DATE" = d."DATE" )
WHERE a."DATE" IS NULL
order by 1,2

Related Topics

Delete ... from ... Where ... In

How to Relate 3 Tables Depending on Event

"Pivoting" a Table in SQL (I.E. Cross Tabulation/Crosstabulation)

Sql Best Practices - Ok to Rely on Auto Increment Field to Sort Rows Chronologically

Extract Email Address from String Using Tsql

Sql How to Remove Duplicates Within Select Query

Creating Trigger That Runs on Two Tables

Sqlite: Alias Column Name Can't Contains a Dot "."

Order by Column1 If Column1 Is Not Null, Otherwise Order by Column2

How to Bulk Update with SQL Server

Making Ssdt Just Generate a SQL Script (And Not Deploy a Database)

Compare Current Row with Previous Row in SQL Server

How to Delete Duplicate Records in Sql

Sql Primary Key, Int or Guid Or..

Create a New Db User in SQL Server 2005

Sql 2008 Vs 2012 Error: Incorrect Syntax Near The Keyword 'Compute'

Dividing 2 Numbers in SQL Server

Why Does This Oracle Drop Column Alter The Default Value of Another Column


Leave a reply



Submit