Search code examples
phpmysqljoingroup-byrevisions

SQL query which counts status of grouped by month w. revision system

While attempting to produce a view of ticket statuses over time, I've run into a few challenges:

Background:

The database containing ticket records is setup to allow basic re-visioning, and includes the following columns (this is what I believe to be relevant):

ID - original - status - date_created - modified

Desired Solution:

A table which includes: The count of each status grouped by week/month/year.

The challenge:

Excluding any revision in which the status has not changed.

Thanks ahead of time for any assistance, this is heating up my brain :)

Solution

  • Should be something like this (without the grouping by date):

     SELECT t1.*
 FROM table as t1
 JOIN table as t2 ON (t1.original = t2.ID)
 WHERE t1.status != t2.status;