CODEWITHSUNDEEP
sqldatabaseoracle-database
Brian Wilson
Brian Wilson

Reputation: 135

ORACLE SQL - Time between events?

I HAD A PROBLEM THAT WENT LIKE THIS:

How many months from today till Christmas?

SQL> SELECT MONTHS_BETWEEN (TO_DATE('2013/10/14', 'YYYY/MM/DD'),
  2  TO_DATE('2013/12/25', 'YYYY/MM/DD')) AS MONTHS_BETWEEN FROM DUAL;

MONTHS_BETWEEN
--------------
    -2.3548387

Now I have this problem:

How many months are in-between the EVENTs named Flag Football Game and Guest Speaker?

The issue I cannot figure out is that the relevant dates are in the same column (1 for FLAG FOOTBALL, 2 for GUEST_SPEAKER). Also, they are from a table, not a non-entity like ‘dual’.

Perhaps there is a way to nest a ‘WHERE’ clause within the selection statement, such as:

Select TO_CHAR(EVENT_DATE(WHERE EVENT_NAME = UPPER(‘Flag FOOtball Game’) 
– TO_CHAR(EVENT_DATE(WHERE EVENT_NAME = UPPER(‘GUEST SPEAKER’)AS 
TIME FROM FOOTBALL_TO_SPEAKER;

Here is the event table:

SQL> select * from event;

  EVENT_ID   CLASS_ID EVENT_NAME                     EVENT_DAT T RAIN_DATE
---------- ---------- ------------------------------ --------- - ---------
         1          9 ASTRO Physics STAR GAZING      09-NOV-99 O
         2         10 Flag FOOtball Game             19-JAN-99 O 20-JAN-99
         3          1 Guest SPEAKER                  01-DEC-99 I
         4          8 Barney ViDeo                   01-SEP-99 I
         5         10 PICKUP SOCCER GAME             09-JUL-99 I 10-JUL-99
         6          2 Guest Speaker                  20-NOV-99 I

What do I need to do?

Upvotes: 0

Views: 230

Answers (3)

Shann
Shann

Reputation: 690

For the first part, the below should be correct.

SELECT trunc(MONTHS_BETWEEN (TO_DATE('2013/10/14', 'YYYY/MM/DD'), TO_DATE('2013/12/25', 'YYYY/MM/DD')) )AS MONTHS_BETWEEN FROM DUAL;


select trunc(MONTHS_BETWEEN(fg,gs)) as "difference in month"
from
(
select 
                 max(case when event_name = 'Flag FOOtball Game'
                     then event_dat
                 end) as FG,
                 max(case when event_name = 'Guest Speaker'
                           then event_dat
                 end)  as GS
from event
where event_name in ('Flag FOOtball Game','Guest Speaker')   
) 
;

Upvotes: 0

Noel
Noel

Reputation: 10525

Here's a way,

select months_between(
            max(case when event_name = 'Flag FOOtball Game'
                      then event_dat
                end),
            max(case when event_name = 'Guest Speaker'
                      then event_dat
                end)
  )
from event
where event_name in ('Flag FOOtball Game','Guest Speaker');

Upvotes: 1

Mikhail
Mikhail

Reputation: 1560

Just join this table on itself with needed conditions, for example:

SELECT t1.EVENT_DAT - t2.EVENT_DAT FROM EVENT t1, EVENT t2
WHERE t1.EVENT_NAME = 'Flag FOOtball Game' AND t2.EVENT_NAME = 'Guest Speaker'

Upvotes: 0

Related Questions