CODEWITHSUNDEEP
sqldatabaseoracle-databaseselectoracle10g
user2763589
user2763589

Reputation: 43

How to get min date using oracle database date table

enter image description here

here is my sql query

select e.event_name,
    min(dt.start_date) as start_date,
    min(dt.start_date)||'-'||max(dt.end_date) as FromTo
from event e join
    date_table dt
on e.e_id = dt.e_id
group by e.event_name;

I want to get upcomming events. For that, I use MAX(dt.end_date)>=sysdate then i got the upcomming events. But my problem is, when an event happens today, tomorrow and day after tomorrow, I display start date as "today"(minimum start date). But when tomorrow comes, still I showed start date as previous date. Because minimum start date is yesterday. i want to display tomorrow date as the start date.

How should I change my code?

Upvotes: 4

Views: 55606

Answers (2)

DB_learner
DB_learner

Reputation: 1026

Simply add a where clause. 

select e.event_name,
    min(dt.start_date) as start_date,
    min(dt.start_date)||'-'||max(dt.end_date) as FromTo
from event e join
    date_table dt
on e.e_id = dt.e_id
where dt.start_date >=sysdate
group by e.event_name;

Upvotes: 1

haki
haki

Reputation: 9759

I'm not sure why you are using min/max here.

If you want to see your next 10 upcoming events (from today) you might want to try something like this :

select * 
from (
    select  e.* , dt.start_date , dt.end_date
    from    event e , date_table dt 
    where   e.e_id = dt.e_id 
    and dt.start_date > sysdate 
    order by dt.start_date)
where rownum < 10

Upvotes: 2

Related Questions