How can I count rows matching criteria for each week in MySQL?

Question

I have a table which stores ticket requests, and records both a requested and resolved date.

I want to craft a query that will show historically, for each week, how many unresolved tickets there were in the system.

The problem is if I use the requested date as a group criteria, then tickets that went unresolved more than one week are not counted twice. I want to make sure that any ticket that is unresolved for longer than my group size, contributes its count more than once as needed.

For example, with the following data:

id  requested   resolved
==  ==========  ==========
1   2015-07-01  2015-07-02
2   2015-07-01  NULL
3   2015-07-08  2015-07-10
4   2015-07-08  NULL

The first (26) and second week (27) each have two requests. Each week has one resolved and one unresolved request, so the result of the query should show 1 unresolved for the first week, and 2 unresolved for the second. (Items with a resolution date in a future week also count as unresolved, but for the purposes of simplifying this example, I'm only showing null dates.)

I'd like the result to show:

year   week    # unresolved
====   ====    ============
2015   26      1
2015   27      2

The query I have so far:

SELECT
    YEAR(requested) `year`,
    WEEK(requested, 5) `week`,
    COUNT(id) `# unresolved`
FROM
    tickets
WHERE
    WEEK(requested) < WEEK(resolved)
    OR resolved IS NULL
GROUP BY `Year`, `Week`;

Only shows 1 unresolved ticket per week:

year  week  # unresolved  
====  ====  ============
2015  26    1               
2015  27    1               

What should I be looking at to modify this query appropriately?

http://sqlfiddle.com/#!9/90782/1/0

Edit:

As additional info, this is a relatively simple query when providing it with any specific week to examine:

SELECT
    COUNT(id) `# unresolved`
FROM
    tickets
WHERE
    WEEK(requested) <= WEEK('2015-07-01')
    AND
    (
        WEEK(resolved) > WEEK('2015-07-01')
        OR resolved IS NULL
    );

By changing the input week, it is possible to obtain the number of unresolved tickets for any week of interest. My goal is to create a query that will group all available data by week, rather than modify this query for single-week results.

Answer 1

(Note: I am showing my thought process, but you only need the final query in this answer)

(Note2: Check the sqlfiddle here)

I would look at the ticket requested and ticket resolved as two different types of events, so

select requested eventDate, 1 ticketChange from table

would give me a 1 count for each requested ticket and

select resolved eventDate, -1 ticketChange from table

would give me a -1 count for each resolved ticket. If I do a union of those two queries, I would get a list of dates with a +1 and a -1 for whether a ticket was added or resolved. So I can get a total unresolved, per week by doing

    select year(eventDate) myYear, week(eventDate) myWeek, sum(ticketChange) totTicketChange
    from (select requested eventDate, 1 ticketChange from table union all
          select resolved eventDate, -1 ticketChange from table where resolved is not null)
    group by year(eventDate) asc, week(eventDate) asc

But since you need a cumulative total, then I would define a variable @unresolvedCount and increment it as I go through the select rows:

set @unresolvedCount := 0;
select myYear, myWeek, (@unresolvedCount := @unresolvedCount + totTicketChange) unresolved
  from (select year(eventDate) myYear, week(eventDate) myWeek, sum(ticketChange) totTicketChange
          from (select requested eventDate, 1 ticketChange from tickets union all
                select resolved eventDate, -1 ticketChange from tickets where resolved is not null) TicketEvents
 group by year(eventDate) asc, week(eventDate) asc) TicketCummulative

This does exactly what you want. I have checked it with the fiddle noted above, and I would be surprised if you can find a more efficient algorithm to do what you are trying to do. I would also suggest that you run each inner query by itself, to see its results, and work your way out. That will give you insight into how it works.

If you want to get the results for only a particular time period, say the current year, there are three different ways to do it depending on what you want. If you want to count only the tickets that were requested during this time period, that is, for example, you don't want to count tickets that were requested last year even if they are still unresolved or even if they got resolved this year, then you would change the innermost query to read:

select requested eventDate, 1 ticketChange from tickets where requested >= '2015-01-01' union all
select resolved eventDate, -1 ticketChange from tickets where requested >= '2015-01-01' and resolved is not null

If you want to count tickets that were either requested or resolved in the given time period, then you would change the innermost query to read:

select requested eventDate, 1 ticketChange from tickets where requested >= '2015-01-01' union all
select resolved eventDate, -1 ticketChange from tickets where resolved is not null and resolved >= '2015-01-01'

If you want to count all tickets, as long as they were requested or resolved in the current time period, or if they are still unresolved (even if they are 3 years old), then you would have to put the test for the date, at the very end of the complete query, letting the inner queries process all tickets.

... group by year(eventDate) asc, week(eventDate) asc) TicketCummulative where myYear >= 2015 and myWeek >= 1

Answer 2

Well sparse data sets deserve sparse answers, so here's another to think about...

SELECT WEEK(x.requested) wk
     , COUNT(y.id) 
  FROM tickets x 
  JOIN tickets y 
   ON y.id <= x.id 
  AND y.resolved IS NULL 
WHERE x.resolved IS NULL 
GROUP 
   BY WEEK(x.requested);

Answer 3

How about setting the Unresolved into a MySQL veriable like the following

set @preCount = 0;
select ticketsB.Year, ticketsB.Week,
    IF(@preCount=0, @preCount:=Unresolved, @preCount:=@preCount+Unresolved) as Unresolved from (
SELECT
    YEAR(requested) `Year`,
    WEEK(requested, 5) `Week`,
    count(id) `Unresolved`
FROM
    tickets
WHERE
    WEEK(requested) < WEEK(resolved)
    OR resolved IS NULL
GROUP BY `Year`, `Week`) as ticketsB

See results here http://sqlfiddle.com/#!9/90782/61

Basically, you storing the previous unresolved count into a local veriable and then adding to the next row unresolved count based what's in the variable.

Answer 4

I think it's a matter of perspective. You'll need to feed the query a reporting date/week.

SELECT
    YEAR(requested) `Year`,
    WEEK(requested, 5) `Week`,
    COUNT(id) `Qty Unresolved`
FROM
    tickets
WHERE
    WEEK('2015-07-08') <= WEEK(resolved)
    or resolved IS NULL
GROUP BY `Year`, `Week`;