How to get number of days between the earliest date and the oldest date in every row of my input

Question

I am having difficulty getting the number of days between the oldest date and the newest date, the problem I have is if I use MIN or MAX I have to group by a column which is an issue as I do not have a column to group by.

If my data looks like this:

 2017/01/01         EXAMPLE 
 2017/01/01         EXAMPLE
 2017/01/01         EXAMPLE
 2017/01/05         EXAMPLE
 2017/01/06         EXAMPLE
 2017/01/06         EXAMPLE
 2017/01/08         EXAMPLE

I need to add a column with a static number throughout the table so it looks like:

 2017/01/01         EXAMPLE       8
 2017/01/01         EXAMPLE       8
 2017/01/01         EXAMPLE       8
 2017/01/05         EXAMPLE       8
 2017/01/06         EXAMPLE       8
 2017/01/06         EXAMPLE       8
 2017/01/08         EXAMPLE       8

This is probably very simple but I just cannot get it right without grouping - 8 is the number of days between the earliest day and the last day

This is for ORACLE

What is "EXAMPLE" Is your example data two columns? Please clarify. — OldProgrammer, Aug 17 '17 at 13:52

Ponder Stibbons · Answer 1 · 2017-08-18T09:41:07.653

Use max() and min() in analytic version:

select dt, str, max(dt) over () - min(dt) over () + 1 diff
  from t

Example:

with t (dt, str) as (
    select date '2017-01-01', 'EXAMPLE' from dual union all
    select date '2017-01-01', 'EXAMPLE' from dual union all
    select date '2017-01-01', 'EXAMPLE' from dual union all
    select date '2017-01-05', 'EXAMPLE' from dual union all
    select date '2017-01-06', 'EXAMPLE' from dual union all
    select date '2017-01-06', 'EXAMPLE' from dual union all
    select date '2017-01-08', 'EXAMPLE' from dual)
select dt, str, max(dt) over () - min(dt) over () + 1 diff
  from t

Result:

DT          STR           DIFF
----------- ------- ----------
2017-01-01  EXAMPLE          8
2017-01-01  EXAMPLE          8
2017-01-01  EXAMPLE          8
2017-01-05  EXAMPLE          8
2017-01-06  EXAMPLE          8
2017-01-06  EXAMPLE          8
2017-01-08  EXAMPLE          8
7 rows selected

Edit: Your query should be:

select RATE_CODE, BUSINESS_DATE, 
       max(BUSINESS_DATE) over () - min(BUSINESS_DATE) over () + 1 diff 
  from RATE_CODE_STAT_DAILY

You don't need with clause, I added it only to make some example data. Just use query above. And I didn't know real table name and column names.

In this case you should use min and max. First_value and last_value are alternative, but please read carefully documentation and understand difference.

with RATE_CODE_STAT_DAILY (RATE_CODE, BUSINESS_DATE) as ( select RATE_CODE, BUSINESS_DATE from dual ) select RATE_CODE, BUSINESS_DATE, max(BUSINESS_DATE) over () - min(BUSINESS_DATE) over () + 1 diff from RATE_CODE_STAT_DAILY — , Aug 18 '17 at 06:45
Hi Ponder, Thanks very much for the above, I am having difficulty as my SQLDeveloper is kicking out errors saying invalid identifier on RATE_CODE after the Select from the above query — , Aug 18 '17 at 06:47

score 0 · Answer 2 · answered Aug 18 '17 at 07:13

0

The below gave me exactly what I need - thank you @ponder for the hints on analytic, did some research and came across FIRST_VALUE and LAST_VALUE

select 
RATE_CODE,
BUSINESS_DATE,
FIRST_VALUE(BUSINESS_DATE) over() AS FIRSTDATE,
LAST_VALUE(BUSINESS_DATE) over(ORDER BY RATE_CODE RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LASTDATE

from RATE_CODE_STAT_DAILY
where BUSINESS_DATE > '01-JUL-17'

answered Aug 18 '17 at 07:13

I think You should use `min` and `max`. The query you attached in comments does not work because `dual` has not columns like `rate_code`. Simply replace `first_value` with `min` and last_value with `max` in your answer and work on real table. But if You insist on `first_value` and it works for You it's OK. For me it looks somewhat suspicious. – Ponder Stibbons Aug 18 '17 at 09:31

How to get number of days between the earliest date and the oldest date in every row of my input

2 Answers2