Home » SQL & PL/SQL » SQL & PL/SQL » Date interval Blank count (Oracle 11)
Date interval Blank count [message #681957] Wed, 16 September 2020 13:57 Go to next message
Marinus1979
Messages: 2
Registered: September 2020
Junior Member
There are occasions when no data is taken for while. So if there are no data for a particular interval then I would like this to show up as zero so that it shows up on the plot. So if i'm taking averages every 10 minutes and
between 12:30 and 13:00 there is no data stored in the
table how can I get results like

Time Ave
12:00 1.2
12:10 3.0
12:20 4.0
12:30 0
12:40 0
12:50 0
13:00 1.2

[Updated on: Thu, 17 September 2020 01:57] by Moderator

Report message to a moderator

Re: Date interval Blank count [message #681959 is a reply to message #681957] Wed, 16 September 2020 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Outer join your table with a row generator for that.

Re: Date interval Blank count [message #681960 is a reply to message #681957] Wed, 16 September 2020 14:22 Go to previous messageGo to next message
Littlefoot
Messages: 21623
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One option is to create a "calendar" of all times you're working with and outer join it with your own data. See comments.

SQL> with test (datum, val) as
  2    -- your data
  3    (select to_date('10.09.2020 12:03', 'dd.mm.yyyy hh24:mi'), 5 from dual union all
  4     select to_date('10.09.2020 12:06', 'dd.mm.yyyy hh24:mi'), 2 from dual union all
  5     select to_date('10.09.2020 12:14', 'dd.mm.yyyy hh24:mi'), 6 from dual union all
  6     select to_date('10.09.2020 12:22', 'dd.mm.yyyy hh24:mi'), 1 from dual union all
  7     select to_date('10.09.2020 13:02', 'dd.mm.yyyy hh24:mi'), 5 from dual
  8    ),
  9  -- min and max dates involved - needed for a calendar
 10  minimax as
 11    (select trunc(min(datum), 'hh24') mindat,
 12            trunc(max(datum), 'hh24') maxdat
 13     from test
 14    ),
 15  -- "calendar"
 16  times as
 17    (select mindat + 10/(24*60) * (level - 1) tfrom
 18     from minimax
 19     connect by level <= (maxdat - mindat) * 24 * 60 / 6
 20    ),
 21  -- periods of 10 minutes
 22  tab_times as
 23    (select t.tfrom time_from,
 24            lead(t.tfrom) over (order by t.tfrom) time_to
 25     from times t
 26    )
 27  -- finally, outer join created "calendar" with your own data
 28  select to_char(tt.time_from, 'hh24:mi') time_From,
 29         to_char(tt.time_to, 'hh24:mi')   time_to,
 30         nvl(avg(s.val), 0) avg_val
 31  from tab_times tt left join test s on s.datum >= tt.time_from
 32                                    and s.datum <  tt.time_to
 33  group by tt.time_from, tt.time_to
 34  order by tt.time_from;

TIME_ TIME_    AVG_VAL
----- ----- ----------
12:00 12:10        3,5
12:10 12:20          6
12:20 12:30          1
12:30 12:40          0
12:40 12:50          0
12:50 13:00          0
13:00 13:10          5
13:10 13:20          0
13:20 13:30          0
13:30                0

10 rows selected.

SQL>
Re: Date interval Blank count [message #681963 is a reply to message #681960] Thu, 17 September 2020 01:43 Go to previous messageGo to next message
Marinus1979
Messages: 2
Registered: September 2020
Junior Member
Thanks, it seems to be working..

just a question:

If I want to change the interval must I just change mindat+10 or also connect by level <= (maxdat - mindat) * 24 * 60 / 6

17 (select mindat + 10/(24*60) * (level - 1) tfrom
18 from minimax
19 connect by level <= (maxdat - mindat) * 24 * 60 / 6

Re: Date interval Blank count [message #681964 is a reply to message #681963] Thu, 17 September 2020 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Wed, 16 September 2020 21:09

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.


Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Outer join your table with a row generator for that.
Re: Date interval Blank count [message #681967 is a reply to message #681964] Fri, 18 September 2020 10:07 Go to previous message
Littlefoot
Messages: 21623
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Marinus1979, yes - both should be changed. Try it and see.
Previous Topic: understanding table partition
Next Topic: Query help on 5 tables.
Goto Forum:
  


Current Time: Sat Nov 28 22:41:24 CST 2020