I have different events:
If more than 2 of them take place in one day, I would like to know which ones and how many that are. How do I build the SQL command?
Expected output:
More than 2 events on the same day.
3
-
3Post your sample data and expected output as plain text, not images.Kaushik Nayak– Kaushik Nayak2018-09-27 09:27:34 +00:00Commented Sep 27, 2018 at 9:27
-
1how event ID 181 is expected in the outcome ? Date_from and Data_until are not 27..please explainJayanth– Jayanth2018-09-27 09:51:45 +00:00Commented Sep 27, 2018 at 9:51
-
please give samplesNikhil– Nikhil2018-09-27 14:39:03 +00:00Commented Sep 27, 2018 at 14:39
Add a comment
|
2 Answers
Try this:
WITH calendar
AS ( SELECT TRUNC( SYSDATE - LEVEL ) AS cal_day
FROM DUAL
CONNECT BY LEVEL < 30)
SELECT calendar.cal_day,
COUNT( e.event_id ) AS number_of_events,
LISTAGG( e.event_id, ', ' ) WITHIN GROUP (ORDER BY e.date_from)
AS events
FROM calendar, event e
WHERE calendar.cal_day BETWEEN e.date_from AND e.date_until
GROUP BY calendar.cal_day
HAVING COUNT( e.event_id ) > 1;
You can always change number in CONNECT BY LEVEL < :n or materialize calendar as a table.
Comments
sample input:
create table ns_123(a int,b date,c date);
insert into ns_123 values(179,'27-sep-2018','27-sep-2018');
insert into ns_123 values(181,'26-sep-2018','28-sep-2018');
insert into ns_123 values(180,'27-sep-2018','27-sep-2018');
select * from ns_123;
select distinct n1.a from ns_123 n1,ns_123 n2 where (n1.b-n2.b)>=(n1.c-n2.c);
sample output:
179
180
181
