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.
Oracle SQL - Populate Missing dates and populate last available date values for a given ID
create table test_roll
( dte date,
id int,
value number(9,2)
);
insert into test_roll values(to_date('2/4/17','MM/DD/YYYY'),3, 4.4);
insert into test_roll values(to_date('2/4/17','MM/DD/YYYY'),9, 6.2);
insert into test_roll values(to_date('2/9/17','MM/DD/YYYY'),3, 4.7);
insert into test_roll values(to_date('2/9/17','MM/DD/YYYY'),4, 7.4);
insert into test_roll values(to_date('2/9/17','MM/DD/YYYY'),9, 9.4);
insert into test_roll values(to_date('2/11/17','MM/DD/YYYY'),3, 9.7);
insert into test_roll values(to_date('2/11/17','MM/DD/YYYY'),7, 12.4);
commit;
select * from test_roll order by dte, id;
DTE ID VALUE
--------- ---------- ----------
04-FEB-17 3 4.4
04-FEB-17 9 6.2
09-FEB-17 3 4.7
09-FEB-17 4 7.4
09-FEB-17 9 9.4
11-FEB-17 3 9.7
11-FEB-17 7 12.4
SELECT DTE, ID, LAST_VALUE(value) IGNORE NULLS OVER (PARTITION BY ID ORDER BY ID,DTE) AS value
FROM (
WITH DATES AS
(
select DISTINCT ID, (MIN_DTE + LEVEL - 1) REAL_DTE
FROM (
select ID, min(DTE) as MIN_DTE,
greatest( max(dte), (select max(dte) - 1 from test_roll)) as MAX_DTE
FROM test_roll
group by ID )
CONNECT BY LEVEL <= MAX_DTE - MIN_DTE + 1
)
SELECT D.REAL_DTE AS DTE,
D.ID as ID,
t.value as value
FROM DATES D
LEFT JOIN test_roll t ON t.ID=D.ID AND D.REAL_DTE=t.DTE
) ROLLED
ORDER BY DTE, ID;
DTE ID VALUE
--------- ---------- ----------
04-FEB-17 3 4.4
04-FEB-17 9 6.2
05-FEB-17 3 4.4
05-FEB-17 9 6.2
06-FEB-17 3 4.4
06-FEB-17 9 6.2
07-FEB-17 3 4.4
07-FEB-17 9 6.2
08-FEB-17 3 4.4
08-FEB-17 9 6.2
09-FEB-17 3 4.7
09-FEB-17 4 7.4
09-FEB-17 9 9.4
10-FEB-17 3 4.7
10-FEB-17 4 7.4
10-FEB-17 9 9.4
11-FEB-17 3 9.7
11-FEB-17 7 12.4
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
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)
)
....
How do I fill in missing dates by group in Oracle
You can use Recursive Subquery Factoring to generate intervals:
with tbl as (
select to_date('1/28/2020 11:51', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color from dual union
select to_date('2/3/2020 11:51', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color from dual union
select to_date('2/6/2020 11:51', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color from dual union
select to_date('4/16/2020 11:51', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color from dual union
select to_date('4/19/2020 11:51', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color from dual union
select to_date('4/23/2020 11:51', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color from dual union
select to_date('5/2/2020 11:51', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color from dual
),
tbl_min_max as (
select t.color, min(trunc(color_date)) begin_interval, max(trunc(color_date)) end_interval from tbl t group by t.color
),
tbl_interval(color, begin_interval, end_interval, color_date) as (
select color, begin_interval, end_interval, begin_interval from tbl_min_max
union all
select color, begin_interval, end_interval, color_date + 1 from tbl_interval where color_date < end_interval
)
select
t.color, t.color_date
from
tbl_interval t
order by
t.color, t.color_date
Related Topics
Query to Get Records Based on Radius in SQLite
Rails .Where() Query Not Working
How to Use Free SQLserver Express in Commercial App
Set Limit for a Table Rows in SQL
SQL Server Export to Excel with Openrowset
How to Create an Index for Elements of an Array in Postgresql
SQL Server Displaying Missing Dates
How to Select Records from Last 24 Hours Using SQL
Understanding How Join Works When 3 or More Tables Are Involved. [Sql]
What's Best SQL Datatype for Storing JSON String
Can a Foreign Key Refer to a Primary Key in the Same Table
Add Unique Constraint in SQL Server 2008 Gui
How to Use a Package Constant in SQL Select Statement