Reputation: 93
I have an Oracle database with two tables:
Register events table:
ID Student_ID REGISTER_DATE UNREGISTER_DATE
1 1 30/6/2015
2 1 15/7/2015
3 3 17/12/2015
4 1 22/1/2016
I know how to query to check if the student was registered on a given day.
For a given date range (e.g "20-JAN-2015" to " "3-DEC-2016") I need to query for the number of registered students on each day in the range.
The output should be like:
Date Number_of_students
20/01/2015 32
21/01/2015 36
... ...
... ...
3/12/2016 67
This should be done with sql or plsql, but data manipulation by app (there is no ORM, just JDBC) is also allowed.
Upvotes: 0
Views: 176
Reputation: 35930
As far as I understood your event table stores two entries, one for registration and one for un-registration.
You want to find the total number of active students on a given date range for each day.
You can use the following code:
WITH DATERANGE(IDATE) AS
(SELECT &&STARTDATE + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= &&ENDDATE - &&STARTDATE + 1),
--
STUDENT_REGISTER(STU_ID, REGISTRATIONDATE, UNREGISTRATIONDATE) AS
(SELECT STU_ID,
MAX(CASE WHEN EVENT = 'REGISTARTION' THEN YOURDATECOLUMN END) AS REGDATE,
MAX(CASE WHEN EVENT = 'UNREGISTARTION' THEN YOURDATECOLUMN END) AS UNREGDATE
FROM REGISTER_EVENTS
GROUP BY STU_ID)
--
SELECT DR.IDATE, COUNT(1) AS Number_of_students
FROM DATERANGE DR
JOIN REGISTER_EVENTS RE
ON DR.IDATE BETWEEN RE.REGISTRATIONDATE AND COALESCE(RE.UNREGISTRATIONDATE, DR.IDATE)
GROUP BY DR.IDATE
Upvotes: 1
Reputation: 1271231
You can "unpivot" the data and use cumulative counts:
select dte, sum(sum(inc)) over (order by dte) as num_registered
from ((select register_date as dte, 1 as inc
from register_events
) union all
(select unregister_date, -1 as inc
from register_events
)
) re
where dte is not null
group by dte
order by dte;
Note: This works for multiple registrations/unregistrations for a single student. But it does not work for overlapping registrations for a single student.
Upvotes: 0
Reputation: 824
select nvl(register_date, unregister_date) dt, count(register_date) - count(unregister_date)
FROM t
group by nvl(register_date, unregister_date)
If your are querying for a date range and the unregister happens after your date range it won't be picked up)
Upvotes: 0
Reputation: 143163
From what you posted so far:
select date_column, count(*)
from register_events
where event = 'register'
and date_column between date '2015-01-20' and date '2016-12-03'
group by date_column
Upvotes: 0