1

I'm new to writing Oracle SQL, using Oracle SQL Developer, having changed from MS SQL Server environment. Trying to calculate average between two dates, truncated, and am getting values where one of the dates (appt.CONTACT_DATE) is NULL. I would expect a null there. See my code below. What am I doing incorrectly? Help is very much appreciated.

TRUNC (
          AVG (
               TO_DATE (appt.CONTACT_DATE, 'DD-MM-YYYY')
             - TO_DATE (REF.ENTRY_DATE, 'DD-MM-YYYY'))
          OVER (PARTITION BY ser2.PROV_NAME),
          1)
          AS "AVG BY PROVIDER"
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
Jen
  • 29
  • 2
  • You mean that if one of the input values is null you'd expect the whole average to be null? Oracle AVG ignores nulls and just averages the rest: https://stackoverflow.com/q/22220449 – Rup Apr 30 '18 at 16:17
  • Yes... I don't want an average unless both input values are populated. Thanks! – Jen Apr 30 '18 at 16:20
  • The title should be a summary of your problem, not a fragment of the query that is causing you problems. – Mark Rotteveel Apr 30 '18 at 16:23
  • sorry dude, just joined, thanks for the tip! – Jen Apr 30 '18 at 16:27

1 Answers1

2

That would be

TRUNC (AVG (appt.contact_date - REF.entry_date) OVER (PARTITION BY ser2.prov_name), 1)

because:

  • if CONTACT_DATE and ENTRY_DATE are of the DATE datatype (and yes, they should be), you don't have to (actually, must not) "convert" to date again.
    • If they are strings (i.e. stored into VARCHAR2 columns), then yes - you should convert them to DATEs by applying the TO_DATE with the appropriate format mask
  • difference between two DATE values is number of days, so no problem in AVG-ing that
  • truncate is OK as well

EDIT

Some more info: here's an example which shows what's going on:

SQL> with
  2  appt (contact_date) as
  3    (select trunc(sysdate) from dual union
  4     select null from dual union all
  5     select null from dual),
  6  ref (entry_date) as
  7    (select trunc(sysdate - 5) from dual),
  8  ser2 (prov_name) as
  9    (select 'a' from dual)
 10  select appt.contact_date, ref.entry_date, ser2.prov_name,
 11    TRUNC (AVG (appt.contact_date - REF.entry_date) OVER (PARTITION BY ser2.prov_name), 1) avg_by_provider
 12  from appt, ref, ser2;

CONTACT_ ENTRY_DA P AVG_BY_PROVIDER
-------- -------- - ---------------
30.04.18 25.04.18 a               5
         25.04.18 a               5
         25.04.18 a               5

SQL>

Although there are two NULL values, AVG still returns the result which is not NULL. How come? Because of ANSI SQL standard, which says that aggregate functions (such as AVG) ignore NULL values.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • The OP is truncating (or attempting to truncate using `TO_DATE`) before subtracting the dates so that the number of whole days difference is found before doing the average. Your query is finding the fractional days difference, averaging and then truncating which is a very different calculation. To match the OP's query it should be `AVG( TRUNC( contact_date ) - TRUNC( entry_date ) ) OVER ...`. – MT0 May 01 '18 at 09:01