CODEWITHSUNDEEP
sqloracle-database
Room'on
Room'on

Reputation: 93

How to calculate the actual number for each day using start and end date?

I need to calculate the number of distinct ID for each date. Is there a way to do it only with SQL, without using PL/SQL?

create table test (
    id number(10),
    start_date timestamp,
    end_date timestamp
);

insert into test values(1, to_date('01.01.2019', 'dd.mm.yyyy'),  to_date('03.01.2019', 'dd.mm.yyyy'));
insert into test values(2, to_date('02.01.2019', 'dd.mm.yyyy'),  to_date('05.01.2019', 'dd.mm.yyyy'));
insert into test values(3, to_date('04.01.2019', 'dd.mm.yyyy'),  to_date('06.01.2019', 'dd.mm.yyyy'));

I need a SELECT statement to get something like this:

01.01.2019 1
02.01.2019 2
03.01.2019 2
04.01.2019 2
05.01.2019 2
06.01.2019 1

Upvotes: 1

Views: 201

Answers (3)

D-Shih
D-Shih

Reputation: 46219

Another way you can try to use cte recursive

WITH   CTE(id,start_date,end_date) AS
(
        SELECT  id,start_date ,end_date
        FROM test
        UNION ALL
        SELECT id,start_date ,cast((end_date - 1) as date)
        FROM CTE 
        WHERE start_date <= cast((end_date - 1) as date)
)
SELECT  end_date,COUNT(*)
FROM    CTE
GROUP BY end_date
ORDER BY end_date

sqlfiddle

Or using CONNECT BY

SELECT dt,count(*)
FROM ( 
    SELECT DISTINCT end_date,start_date + LEVEL dt
    FROM    test
    CONNECT BY extract(day from end_date - start_date) - LEVEL + 1 >= 0
) t1
GROUP BY dt
ORDER BY dt

sqlfiddle

Upvotes: 2

MattDevs
MattDevs

Reputation: 59

SELECT start_date,COUNT(Id_number) from test group by start_date

This way allows you group your rows by date (start date or end date, whatever you prefer), and count the rows with the same date.

I hope it helps you.

Upvotes: 0

GMB
GMB

Reputation: 222462

The key point to solve this is to generate a list of dates for which you want the number of distinct id to be counted. THen you can simply JOIN this list with the table and turn on aggregation.

Here is a simple solution that builds the list of date from the distinct values available in both columns containing dates in the original table. Other (better) options exists, using a recursive subquery or a calendar table.

SELECT dt.mydate, COUNT(DISTINCT id_number) cnt
FROM
    ( 
        SELECT DISTINCT start_date mydate FROM test 
        UNION ALL SELECT DISTINCT end_date  FROM test 
    ) dt
    LEFT JOIN test t ON dt.mydate >= t.start_date AND dt.mydate <= t.end_date
GROUP BY dt.mydate
ORDER BY dt.mydate

This demo on DB Fiddle with your sample data returns:

MYDATE                       | CNT
:--------------------------- | --:
01-JAN-19 12.00.00.000000 AM |   1
02-JAN-19 12.00.00.000000 AM |   2
03-JAN-19 12.00.00.000000 AM |   2
04-JAN-19 12.00.00.000000 AM |   2
05-JAN-19 12.00.00.000000 AM |   2
06-JAN-19 12.00.00.000000 AM |   1

Upvotes: 1

Related Questions