Clever Geek Handbook

How can I calculate row matching criteria for each week in MySQL?

I have a table that stores ticket requests and records both the requested and the resolved date.

I want to create a query that will be displayed historically, for each week, how many unresolved tickets were in the system.

The problem is that I am using the requested date as a group criterion, then tickets that have not been allowed for more than one week are not counted twice. I want to make sure that any ticket that is not allowed beyond my group size contributes 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) have two queries. Each week has one allowed and one unresolved request, so the query result should show 1 unresolved for the first week and 2 unresolved for the second. (Items with a resolution date next week are also considered unresolved, but for the sake of simplicity of this example, I'm only showing dates that are zero.)

I want the result to show:

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

The request 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`;

Shows only 1 unauthorized ticket per week:

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

What should I be looking for to modify this request accordingly?

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

Edit:

As additional information, this is a relatively straightforward request when it provides any particular week to study:

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 entry week, you can get 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 altering that query for one week results.

+3


source to share


4 answers


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

( Note2 : here's a sqlfiddle )

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

select requested eventDate, 1 ticketChange from table

would give me 1 bill for each ticket requested and

select resolved eventDate, -1 ticketChange from table

would give me a -1 count for every ticket allowed. If I combine the two requests, I would get a list of dates with +1 and -1 for whether a ticket was added or allowed. This way I can get the full unresolved, in a 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 want the total, then I would define the @unresolvedCount variable 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 tested it with the fiddle noted above and I would be surprised if you could find a better algorithm to do what you are trying to do. I also suggest that you run each internal query on its own to see the results and work your way. This will give you an idea of ​​how it works.

If you only want results for a specific period of time, say the current year, there are three different ways to do this depending on what you want. If you only want to count tickets that were requested during this time period, that is, for example, you do not want to count tickets that were requested last year, even if they are not yet allowed or even if they are resolved this year, then you change the innermost query:

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 the tickets that have been requested or allowed in a given time period, you must modify the innermost request 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 recount all tickets, if they were requested or allowed in the current time period, or if they still remain unresolved (even if they are 3 years old), you will have to take a test on a date at the very end of the full request, allowing internal requests to be processed all tickets.

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


source


How about installing unresolved in MySQL like below:

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 store the previous unresolved count to local and then add the unresolved count to the next line based on what's in the variable.

0


source


Well sparse datasets deserve rare answers, so some more thought here ...

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);
0


source


I think it's a matter of perspective. You will need to submit a request for the 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`;
-1


source






More articles:


All Articles