1

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 :)

Slukehart
  • 1,037
  • 5
  • 15
  • 1
    Thanks so much for the help! This was the solution that appears to have worked: `SELECT COUNT(t1.status), month(t1.date_modified), year(t1.date_modified), week(t1.date_modified), t1.status FROM trends as t1 JOIN trends as t2 ON (t1.original = t2.ID) WHERE t1.status != t2.status GROUP BY MONTH(date_modified), status;` – Slukehart Aug 23 '11 at 16:10

2 Answers2

3

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;
Alexander Sulfrian
  • 3,533
  • 1
  • 16
  • 9
0

A subquery with EXISTS/NOT EXISTS should be what you are looking for.

piotrp
  • 3,755
  • 1
  • 24
  • 26
  • 1
    Just to help me understand how that function applies to this problem could you provide further details? (Not time sensitive:) – Slukehart Aug 23 '11 at 16:14
  • I think I misunderstood your question :) Basically - `EXISTS()` is a function used in `WHERE` clause that takes a subquery (SELECT...) as an argument and returns TRUE if it returns any rows. In simple cases it performs as fast as a join doing similar work (and can be more readable and easier to write), sometimes it's slower. – piotrp Aug 23 '11 at 16:41