Daily count of Active tickets with start and closed dates for a given date range (calendar)

user_14883150 0
Viewed 63

I want to count of the active tickets, I have the start and closed date of each ticket. I need the daily total between two dates (a calendar)

This is the ticket table (MINITABLE)

Ticket              Created                Closed
MAS-10772905    2020-04-20T14:00:00 
MAS-11542813    2020-07-28T16:00:00    2020-07-30T10:00:00
MAS-11850014    2020-09-04T04:00:00 
MAS-13926615    2021-02-26T02:00:00 
MAS-11960295    2020-09-16T10:00:00    2020-09-16T06:00:00
MAS-12301712    2020-10-27T23:00:00    2021-01-02T04:00:00
MAS-11850014    2020-09-04T23:00:00 
MAS-12361774    2020-11-02T08:00:00 
MAS-13141083    2021-01-02T11:00:00 
MAS-13144525    2021-01-02T14:00:00 
...

This is what I want to get

Start       Active Tickets
2020-01-01    10
2020-01-02    8
2020-01-03    14
2020-01-04    13
2020-01-05    11
2020-01-06    16
...

I try to do it by this query but is not working... it gives me the following failed message: "LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join."

SELECT start, COUNT(DISTINCT Ticket) AS Actived
FROM   (SELECT 
        CAST(CONCAT(DATE(calendar)," ", TIME(00,00,00)) AS DATETIME) AS start,
        CAST(CONCAT(DATE(calendar)," ", TIME(22,00,00)) AS DATETIME) AS ending
        FROM 
        UNNEST(GENERATE_DATE_ARRAY('2020-01-01', (CURRENT_DATE()), INTERVAL 1 DAY)) AS calendar
        ORDER BY start ASC) calendar

LEFT JOIN MINITABLE ON (start BETWEEN Created AND Closed) OR 
                      (ending BETWEEN Created AND Closed) OR 
                      (Created >= start AND Closed <= ending) OR 
                      (Created <= start AND Closed >= ending)
GROUP BY start
ORDER BY start ASC

Any help would be much appreciated! Thank you very much :)

google-bigquery
1 Answers

Consider below

select date(timestamp_trunc(hour, day)) day, 
  count(distinct Ticket) Active_Tickets
from `project.dataset.minitable`,
unnest(generate_timestamp_array(timestamp(created), ifnull(current_timestamp(), timestamp(closed)), interval 1 hour)) hour
group by day

here first - you split/expand each entry row into respective hourly rows - as many rows as how many hours in between created and closed (if closed is null you replace it with current timestamp). Then you simply group by day and count distinct tickets - that simple

user_52219440
Related