CODEWITHSUNDEEP
sqloracle-database
L.dev
L.dev

Reputation: 93

How to query for data from each day

I have an Oracle database with two tables:

  1. Students
  2. Register events - when the student register, a row is created with the registration date and when the student unregister, a row is created with the unregistration date.

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

Answers (4)

Popeye
Popeye

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

Gordon Linoff
Gordon Linoff

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

Slkrasnodar
Slkrasnodar

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

Littlefoot
Littlefoot

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

Related Questions