Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE myTable (
  ID            INT,
  TicketId      INT,
  Status        VARCHAR2(20),
  Ticket_Created  DATE,
  Status_Changed     TIMESTAMP
);
INSERT INTO myTable
          SELECT 1, 1, 'other_error',   DATE'2020-01-01', TIMESTAMP'2020-01-01 08:00:00' FROM DUAL
UNION ALL SELECT 2, 2, 'tech_error',   DATE'2020-01-01', TIMESTAMP'2020-01-01 09:00:00' FROM DUAL
UNION ALL SELECT 3, 3, 'unknown',   DATE'2020-01-01', TIMESTAMP'2020-01-01 09:10:00' FROM DUAL
UNION ALL SELECT 4, 4, 'unknown',   DATE'2020-01-01', TIMESTAMP'2020-01-01 09:20:00' FROM DUAL
UNION ALL SELECT 5, 4, 'tech_error',   DATE'2020-01-01', TIMESTAMP'2020-01-02 09:30:00' FROM DUAL
UNION ALL SELECT 6, 1, 'solved',   DATE'2020-01-01', TIMESTAMP'2020-01-02 10:00:00' FROM DUAL
UNION ALL SELECT 7, 2, 'solved',   DATE'2020-01-01', TIMESTAMP'2020-01-02 07:00:00' FROM DUAL
UNION ALL SELECT 8, 5, 'tech_error',   DATE'2020-01-02', TIMESTAMP'2020-01-02 08:00:00' FROM DUAL
UNION ALL SELECT 9, 6, 'unknown',   DATE'2020-01-02', TIMESTAMP'2020-01-02 08:30:00' FROM DUAL
UNION ALL SELECT 10, 6, 'solved',   DATE'2020-01-02', TIMESTAMP'2020-01-02 09:30:00' FROM DUAL
UNION ALL SELECT 11, 5, 'unknown',   DATE'2020-01-02', TIMESTAMP'2020-01-03 08:40:00' FROM DUAL
UNION ALL SELECT 12, 4, 'unknown',   DATE'2020-01-03', TIMESTAMP'2020-01-03 09:00:00' FROM DUAL
UNION ALL SELECT 13, 7, 'unknown',   DATE'2020-01-03', TIMESTAMP'2020-01-03 09:00:00' FROM DUAL
UNION ALL SELECT 14, 7, 'unknown',   DATE'2020-01-03', TIMESTAMP'2020-01-03 09:10:00' FROM DUAL
14 rows affected

SELECT * FROM myTable order by status_changed


ID
TICKETID
STATUS
TICKET_CREATED
STATUS_CHANGED


1
1
other_error
01-JAN-20
01-JAN-20 08.00.00.000000

2
2
tech_error
01-JAN-20
01-JAN-20 09.00.00.000000

3
3
unknown
01-JAN-20
01-JAN-20 09.10.00.000000

4
4
unknown
01-JAN-20
01-JAN-20 09.20.00.000000

7
2
solved
01-JAN-20
02-JAN-20 07.00.00.000000

8
5
tech_error
02-JAN-20
02-JAN-20 08.00.00.000000

9
6
unknown
02-JAN-20
02-JAN-20 08.30.00.000000

10
6
solved
02-JAN-20
02-JAN-20 09.30.00.000000

5
4
tech_error
01-JAN-20
02-JAN-20 09.30.00.000000

6
1
solved
01-JAN-20
02-JAN-20 10.00.00.000000

11
5
unknown
02-JAN-20
03-JAN-20 08.40.00.000000

13
7
unknown
03-JAN-20
03-JAN-20 09.00.00.000000

12
4
unknown
03-JAN-20
03-JAN-20 09.00.00.000000

14
7
unknown
03-JAN-20
03-JAN-20 09.10.00.000000


