My sample is a ticketing system, holding entries for status updates and the creation of a ticket.

Edit: sorry had the incorrect Fiddle Link

Fiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=a5ff4600adbab185eb14b08586f1bd29

I want to evaluate the data based on the ticket creation date, and obtain three things for a specific date:

1. (done) how many tickets where totally created on the given date
2. (done) how many tickets were created in status 'unknown' on the given date
3. (not done) how many tickets were totally in status 'unknown' on the given date? Tricky! Because what matters is the status at the max STATUS_CHANGED below midnight of the given date.

Desired result for 01.01.2021:

Explanation: on 01-JAN-20, ticket 3 and 4 were in status 'unknown' at end of the day

Desired result for 02.01.2021:

Explanation: on 02-JAN-20, only ticket 3 was in status 'unknown' at end of the day

Current solution for part 1 2:

select ticket_created, 
count(*) as "Total Created",
sum(case when status = 'unknown' then 1 else 0 end) as "Unknown tickets created",
'?' as "Total tickets in Unknown status"
from myTable
where id in
    (select min(id) as id
    from myTable
    where ticket_created = to_date('01.01.2020', 'DD.MM.YYYY')
    group by ticketid)
group by ticket_created

Could you give me some hint on how to approach point 3?

CodePudding user response：

This is how I would achieve your aims, assuming I've understood your logic correctly:

with ticket_info as (select id,
                            ticketid,
                            status,
                            ticket_created,
                            status_changed,
                            row_number() over (partition by ticketid, trunc(status_changed) order by status_changed desc) rn_per_id_day_desc,
                            row_number() over (partition by ticketid order by status_changed) rn_per_id_asc
                     from   mytable)
select ticket_created,
       count(distinct case when trunc(ticket_created) = to_date('01/01/2020', 'dd/mm/yyyy') then ticketid end) as "Total Created",
       count(case when rn_per_id_asc = 1 and status = 'unknown' then 1 end) as "Unknown tickets created",
       count(case when rn_per_id_day_desc = 1 and status = 'unknown' then 1 end) as "Total tickets in Unknown status"
from   ticket_info
where  status_changed >= to_timestamp('01/01/2020', 'dd/mm/yyyy')
and    status_changed < to_timestamp('01/01/2020', 'dd/mm/yyyy')   interval '1' day
group by ticket_created;

db<>fiddle

You can see that first of all, I use a couple of row_number() analytic functions to provide labels for the rows - one labels the rows per id in the order they were changed (which allows us to identify the first row per id, i.e. the ticket created row), the other labels the rows per id and day in descending order (which allows us to identify the last row of the day per id).

Using that information, we can calculate all three of your cases:

1. tickets created on a day - here I've used a count distinct, but you could change it to count(case when rn_per_id_asc = 1 then 1 end), which may well be more efficient and easier to understand.
2. tickets created on a day as "unknown" - here I've used a conditional count: if it's the first row and it has a status unknown, then count it
3. tickets in "unknown" status at the end of the day - here I've used another conditional count: if it's the last row of the day and the status is unknown, count it.