with date_of_interest as (select start_date + level -1 dt,
                                 start_date + level next_dt
                          from   (select to_date('01/01/2020', 'dd/mm/yyyy') start_date,
                                         to_date('03/01/2020', 'dd/mm/yyyy') end_date
                                  from   dual)
                          connect by level <= (end_date - start_date) + 1),
          ticket_info as (select mt.id,
                                 mt.ticketid,
                                 mt.status,
                                 mt.ticket_created,
                                 mt.status_changed,
                                 row_number() over (partition by mt.ticketid, doi.dt order by mt.status_changed) rn_per_id_asc,
                                 row_number() over (partition by mt.ticketid, doi.dt order by mt.status_changed desc) rn_per_id_desc,
                                 doi.dt,
                                 doi.next_dt
                          from   mytable mt
                                 inner join date_of_interest doi on mt.status_changed < doi.next_dt)
select dt,
       count(case when ticket_created = dt and rn_per_id_asc = 1 then 1 end) as "Total Created",
       count(case when ticket_created = dt and rn_per_id_asc = 1 and status = 'unknown' then 1 end) as "Unknown tickets created",
       count(case when rn_per_id_desc = 1 and status = 'unknown' then 1 end) as "Total tickets in Unknown status"
from   ticket_info
group by dt
order by dt;


DT
Total Created
Unknown tickets created
Total tickets in Unknown status


01-JAN-20
4
2
2

02-JAN-20
2
1
1

03-JAN-20
1
1
4


with date_of_interest as (select dt,
                                 dt + 1 next_dt
                          from   (select to_date('03/01/2020', 'dd/mm/yyyy') dt
                                  from   dual)),
          ticket_info as (select mt.id,
                                 mt.ticketid,
                                 mt.status,
                                 mt.ticket_created,
                                 mt.status_changed,
                                 row_number() over (partition by mt.ticketid, doi.dt order by mt.status_changed) rn_per_id_asc,
                                 row_number() over (partition by mt.ticketid, doi.dt order by mt.status_changed desc) rn_per_id_desc,
                                 doi.dt,
                                 doi.next_dt
                          from   mytable mt
                                 inner join date_of_interest doi on mt.status_changed < doi.next_dt)
select dt,
       count(case when ticket_created = dt and rn_per_id_asc = 1 then 1 end) as "Total Created",
       count(case when ticket_created = dt and rn_per_id_asc = 1 and status = 'unknown' then 1 end) as "Unknown tickets created",
       count(case when rn_per_id_desc = 1 and status = 'unknown' then 1 end) as "Total tickets in Unknown status"
from   ticket_info
group by dt;


DT
Total Created
Unknown tickets created
Total tickets in Unknown status


03-JAN-20
1
1
4

ID	TICKETID	STATUS	TICKET_CREATED	STATUS_CHANGED
1	1	other_error	01-JAN-20	01-JAN-20 08.00.00.000000
2	2	tech_error	01-JAN-20	01-JAN-20 09.00.00.000000
3	3	unknown	01-JAN-20	01-JAN-20 09.10.00.000000
4	4	unknown	01-JAN-20	01-JAN-20 09.20.00.000000
7	2	solved	01-JAN-20	02-JAN-20 07.00.00.000000
8	5	tech_error	02-JAN-20	02-JAN-20 08.00.00.000000
9	6	unknown	02-JAN-20	02-JAN-20 08.30.00.000000
10	6	solved	02-JAN-20	02-JAN-20 09.30.00.000000
5	4	tech_error	01-JAN-20	02-JAN-20 09.30.00.000000
6	1	solved	01-JAN-20	02-JAN-20 10.00.00.000000
11	5	unknown	02-JAN-20	03-JAN-20 08.40.00.000000
13	7	unknown	03-JAN-20	03-JAN-20 09.00.00.000000
12	4	unknown	03-JAN-20	03-JAN-20 09.00.00.000000
14	7	unknown	03-JAN-20	03-JAN-20 09.10.00.000000